Combo box matchrequired and allow blanks 1

[Epsilon101]

Hi,

I have got a combobox (CboTitle) with match required = true

I was wondering how to stop run time error 380 from occuring when the information in the combo box is blank.

Ive tried a few things in CboTitle_BeforeUpdate but nothing has worked as yet.

I was trying to get it to check
if CboTitle.value = "" then
Ignore error 380

simple enough but i dont know how to set it up to ignore an error in Excel VBA.

Any help would be groovy


---------------------------------------

Neil

 

[jcrater]

method 1: Add a blank record to the combobox

method 2: in the BeforeUpdate event, loop through the listitems in the combo and compare them yourself to the new value.

method 3: if it's possible, set up an errorhandler and do something like
if err.number = 380 then resume next

 

[Epsilon101]

Hi, thanks for the reply, just got to ask what is the purpose of method 2, also how can it be done??

Doesnt matchrequired = true do that for me??

---------------------------------------

Neil

 

[Epsilon101]

Got a problem, this form is to be maintained not by me, i have setup a maintenance form to edit the text that is displayed in these combo boxes. If i leave one of the cells blank, no doubt someone will remove it and mess everything up.

So i need something that checks to see if the combobox.value = "" when it changes and before the error 380 occurs, then stop error 380 from occuring if the value = "".

I tried putting If err.number = 380 then resume next into, .afterupdate, .beforeupdate, and also tried

Private Sub CboPassed_Change()
If Err.Number = 380 Then
If CboPassed.Value = "" Then
Resume Next
End If
End If
End Sub

but that didnt work either.

---------------------------------------

Neil

 

[jcrater]

Do you need to allow the user to add entries dynamically to the combo? If not, set the style to fmStyleDropDownList or use a simple listbox instead. What's the purpose of the combo exactly?

 

[Epsilon101]

An example of one combo box is the title of someone.

Drop down list of Mr, Mrs, Dr, and so on...

Because this combobox isnt one of my required fields, they can leave it blank or only choose one of the values in the combobox.

Since i cant include a blank cell in the combobox i need to find a way to have matchrequired = true but still let the user leave the combobox blank.

If they dont touch the box it stays blank and thats ok, but if they enter something and then delete it, the error 380 just keeps occuring.

I have seen FmStyleDropDownList as well, but it also wont allow a blank and its worse because if someone entering into my form is tabbing and typing, they will most likely end up selecting the wrong information and once this data is logged they arent going to be able to see it or delete it.

I am keeping it hidden and protected, and only unpretecting and protecting again when they right to it, using the command button CmdCreate.


---------------------------------------

Neil

 

[Epsilon101]

Its the same idea as in MS Access, with the combobox being set to limittolist i think.

I have already set this log up in Access but my users cant maintain it, so they have asked for it in Excel.

---------------------------------------

Neil

 

[jcrater]

Aren't blanks allowed? This seems to work for me:

Dim iRow As Integer

iRow = 1

ComboBox1.AddItem ""

Do Until Trim(Cells(iRow, 1).Value) = ""
ComboBox1.AddItem Trim(Cells(iRow, 1).Value)
iRow = iRow + 1
Loop

 

[Epsilon101]

Thanks for that jcrater

Just wanted to ask as well, do you know how to change the description of an error?

As i know someone will get the error because they wont select the blank in the list, so i want to change the description so they understand whats going on.

---------------------------------------

Neil

 

[jcrater]

Just set Err.Description to whatever string you want to use.

I still don't understand why you wouldn't use the fmStyleDropDownList style -- if you're wanting to prevent the user from entering anything that doesn't already exist in the list.

 

[Epsilon101]

Since i have got blanks create in the comboboxes now i will use fmStyleDropDownlist, its just my users are crap and they will most likely type without looking and press keys at the wrong time which will cause it to move to a different selection in the combobox.




---------------------------------------

Neil

 

[Epsilon101]

If i am doing the Err.Description changing, where do i add it?? because i was trying that and i couldnt get it to work.

---------------------------------------

Neil

 

[PHV]

Take a look at the On Error GoTo instruction in the VBA help.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244