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

 

[Fluff]

You're welcome & thanks for the feedback