Create a public variable

O

ordnance1

Below is my UserForm_Initialize code. What I'm wondering is, is it possible
to declare a Public Variable if rng(1, 7).value is "Yes"?

I would call the Variable Cleared and then during my exit routine
(clicking the Finished Button) I would need a statement that said if Cleared
is true then exit sub.



Private Sub UserForm_Initialize()
Dim rng
Set rng = Cells(ActiveCell.Row, 1)

TextBox1.Value = rng(1, 1) 'Date
TextBox1.Text = Format(TextBox1.Text, "mm/dd/yyyy")
TextBox2.Value = rng(1, 2) 'Check Number
TextBox3.Value = rng(1, 3) 'Check Amount
TextBox3.Value = Format(TextBox3.Value, "currency")
TextBox4.Value = rng(1, 4) 'Paid To
TextBox5.Value = rng(1, 5) 'Explination

If rng(1, 7).Value = "Yes" Then
OptionButton1.Value = True
End If

End Sub
 
J

joel

You need to put the public statement in a module outside any function or
sub. Like this


Public Joel1

Sub Test
Userform1.Show
end Test
 
R

Ryan H

This should help. If this helps please click "YES" below.

If rng(1, 7).Value = "Yes" Then
Public Cleared As Boolean
Cleared = True
End If

Sub FinishedButton_Click()
If Cleared Then Exit Sub
End Sub
 
B

Bob Phillips

Did you actually try that?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
 
R

Ryan H

No, Bob. I didn't. Thanks for the correction. It should be.

'in standard module
Public Cleared As Boolean

If rng(1, 7).Value = "Yes" Then
Cleared = True
Else
Cleared = False
End If

Sub FinishedButton_Click()
If Cleared Then Exit Sub
End Sub
 
R

Rick Rothstein

If rng(1, 7).Value = "Yes" Then
Cleared = True
Else
Cleared = False
End If

Or, more concisely, replace the above with this single line of code...

Cleared = rng(1, 7).Value = "Yes"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top