code for allowing a character (single character) that is not part of the list in a combobox? (part of the "matchentrycomplete")

[kbishop94]

PROBLEM:
I have 2 forms in my workbook. An entry form, and a edit form. The problem I have is with the edit form.

For reasons too long to get into here, I have no blank cells within the spreadsheets range where all the data that is entered and is stored. When a cell is left blank (when entered when using the entry form), it automatically changes the null value to the " - " character (minus sign). When that same entry is populated on the EDIT FORM when someone needs to change something, any of the fields that happen to be a combobox, and if that field contains the " - " character, then it gives the error "INVALID PROPERTY ERROR"
How can I make that combobox allow the " - " character to be entered but also allow any entries to be made from the list?
(and aside from adding the " - " character to my existing lists that are used to populate the comboboxes.)

Please and Thank you for any and all help or assistance.

 

[Yongle]

This works (the list in A2:A6 is the equivalent of your list)

Private Sub UserForm_Initialize()
    Dim cel As Range
    ComboBox1.AddItem "-"
    For Each cel In Range("A2:A6")
        ComboBox1.AddItem cel
    Next
End Sub

 

[Yongle]

or
If user goes to combobox2 without putting a value in combobox1, mouseover changes contents in combobox1 to "-"

Private Sub ComboBox2_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    If ComboBox1.Value = "" Then ComboBox1.Value = "-"
End Sub

 

[kbishop94]

This works (the list in A2:A6 is the equivalent of your list)

It sure did!!! Many thanks, Yongle.

 

[kbishop94]

Just a follow up '?' to make sure that my code is stated correctly and in the most efficient way... (well, I know its correct because it works... but I'm curious if this is how it should look):

So I already had a similar looking code for my procedure for populating the combobox (which is called "cboOnBehalfOf1") and i joined your code into it instead of having a separate procedure... which both worked, btw, but combining them would be better... correct(?)

Here is my existing (prior to your provided code) for populating the combobox "cboOnBehalfOf1" under the private sub UserForm_Initialize:

Dim cBeh As Range
Dim ws As Worksheet
Set ws = Worksheets("LookupLists")

For Each cBeh In ws.Range("NAMES")
With Me.cboOnBehalfOf1
.AddItem cBeh.Value
End With

Next cCus

I took your new code you provided and combined it with the above code to get this: (which, again, works just fine, but I'm curious if its stated correctly or efficiently as it should (?))

Dim cBeh As Range
Dim cel As Range

Dim ws As Worksheet
Set ws = Worksheets("LookupLists")

For Each cBeh In ws.Range("Names")
With Me.cboOnBehalfOf1
.AddItem cBeh.Value
End With

cboOnBehalfOf1.AddItem "-"

For Each cel In ws.Range("Names")
cboOnBehalfOf1.AddItem cel
Next

Next cBeh

Thanks again

 

[Yongle]

Try this

    Dim cBeh As Range, ws As Worksheet
    Set ws = Worksheets("LookupLists")

    With Me.cboOnBehalfOf1
        .AddItem "-"
        For Each cBeh In ws.Range("Names")
            .AddItem cBeh.Value
        Next cBeh
    End With