Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox Error Handling
I have some ComboBoxes on a form. I set MatchRequired = True. If I fill
these boxes with data from a worksheet (through VBA), it will let me use items that are not found in the required list. In other words, the required match is not checked until the user enters then exits the combobox. How do I check that the MatchRequired has been met if the user is not going to go in, then out, of every combobox? Example: the combobox is named ComboBoxColors the validated list contains these items: "Blue" "Green" "Red" If I use VBA and say, ComboBoxColors.Value = "Pink" (value from a worksheet), it will set the value = "Pink". If I then enter the combobox, then leave it, I will get an error that this is not a legal entry. How can I trap this error at the time I copy the value from the worksheet? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox Error Handling
Sorry i am a little confused. I am not clear on whether you want the user to be able to type into the Combobox ? if not the below will work If this is not quite what you want let me know. Private Sub ComboBox1_Change() If ComboBox1.ListIndex < 0 Then ComboBox1.ListIndex = 0 End Sub steve wrote: I have some ComboBoxes on a form. I set MatchRequired = True. If I fill these boxes with data from a worksheet (through VBA), it will let me use items that are not found in the required list. In other words, the required match is not checked until the user enters then exits the combobox. How do I check that the MatchRequired has been met if the user is not going to go in, then out, of every combobox? Example: the combobox is named ComboBoxColors the validated list contains these items: "Blue" "Green" "Red" If I use VBA and say, ComboBoxColors.Value = "Pink" (value from a worksheet), it will set the value = "Pink". If I then enter the combobox, then leave it, I will get an error that this is not a legal entry. How can I trap this error at the time I copy the value from the worksheet? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox Error Handling
Why not check to see if the value is on the list before you plop it into the
combobox? steve wrote: I have some ComboBoxes on a form. I set MatchRequired = True. If I fill these boxes with data from a worksheet (through VBA), it will let me use items that are not found in the required list. In other words, the required match is not checked until the user enters then exits the combobox. How do I check that the MatchRequired has been met if the user is not going to go in, then out, of every combobox? Example: the combobox is named ComboBoxColors the validated list contains these items: "Blue" "Green" "Red" If I use VBA and say, ComboBoxColors.Value = "Pink" (value from a worksheet), it will set the value = "Pink". If I then enter the combobox, then leave it, I will get an error that this is not a legal entry. How can I trap this error at the time I copy the value from the worksheet? -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox Error Handling
Dave,
How do I check if it is on the list before I put it in? "Dave Peterson" wrote: Why not check to see if the value is on the list before you plop it into the combobox? steve wrote: I have some ComboBoxes on a form. I set MatchRequired = True. If I fill these boxes with data from a worksheet (through VBA), it will let me use items that are not found in the required list. In other words, the required match is not checked until the user enters then exits the combobox. How do I check that the MatchRequired has been met if the user is not going to go in, then out, of every combobox? Example: the combobox is named ComboBoxColors the validated list contains these items: "Blue" "Green" "Red" If I use VBA and say, ComboBoxColors.Value = "Pink" (value from a worksheet), it will set the value = "Pink". If I then enter the combobox, then leave it, I will get an error that this is not a legal entry. How can I trap this error at the time I copy the value from the worksheet? -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox Error Handling
Is the list on a worksheet somewhere?
dim res as variant dim someval as variant dim myRng as range with worksheets("somesheet") set myrng = .range("a1",.cells(.rows.count,"A").end(xlup)) end with res = application.match(someval, myrng,0) if iserror(res) then 'not there else 'was there end if ===== If the combobox was already populated (some other way??), then maybe something like this: Option Explicit Private Sub CommandButton1_Click() Dim res As Variant Dim myArr() As Variant Dim iCtr As Long If Trim(Me.TextBox1.Value) = "" Then Beep Exit Sub End If ReDim myArr(0 To Me.ComboBox1.ListCount - 1) For iCtr = LBound(myArr) To UBound(myArr) myArr(iCtr) = Me.ComboBox1.List(iCtr, 0) Next iCtr res = Application.Match(Me.TextBox1.Value, myArr, 0) If IsError(res) Then Beep MsgBox "not valid" Else MsgBox "It's a match!" End If End Sub steve wrote: Dave, How do I check if it is on the list before I put it in? "Dave Peterson" wrote: Why not check to see if the value is on the list before you plop it into the combobox? steve wrote: I have some ComboBoxes on a form. I set MatchRequired = True. If I fill these boxes with data from a worksheet (through VBA), it will let me use items that are not found in the required list. In other words, the required match is not checked until the user enters then exits the combobox. How do I check that the MatchRequired has been met if the user is not going to go in, then out, of every combobox? Example: the combobox is named ComboBoxColors the validated list contains these items: "Blue" "Green" "Red" If I use VBA and say, ComboBoxColors.Value = "Pink" (value from a worksheet), it will set the value = "Pink". If I then enter the combobox, then leave it, I will get an error that this is not a legal entry. How can I trap this error at the time I copy the value from the worksheet? -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error Handling | Excel Discussion (Misc queries) | |||
Error Handling - On Error GoTo doesn't trap error successfully | Excel Programming | |||
Error handling with a handling routine | Excel Programming | |||
error handling | Excel Programming | |||
Error Handling | Excel Programming |