How to link specific worksheet to my combobox?

[John_Though]

Hello forum, can someone please help with this problem?

Objective: To be able to have specific worksheets in the combobox drop down and navigate to desired worksheet by selecting it.

Problem: The script I found online links all of my worksheets to the combobox. I cannot figure out how to separate them and only have my desired worksheets in the drop down.

My VBA knowledge: no knowledge, no prior coding knowledge either.

Desired result: To have only 3 worksheets in combobox drop down, instead of all of them, for example: worksheet1, worksheet2, worksheet3 are in drop down list of combobox, while worksheet4, worksheet5, are hidden.

Q: Why do I need this?
A: I am creating an inventory form, and I want to separate around 20+ worksheets in 1 excel document to two categories: current and archived. My UserForm will have two comboboxes, combobox #1 will show current worksheets and combobox #2 will show archived worksheets.

Script:

Private Sub ComboBox_Current_Change()
'Updateby Extendoffice
If ComboBox_Current.ListIndex > -1 Then Sheets(ComboBox_Current.Text).Select

End Sub

Private Sub ComboBox_Current_DropButt*******()
Dim xSheet As Worksheet
On Error Resume Next
Application.ScreenUpdating = False
Application.EnableEvents = False
If ComboBox_Current.ListCount <> ThisWorkbook.Sheets.Count Then
ComboBox_Current.Clear
For Each xSheet In ThisWorkbook.Sheets
ComboBox_Current.AddItem xSheet.Name
Next xSheet
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Private Sub ComboBox_Current_GotFocus()
If ComboBox_Current.ListCount <> 0 Then ComboBox_Current.DropDown
End Sub

 

[mikerickson]

I don't know how you the program is to differentiate between Archive and Current worksheets, but the place to put that test in is

For Each xSheet In ThisWorkbook.Sheets
    [COLOR="#FF0000"]If [xSheet is a current one] Then[/COLOR]
        ComboBox_Current.AddItem xSheet.Name
    [COLOR="#FF0000"]End If[/COLOR]
Next xSheet

Although I suspect that

[COLOR="#FF0000"]If Not [xSheet is an archive] Then[/COLOR]

would be more likely.

 

[John_Though]

I don't know how you the program is to differentiate between Archive and Current worksheets, but the place to put that test in is

For Each xSheet In ThisWorkbook.Sheets
    [COLOR=#FF0000]If [xSheet is a current one] Then[/COLOR]
        ComboBox_Current.AddItem xSheet.Name
    [COLOR=#FF0000]End If[/COLOR]
Next xSheet

Although I suspect that

[COLOR=#FF0000]If Not [xSheet is an archive] Then[/COLOR]

would be more likely.

Hi Mike,

I don't have a code to differentiate or anything liket that.
I just created a brand new userform and dragged a combobox to it, then I went to "View Code" for the combobox and pasted the above script
My plan is to add some worksheets to combobox #1 and then hide (right click and select hide in the Excel) on the worksheets I don't need

for some reason adjusting the code to your recommended still shows all the worksheets in combobox drop down list?

 

[mikerickson]

The part in brackets isn't code, its pseudo code indicating what the code is supposed to be doing at that point.
How is the code to distinguish which sheets are archive and which are current?
Do you have a fixed number of archive sheets with known names?
Do you have a naming convention for the sheets so that looking at the name will tell you (or VBA) which is which?
Do either all the current or all the archive sheets have exactly the same lay-out that is unique to that kind of sheet?

It looks like your userform might add to the number of current sheets while it is invoked. Will the number of archive sheets change while the userform is in play?

 

[John_Though]

I feel like I made it way more complicated.
Let me try and reword it. Also, can we ignore the script I provided for a minute?

Just so that I stay on track, let's imagine this: I start brand new excel document, by default the first sheet is "Sheet1", now imagine I added "Sheet2", "Sheet3", "Sheet4"
in total that gives me 4 sheets.

I then press Alt + F11 to go to VBA, I select ToolBox to add combobox, then I name it ComboBox_Current, when I select "viewcode" I get:

Private Sub ComboBox_Current_Change()

End Sub

How do I write the code to tell combobox to only display Sheet1 and Sheet2, and to be able to switch between those two sheets?

also, my sheets are named " laptops uptodate", "pc uptodate", "server uptodate" that i want to see in the drop down

just to answer your questions:

How is the code to distinguish which sheets are archive and which are current?
A: I don't want the code to distinguish that, I will manually hide the sheets (archive) I don't need

Do you have a fixed number of archive sheets with known names?
A: when i say archive, i mean i want to Hide sheets i no longer need but still need for record puposes

Do either all the current or all the archive sheets have exactly the same lay-out that is unique to that kind of sheet?
A: All sheets just have normal data text and numbers (no formulas, scripts or anything of the like for used on the cells) but not all start from cell A1, some data starts from cell C1 for example

 

[mikerickson]

It sounds like these are ActiveX controls. My Mac doesn't support ActiveX, but from reading threads in the forum, I think that this will populate the List box with those two sheets.

Private Sub ComboBox1_DropButt*******()
    With ComboBox1
        .Clear
        .AddItem "Sheet1"
        .AddItem "Sheet2"
    End With
End Sub

Private Sub ComboBox1_Click()
    With ComboBox1
        If .ListIndex <> -1 Then
            ThisWorkbook.Sheets(.List(.ListIndex)).Activate
        End If
    End With
End Sub

 

[John_Though]

I got no errors running this code but drop down is still empty for some reason?

I started a brand new excel book and created worksheet 1, 2, and 3 and combobox dropdown is also empty on those. Any ideas?

 

[John_Though]

bump. Hoping to get more experts to see it

 

[Fluff]

How about

Private Sub ComboBox1_Click()
   If Me.ComboBox1.Value <> "" Then Sheets(Me.ComboBox1.Value).Activate
End Sub


Private Sub UserForm_Initialize()
   Dim Ws As Worksheet
   
   For Each Ws In Worksheets
      If Ws.Visible = xlSheetVisible Then
        Me.ComboBox1.AddItem Ws.Name
      Else
         Me.ComboBox2.AddItem Ws.Name
      End If
   Next Ws
End Sub

You will need to remove the code in the DropButton Click event.

 

[John_Though]

This worked! This is perfect man, thanks a lot Just what I was looking for! Now I can continue with my project.