TextBox Change/Exit problem
 

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

TextBox Change/Exit problem
 

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

TextBox Change/Exit problem
 

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

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