TextBox Change/Exit problem

[Patrick C. Simonds]

Thought I would refine and try my question again:

On my UserForm is TextBox1, it is populated during UserForm Initialization
with the value of rng(1, 7). What I would like to have happen is:

If the user changes the contents of TextBox1 then all OptionButtons (which
are located in Frame1) would be set to False and the background and border
color of Frame1 would change, giving the user a visual clue that they need
to select an OptionButton.

My problem is that when the user exits the TextBox all the OptionButtons are
set to False and the color of Frame1 is changed. I have narrowed the problem
down to the TextBox1_Exit routine because the textbox value is set to blank
and then the rewritten with the value in from sheet2. Assuming no miss
spellings I do not understand why this should be a problem. With no miss
spellings the value written back into TextBox1 should still be the same as
the value in rng(1, 7), and my If statement compares those 2 values.


Private Sub TextBox1_Change()

If TextBox1.Value <> rng(1, 7) Then

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

Frame1.BackColor = &H80FFFF
Frame1.BorderColor = &HFF&

End If

End Sub

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Application.SpellingOptions.IgnoreCaps = False
Sheets("Sheet2").Unprotect

Sheets("Sheet2").Range("a1").Value = TextBox1.Text
Application.EnableEvents = False
Sheets("Sheet2").Range("a1").CheckSpelling
Application.EnableEvents = True
TextBox1.Text = ""
TextBox1.Text = Sheets("Sheet2").Range("a1").Value
Sheets("Sheet2").Range("a1").Value = ""

End Sub

 

[Rob Wills]

apologies for my syntax errors...

but something like this should work

dim ctrl as Control
dim blnOpt
blnOpt = true

for each ctrl in Userform.Controls
if left(ctrl.name,3) = "Opt" then
if ctrl.value then
blnopt = true
end if
end if
next ctrl

if not(blnOpt) then
'Cancel form exit
end if

 

[Rick Rothstein]

TextBox1.Text = ""

TextBox1.Text = Sheets("Sheet2").Range("a1").Value

There is no need to clear the TextBox before assigning a new value to it...
just assign the new value (that is, eliminate the "" assignment statement).
The problem you were encountering is from that unnecessary "" assignment...
it is a change in the text for the TextBox and, as such, triggers a Change
event... your Change event sees the value in the TextBox is not equal to
rng(1,7) and, so, performs its code. Removing the

TextBox1.Text = ""

statement should solve your problem.

 

[Patrick C. Simonds]

Thanks

There is no need to clear the TextBox before assigning a new value to
it... just assign the new value (that is, eliminate the "" assignment
statement). The problem you were encountering is from that unnecessary ""
assignment... it is a change in the text for the TextBox and, as such,
triggers a Change event... your Change event sees the value in the TextBox
is not equal to rng(1,7) and, so, performs its code. Removing the

TextBox1.Text = ""

statement should solve your problem.