TextBox Change/Exit problem
Thanks
"Rick Rothstein" wrote in message
...
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.
--
Rick (MVP - Excel)
"Patrick C. Simonds" wrote in message
...
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
|