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

kbishop94

Active Member
Joined
Dec 5, 2016
Messages
301
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.
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,710
Office Version
  1. 365
Platform
  1. Windows
This works (the list in A2:A6 is the equivalent of your list)

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

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,710
Office Version
  1. 365
Platform
  1. Windows
or
If user goes to combobox2 without putting a value in combobox1, mouseover changes contents in combobox1 to "-"

Code:
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

Active Member
Joined
Dec 5, 2016
Messages
301
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,710
Office Version
  1. 365
Platform
  1. Windows
Try this
Code:
    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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,704
Messages
5,524,416
Members
409,576
Latest member
az168

This Week's Hot Topics

Top