ComboBox error

[Yappa]

Hi,

I am trying to get a combo box to work, but keep encountering the error "Invalid Property Value."

When the combobox entry is deleted and the user moves to the next text box in the userform, this error pops up which is very annoying. It also pops up when the word entered doesn't match, like it is supposed to.

I have MatchRequired set to True, because I want an error message to come up, but with my own error message like " That name doesn't exist, please try again ".

I can't figure out a way to ignore the "Invalid Property Value." error message, and show my own customized message.

Can anyone help?

Thanks,

Yappa

[Richard Buttrey]

Yappa[/QUOTE]

What's the code behind any of the combobox events and what are the entries you're making? Also what's the code behind the text box events?

Rgds

[Yappa]

The reason I am trying a combobox is to eliminate errors by the user, by having existing name entries stored in a sheet column eg.

A1 Lounge
A2 Bed
A3 Car
A4 Desk
A5 Lamp

so when the user enters a name that doesn't exist, a message should appear like " That name doesn't exist, please try again ".

I have a userform that works fine which has many text boxes, but at the moment, the user can enter in a name that doesn't exist, or enter a typing error, and the related macros will not run. I am merely trying out one combobox for now, which should be like an error trapping device, as described earlier.

I'm not sure, but I'm also trying to find out if text boxes can be populated the same way.

My codes are not the problem, I am unsure how to code the combobox property so it ignores the default error message and show mine.

Yappa

[Richard Buttrey]

Hi,

I asked what the code behind the combobox was, because that could be the cause of your error message.

However before going further down this track, is there any reason why you don't use a ListBox which is populated with all the allowable values. i.e. in a similar fashion to the standard Data Validation functionality of Excel itself.

If you've got a predefined list of allowable values, why would you want to allow the user a free choice? Just name the range A1:A5 say 'Products' and enter that name in the RowSource Property of the Listbox. You could then if necessary, depending on what you want to do, also use the ControlSource property to capture the value of the ListBox selection and have it entered in a cell in the spreadsheet.

HTH

[Norie]

Yappa

If MatchRequired is true then you can't avoid getting that message.

What you need to do is check yourself if a value from the list has been chosen.

That could be as easy as checking the ListIndex when the combobox is exited.

If it's -1 then nothing from the list has been selected.

[Yappa]

Hi Richard,

Sorry I didn't understand you correctly, I actually didn't have any underlying code related to the combobox, that's what I was trying to figure out.

However, I have found a way to make the text box work to the manner I was looking for using AfterUpdate, I'll share the code:

Dim rg as range

Private Sub txtProd_AfterUpdate()

Application.ScreenUpdating = False

Prod = txtProd.Text

If Prod = "" Then GoTo Line2

On Error GoTo Line1
Sheets("Items").Select
With ThisWorkbook.Worksheets("Items")
Set rg = WorksheetFunction.Index(Range("A:A"), _
         WorksheetFunction.Match(Prod, Range("A:A"), 0))
End With
Sheets("Collection").Select
Exit Sub

Line1:
Sheets("Collection").Select
MsgBox "That Product Doesn't Exist, Try Again", , "Invalid Product Name"
txtProd.Text = ""
txtProd.SetFocus

Line2:
Application.ScreenUpdating = True
Exit Sub

End Sub

As you said Richard, why give the user a free choice, which is what the combobox seems to let you do. This direction was just part of the path to a solution. I haven't dealt with comboboxes or listboxes before.

This code seems to work fine, although I am having trouble with the cursor going back to the txtProd text box after the error message and it being cleared. It seems the txtProd.SetFocus command doesn't work?

If you would like to help me with this that would be great, and also anything else you would like to comment on is welcome.

Norie, thanks for your reply also, I have edited this reply to recognise your support.

Thanks,

Yappa

[Richard Buttrey]

Thanks for taking the trouble to share the code with others. Would that other members were so diligent.

As the old adage goes, if it ain't broke don't fix it - which is almost certainly the default for matters programming

That said you might find the ListBox more useful for you in this context for the reasons I mentioned earlier.

The SetFocus is working but I guess your problem is that after the focus is given to the txtProd box, the code still continues to the End Sub which of course releases all references and focus. You could try experimenting with a
Do....While loop, - untested but probably something like:

Do While txtProd =""
   txtProd.SetFocus
Loop

HTH