CODEWITHSUNDEEP
excelvba
Tristan300
Tristan300

Reputation: 1

How to select a value from my combobox list?

I have code that populates my combobox with sheet names and updates whenever I click the dropdown button.

The problem is I cannot select a value in the combobox lists. I have also two hidden sheets that I do not want the combobox to populate.

Here is the code:

Private Sub REFRESH_COMBOBOX1()
Dim ws As Worksheet
ComboBox1.Clear
For Each ws In ThisWorkbook.Sheets
    If ws.Visible Then ComboBox1.AddItem(ws.Name)
Next ws
End Sub


Private Sub ComboBox1_DropButtonClick()
REFRESH_COMBOBOX1
End Sub

Upvotes: 0

Views: 392

Answers (3)

jamheadart
jamheadart

Reputation: 5343

If you MUST use the DropButtonClick event then use this slightly different method of clearing the combobox:

Sub REFRESH_COMBOBOX1()
Dim ws As Worksheet
Dim i As Long
Do While ComboBox1.ListCount > 0
    ComboBox1.RemoveItem (0)
Loop
For Each ws In ThisWorkbook.Sheets
    If ws.Visible Then ComboBox1.AddItem (ws.Name)
Next ws
End Sub

Instead of using the in-built .Clear function which will also remove the text inside the box, it will just remove items from the list and then re-add them

This means when you select an item, it won't be cleared even though the "REFRESH_COMBOBOX1" routine is called again.

Upvotes: 0

jamheadart
jamheadart

Reputation: 5343

Try this:

Private Sub REFRESH_COMBOBOX1() ' Routine for refreshing the list of names in box

    Dim ws As Worksheet     ' need a worksheet variable
    ComboBox1.Clear         ' clear the current list

  ' Cycle through every worksheet in the book, if it is NOT hidden then add name to list
    For Each ws In ThisWorkbook.Sheets    
        If ws.Visible Then ComboBox1.AddItem (ws.Name)  
    Next ws
End Sub ' 


' Separate routine to register the user putting focus on the box
' This happens every time the user clicks on or enters ComboBox1
Private Sub ComboBox1_Enter()
    REFRESH_COMBOBOX1 ' Calling the routine above
End Sub

Upvotes: 1

David Zemens
David Zemens

Reputation: 53663

combobox keeps adding the same sheet names when i move the mouse

Well, of course it does. You have an event procedure that's tied to moving the mouse, and your procedure is adding every sheet name to the list, with no check to see if the sheet already exist, etc. This should almost certainly be handled in a different event(s).

Probably, you want to populate this list from the form's Activate or Initialize event handler. So, somewhere in one of those methods, you'll call a procedure that clears & refreshes the ComboBox, like:

Call RefreshSheetList()

That function would look like so, and should be part of the User Form's code module:

Private Sub RefreshSheetList()
With ComboBox1
    .Clear
    For n=1 To ActiveWorkbook.Sheets.Count
        .AddItem ActiveWorkbook.Sheets(n).Name
    Next n
End With
End Sub

If the form itself changes the environment (i.e., adds, renames or removes existing sheets) then the appropriate place to repopulate the ComboBox would be in the event handler that performs that action.

E.g., you have a CommandButton1_Click which adds a new sheet to the workbook (just as an example) then you would call it after the action has been performed, like so:

Sub CommandButton1_Click()
Dim w as Worksheet
With ActiveWorkbook
    Set w = .Worksheets.Add(After:=.Worksheets(.Worksheets.Count))
    w.Name = Me.TextBox1.Text
End With
Call RefreshSheetList()
End Sub

Upvotes: 1

Related Questions