On my UserForm is TextBox1, which if a user makes a change to the TextBox I
want the value of some OptionButtons set to false. That in its self is easy
enough, using the TextBox1_Change routine below. My problem is that during
UserForm Initialization TextBox1 is populated with the following code:

TextBox1.Value = rng(1, 7).Text

Unfortunately the TextBox1_Change routine sees that as a change and sets
all OptionButtons to False. Any suggestions?

Private Sub TextBox1_Change()

Me.TextBox1 = UCase(Me.TextBox1.Text)

OptionButton1.Value = False
OptionButton2.Value = False
OptionButton3.Value = False
OptionButton4.Value = False
OptionButton5.Value = False
OptionButton6.Value = False

End Sub

The simple way to avoid this is to declare a UserForm-wide 'global' Boolean
variable (declare it in the General/Declarations section of the code window,
outside of any declared procedures), check it for being False in the
TextBox's Change event (exiting the Sub if it is) and then setting it to
True at the end of the UserForm's Initialize event so it won't interfere
with the normal operation of the TextBox's Change event. Here is the
general structure of what I am talking about...

Dim Initialized As Boolean

Private Sub TextBox1_Change()
If Not Initialized Then Exit Sub
'
' Your change event code goes here
'
End Sub

Private Sub UserForm_Initialize()
'
' Your UserForm's initialing code goes here
'
Initialized = True
End Sub

--
Rick (MVP - Excel)


"Patrick C. Simonds" wrote in message
...
On my UserForm is TextBox1, which if a user makes a change to the TextBox
I want the value of some OptionButtons set to false. That in its self is
easy enough, using the TextBox1_Change routine below. My problem is that
during UserForm Initialization TextBox1 is populated with the following
code:

TextBox1.Value = rng(1, 7).Text

Unfortunately the TextBox1_Change routine sees that as a change and sets
all OptionButtons to False. Any suggestions?

Private Sub TextBox1_Change()

Me.TextBox1 = UCase(Me.TextBox1.Text)

OptionButton1.Value = False
OptionButton2.Value = False
OptionButton3.Value = False
OptionButton4.Value = False
OptionButton5.Value = False
OptionButton6.Value = False

End Sub

when I encounter similar problem I just place a one liner in the change event
crude but seems to work!

Private Sub TextBox1_Change()

If Me.Visible = False Then Exit Sub

Me.TextBox1 = UCase(Me.TextBox1.Text)

OptionButton1.Value = False
OptionButton2.Value = False
OptionButton3.Value = False
OptionButton4.Value = False
OptionButton5.Value = False
OptionButton6.Value = False

End Sub
--
jb


"Patrick C. Simonds" wrote:

On my UserForm is TextBox1, which if a user makes a change to the TextBox I
want the value of some OptionButtons set to false. That in its self is easy
enough, using the TextBox1_Change routine below. My problem is that during
UserForm Initialization TextBox1 is populated with the following code:

TextBox1.Value = rng(1, 7).Text

Unfortunately the TextBox1_Change routine sees that as a change and sets
all OptionButtons to False. Any suggestions?

Private Sub TextBox1_Change()

Me.TextBox1 = UCase(Me.TextBox1.Text)

OptionButton1.Value = False
OptionButton2.Value = False
OptionButton3.Value = False
OptionButton4.Value = False
OptionButton5.Value = False
OptionButton6.Value = False

End Sub

I'd use a variable at the top of that userform module:

Option Explicit
Dim BlkProc as boolean
Private Sub Userform_Initialize()

blkproc = true
me.textbox1.value = "hi there"
blkproc = false

End Sub
Private Sub TextBox1_Change()

if blkproc = true then exit sub

blkproc = true
Me.TextBox1.value = UCase(Me.TextBox1.Text)
blkproc = false

OptionButton1.Value = False
OptionButton2.Value = False
OptionButton3.Value = False
OptionButton4.Value = False
OptionButton5.Value = False
OptionButton6.Value = False

End Sub

I like this because I can use the same idea whenever I change that
textbox--including making it uppercase!


"Patrick C. Simonds" wrote:

On my UserForm is TextBox1, which if a user makes a change to the TextBox I
want the value of some OptionButtons set to false. That in its self is easy
enough, using the TextBox1_Change routine below. My problem is that during
UserForm Initialization TextBox1 is populated with the following code:

TextBox1.Value = rng(1, 7).Text

Unfortunately the TextBox1_Change routine sees that as a change and sets
all OptionButtons to False. Any suggestions?

Private Sub TextBox1_Change()

Me.TextBox1 = UCase(Me.TextBox1.Text)

OptionButton1.Value = False
OptionButton2.Value = False
OptionButton3.Value = False
OptionButton4.Value = False
OptionButton5.Value = False
OptionButton6.Value = False

End Sub

--

Dave Peterson