Combo Box - Set Value in Macro
 

Hi, I have a combobox (cboReview), I would like to set its value (to "N/A")
through a Macro that also does a few other things. I'm can't figure out the
correct syntax.
Also, does the value I set it to need to be an item in the Range that
provides the list or not (N/A is in the range but doesn't need to be if that
isn't required)? Stored keystrokes didn't help at all. Thank you. Karin R.

Combo Box - Set Value in Macro
 

I'm guessing that this is a combobox on a userform--not a dropdown from the
Forms toolbar placed on a worksheet and not a combobox from the control toolbox
toolbar placed on a worksheet.

If that's true, then it depends on the style of the combobox.

If you look at the properties for that combobox, you'll see a Style property.

That has two options--one is fmStyleDropDownCombo and the other is
fmStyleDropDownList.

The List means that it has to match something in the list.

The Combo means that a second property is looked at (MatchRequired). When I set
that to true, I could change the value displayed in that combobox to look like
#n/a, but I couldn't leave the combobox without changing it to something on that
list.

This was behind my test userform:

Option Explicit
Private Sub CommandButton1_Click()
If Me.ComboBox1.ListIndex < 0 Then
Beep 'nothing selected
Else
MsgBox Me.ComboBox1.Value
End If
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub CommandButton3_Click()
Me.ComboBox1.Value = "#N/A"
End Sub
Private Sub UserForm_Initialize()
With Me.ComboBox1
.RowSource = ThisWorkbook.Worksheets("Sheet1") _
.Range("A1:a10").Address(external:=True)
.Style = fmStyleDropDownCombo
.MatchRequired = True
End With
End Sub

========
I'm not sure what you're doing, but instead of showing #N/A in the combobox,
maybe you could use a label and even disable the combobox.


Karin wrote:

Hi, I have a combobox (cboReview), I would like to set its value (to "N/A")
through a Macro that also does a few other things. I'm can't figure out the
correct syntax.
Also, does the value I set it to need to be an item in the Range that
provides the list or not (N/A is in the range but doesn't need to be if that
isn't required)? Stored keystrokes didn't help at all. Thank you. Karin R.

--

Dave Peterson

Combo Box - Set Value in Macro
 

Thank you - and now I know to be more specific about what type of combo box
- sorry, it came from the control box. What I ended up doing was hiding the
box and typing the N/A in the cell using a macro (based on the results of a
different combo box).



"Dave Peterson" wrote:

I'm guessing that this is a combobox on a userform--not a dropdown from the
Forms toolbar placed on a worksheet and not a combobox from the control toolbox
toolbar placed on a worksheet.

If that's true, then it depends on the style of the combobox.

If you look at the properties for that combobox, you'll see a Style property.

That has two options--one is fmStyleDropDownCombo and the other is
fmStyleDropDownList.

The List means that it has to match something in the list.

The Combo means that a second property is looked at (MatchRequired). When I set
that to true, I could change the value displayed in that combobox to look like
#n/a, but I couldn't leave the combobox without changing it to something on that
list.

This was behind my test userform:

Option Explicit
Private Sub CommandButton1_Click()
If Me.ComboBox1.ListIndex < 0 Then
Beep 'nothing selected
Else
MsgBox Me.ComboBox1.Value
End If
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub CommandButton3_Click()
Me.ComboBox1.Value = "#N/A"
End Sub
Private Sub UserForm_Initialize()
With Me.ComboBox1
.RowSource = ThisWorkbook.Worksheets("Sheet1") _
.Range("A1:a10").Address(external:=True)
.Style = fmStyleDropDownCombo
.MatchRequired = True
End With
End Sub

========
I'm not sure what you're doing, but instead of showing #N/A in the combobox,
maybe you could use a label and even disable the combobox.


Karin wrote:

Hi, I have a combobox (cboReview), I would like to set its value (to "N/A")
through a Macro that also does a few other things. I'm can't figure out the
correct syntax.
Also, does the value I set it to need to be an item in the Range that
provides the list or not (N/A is in the range but doesn't need to be if that
isn't required)? Stored keystrokes didn't help at all. Thank you. Karin R.

--

Dave Peterson