PDA

View Full Version : Clearing entry from VBA Combo Box



jimbokentuck
09-14-2008, 06:09 PM
Hi my combo box has two choices, YES and NO. If the user selects Yes and Select the box hides but the next time it opens it shows Yes again is there any way to make the box set to Blank? the code I'm using is below.

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("ag63").Value = 1 Then
cboGSTINPUT.Show
Exit Sub
End If
If Range("ag63").Value = 0 Then
cboGSTINPUT.Hide
Exit Sub
End If
End Sub

mikerickson
09-14-2008, 06:20 PM
Why use a combo box for two choices. CheckBoxes, or ToggleButtons are designed for binary (yes/no) inputs.

To clear a combobox
cboGSTINPUT.ListIndex = -1

jimbokentuck
09-14-2008, 06:25 PM
The combo box asks a question to reinforce that the user really wants proceed with his entry.

Where should I put the code that you sent?

mikerickson
09-14-2008, 06:30 PM
Either before the .Show or after the .Hide

jimbokentuck
09-14-2008, 07:29 PM
I"m still confused as to how to apply this :dunno

Any help would be appreciated.

mikerickson
09-14-2008, 08:46 PM
Put that line into your routine.
Putting it either before the .Show line or after the .Hide line will give the user the same experience. The only difference is that after the .Hide line will make the user's previous choice unavaliable for being polled by other routines, that may run while the combobox is hidden.

Considering that it, I would think that before the .Show line would be the best place to put that code.

david000
09-15-2008, 11:23 PM
Private Sub Worksheet_Activate()
With Me.ComboBox1
.ListIndex = -1
.ListFillRange = "b1:b2" 'your list wherever it is with 'Yes and No' in it.
End With
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Range("a1") 'your range here.
If Target.Value = 1 Then
Me.ComboBox1.Visible = True
ElseIf Target.Value = 0 Then
Me.ComboBox1.Visible = False
End If
End Sub