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
382
Office Version
  1. 2010
Platform
  1. Windows
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.
icon14.png
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
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,977
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
382
Office Version
  1. 2010
Platform
  1. Windows
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,977
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,477
Messages
5,831,909
Members
430,090
Latest member
bjonesh2o

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top