I want a code that will make a combo box blank out if someone enters a value
other than a value in sheets("sheet1").range("a1:a10")


--
Thanks
Shawn

Be careful with this code. You may have to change a value in the testrange
to get the combobox enabled after it has been disabled. also so I used
Combobox1.Text, you can also use Combobox1.value. Not usre if you are
comparing text or numbers.

Sub disablecombbox()

Set testrange = Sheets("sheet1").Range("a1:a10")
found = False
For Each cell In testrange

If cell = ComboBox1.Text Then
found = True
Exit For
End If
Next cell

If found = True Then
ComboBox1.enable = True
Else
ComboBox1.enable = False
End If

End Sub


"Shawn" wrote:

I want a code that will make a combo box blank out if someone enters a value
other than a value in sheets("sheet1").range("a1:a10")


--
Thanks
Shawn

Numbers for some

text for others
--
Thanks
Shawn


"Joel" wrote:

Be careful with this code. You may have to change a value in the testrange
to get the combobox enabled after it has been disabled. also so I used
Combobox1.Text, you can also use Combobox1.value. Not usre if you are
comparing text or numbers.

Sub disablecombbox()

Set testrange = Sheets("sheet1").Range("a1:a10")
found = False
For Each cell In testrange

If cell = ComboBox1.Text Then
found = True
Exit For
End If
Next cell

If found = True Then
ComboBox1.enable = True
Else
ComboBox1.enable = False
End If

End Sub


"Shawn" wrote:

I want a code that will make a combo box blank out if someone enters a value
other than a value in sheets("sheet1").range("a1:a10")


--
Thanks
Shawn

Below is my code and it works good exept, when

If found = False Then
usfDemo.cmbxDOBMonth.Value = ""

I want it also re-select usfDemo.cmbxDOBMonth

I try usfDemo.cmbxDOBMonth.setfocus but that doesn't work

Private Sub cmbxDOBMonth_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Set testrange = Sheets("Demo").Range("A2:A13")
found = False

For Each cell In testrange

If cell = usfDemo.cmbxDOBMonth.Value Then
found = True
Exit For
End If
Next cell

If found = False Then
usfDemo.cmbxDOBMonth.Value = ""
End If
End Sub
--
Thanks
Shawn


"Joel" wrote:

Be careful with this code. You may have to change a value in the testrange
to get the combobox enabled after it has been disabled. also so I used
Combobox1.Text, you can also use Combobox1.value. Not usre if you are
comparing text or numbers.

Sub disablecombbox()

Set testrange = Sheets("sheet1").Range("a1:a10")
found = False
For Each cell In testrange

If cell = ComboBox1.Text Then
found = True
Exit For
End If
Next cell

If found = True Then
ComboBox1.enable = True
Else
ComboBox1.enable = False
End If

End Sub


"Shawn" wrote:

I want a code that will make a combo box blank out if someone enters a value
other than a value in sheets("sheet1").range("a1:a10")


--
Thanks
Shawn

Why not change the style property of the combobox from fmStyleDropDownCombo
to fmStyleDropdownList. Then the user has to select from the list which will
of course be the list of acceptable values.

If you want to allow typing into the textbox region, then leave ti as
fmStyleDropDownCombo and and change the MatchRequired property to True.

from help on MatchRequired:
Specifies whether a value entered in the text portion of a ComboBox must
match an entry in the existing list portion of the control. The user can
enter non-matching values, but may not leave the control until a matching
value is entered.


--
Regards,
Tom Ogilvy

"Shawn" wrote:

Below is my code and it works good exept, when

If found = False Then
usfDemo.cmbxDOBMonth.Value = ""

I want it also re-select usfDemo.cmbxDOBMonth

I try usfDemo.cmbxDOBMonth.setfocus but that doesn't work

Private Sub cmbxDOBMonth_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Set testrange = Sheets("Demo").Range("A2:A13")
found = False

For Each cell In testrange

If cell = usfDemo.cmbxDOBMonth.Value Then
found = True
Exit For
End If
Next cell

If found = False Then
usfDemo.cmbxDOBMonth.Value = ""
End If
End Sub
--
Thanks
Shawn


"Joel" wrote:

Be careful with this code. You may have to change a value in the testrange
to get the combobox enabled after it has been disabled. also so I used
Combobox1.Text, you can also use Combobox1.value. Not usre if you are
comparing text or numbers.

Sub disablecombbox()

Set testrange = Sheets("sheet1").Range("a1:a10")
found = False
For Each cell In testrange

If cell = ComboBox1.Text Then
found = True
Exit For
End If
Next cell

If found = True Then
ComboBox1.enable = True
Else
ComboBox1.enable = False
End If

End Sub


"Shawn" wrote:

I want a code that will make a combo box blank out if someone enters a value
other than a value in sheets("sheet1").range("a1:a10")


--
Thanks
Shawn

I have so much to learn. That works great.
--
Thanks
Shawn


"Tom Ogilvy" wrote:

Why not change the style property of the combobox from fmStyleDropDownCombo
to fmStyleDropdownList. Then the user has to select from the list which will
of course be the list of acceptable values.

If you want to allow typing into the textbox region, then leave ti as
fmStyleDropDownCombo and and change the MatchRequired property to True.

from help on MatchRequired:
Specifies whether a value entered in the text portion of a ComboBox must
match an entry in the existing list portion of the control. The user can
enter non-matching values, but may not leave the control until a matching
value is entered.


--
Regards,
Tom Ogilvy

"Shawn" wrote:

Below is my code and it works good exept, when

If found = False Then
usfDemo.cmbxDOBMonth.Value = ""

I want it also re-select usfDemo.cmbxDOBMonth

I try usfDemo.cmbxDOBMonth.setfocus but that doesn't work

Private Sub cmbxDOBMonth_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Set testrange = Sheets("Demo").Range("A2:A13")
found = False

For Each cell In testrange

If cell = usfDemo.cmbxDOBMonth.Value Then
found = True
Exit For
End If
Next cell

If found = False Then
usfDemo.cmbxDOBMonth.Value = ""
End If
End Sub
--
Thanks
Shawn


"Joel" wrote:

Be careful with this code. You may have to change a value in the testrange
to get the combobox enabled after it has been disabled. also so I used
Combobox1.Text, you can also use Combobox1.value. Not usre if you are
comparing text or numbers.

Sub disablecombbox()

Set testrange = Sheets("sheet1").Range("a1:a10")
found = False
For Each cell In testrange

If cell = ComboBox1.Text Then
found = True
Exit For
End If
Next cell

If found = True Then
ComboBox1.enable = True
Else
ComboBox1.enable = False
End If

End Sub


"Shawn" wrote:

I want a code that will make a combo box blank out if someone enters a value
other than a value in sheets("sheet1").range("a1:a10")


--
Thanks
Shawn