CODEWITHSUNDEEP
formsvbaexcelcombobox
Joe Dion
Joe Dion

Reputation: 1

Excel Form VBA Combobox reset

This should be easy but I can't seem to find anything to solve. I have a Form in Excel using VBA. The Excel sheet has four columns populated with data and the Form displays those fields.

The user then selects a value in a combobox, uses a command button to submit the responses and the values from the combobox are written to the Excel sheet.

The user then uses a command button to advance one row down in the spreadsheet and load the four values from a new row. This all works great.

The issue I am trying to solve is that the combobox remains selected to the value in the prior selection. I'd like to reset the combobox so nothing is selected and the user has to make a selection again for the next row.

Below is the code I am using to load the combobox and to set a variable for what the user selected. Can't seem to get the combobox back to it's default state after the user has submitted the form.

Private Sub cbDesAccWanted_Change()
    Select Case cbDesAccWanted.Text
        Case "Yes"
            desacc = "Yes"
        Case "No"
            desacc = "No"
    End Select
    cbDesAccWanted.Text = desacc
End Sub

Private Sub cbDesAccWanted_DropButtonClick()
    cbDesAccWanted.List = Array("Yes", "No")
End Sub

Upvotes: 0

Views: 8439

Answers (2)

HarveyFrench
HarveyFrench

Reputation: 4568

the line

cbDesAccWanted.Text = desacc

is totally unnecessary.

Using cbDesAccWanted_DropButtonClick is not the right place to populate the list of values. This list should be set up when the form is first shown to the user. (unelss the values it shows chnages in which case change it when the row changes or something, not when the user clicks on it)

So when theuser clicks the down arrow to move to thenext record include the following line 

Me.cbDesAccWanted.text = Me.cbDesAccWanted.List(1)

Note (1) access teh 2nd item in the list which is No.

So this reset it to the default value of No.

Ok.

Upvotes: 0

Excel Hero
Excel Hero

Reputation: 14764

There are two ways to reset the combobox. Take your pick:

1

cbDesAccWanted.Value = Null

2

cbDesAccWanted.ListIndex = -1

Upvotes: 2

Related Questions