How to use combobox to select different sheet?

[anthonymedz]

Hi guys,

Can you help me on this. I know this is simple to you. I have a project that i need to simply use a combobox in a userform to select a sheet per categories.
For example, if i have a category of Fruits and use the combo box it will have a drop down of mango and will select on that particular sheet.

Fruits(Combobox1)
Mango(sheet1)
Banana(sheet2)
Guava(sheet3)

Cars(Combobox2)
Toyota(Sheet4)
Honda(sheet5)
Mitsubishi(sheet6)

Thanks for the advance help.

 

[anthonymedz]

So you can see a sheet name in the Combobox Correct?
And if you click on the sheet name you should be taken to that sheet. If you have installed my script in the combobox.

i installed your script but its not working. I can't see sheet name in the combobox.

 

[Fluff]

How about

Private Sub ComboBox1_Click()
   Sheets(Me.ComboBox1.Value).Select
End Sub

Private Sub UserForm_Initialize()
   Dim Ws As Worksheet
   
   For Each Ws In Worksheets
      Me.ComboBox1.AddItem Ws.Name
   Next Ws
End Sub

 

[My Aswer Is This]

So tell me this:

In your combobox do you want to see all your sheet names?
If so put this script in A button.

And all sheet names will now be in your ComboBox named ComboBox1.

Private Sub CommandButton1_Click()
'Modified  2/21/2019  4:43:35 PM  EST
Dim i As Long
ComboBox1.Clear
For i = 1 To Sheets.Count
    ComboBox1.AddItem Sheets(i).Name
Next
End Sub

If your Combobox is in a Userform put this script in your Initialize code

 

[anthonymedz]

So tell me this:

In your combobox do you want to see all your sheet names?
If so put this script in A button.

And all sheet names will now be in your ComboBox named ComboBox1.

Private Sub CommandButton1_Click()
'Modified  2/21/2019  4:43:35 PM  EST
Dim i As Long
ComboBox1.Clear
For i = 1 To Sheets.Count
    ComboBox1.AddItem Sheets(i).Name
Next
End Sub

If your Combobox is in a Userform put this script in your Initialize code

Hi,
I tried your script but how about if i use another ComboBox(Combobox2) it will select again all sheets in the excel.
Is there any script that i can manually add an item for each combobox?
because your scirpt include all sheets in one combobox.

Just simply when i select combobox1 it will select mango and banana while combobox2 it will select honda and toyota.

thanks.

 

[jolivanes]

Re: when i select combobox1
What does that mean? How do you select a ComboBox?
Or do you mean that you have a UserForm with 2 ComboBoxes and you select from either one or the other?

 

[anthonymedz]

Re: when i select combobox1
What does that mean? How do you select a ComboBox?
Or do you mean that you have a UserForm with 2 ComboBoxes and you select from either one or the other?

Yes i have a userform with 2 or more comboboxes.

 

[jolivanes]

You have received code from 3 different people that all work as far as we can see.
However, you are not helping at all by not explaining what the logic is on how the boxes are populated.
There is no logic that I know of between Orange and Toyota.

 

[anthonymedz]

You have received code from 3 different people that all work as far as we can see.
However, you are not helping at all by not explaining what the logic is on how the boxes are populated.
There is no logic that I know of between Orange and Toyota.

Hi guys,

Thanks for the help very much appreciated. I just used orange and toyota as an example to be able to understand but it didn't. I am very sorry for my few knowledge in excel and not explaining to you briefly.
However, the purposed of this is that i am making an inventory for our facilities wherein now i figure out using this script:

Private Sub UserForm_Initialize()


Dim cntr As Integer
Sheets("Categories").Select
cntr = Application.WorksheetFunction.CountA(Range("A:A"))


For i = 1 To cntr
Me.ComboBox1.AddItem Cells(i, 1)
Me.ComboBox2.AddItem Cells(i, 2)
Me.ComboBox3.AddItem Cells(i, 3)
Me.ComboBox4.AddItem Cells(i, 4)


Next i


End Sub




Private Sub CommandButton5_Click()
'Electrical
If ComboBox1 = "G9-Segulla" Then Sheets("G9-Segulla").Select


'Mechanical
If ComboBox2 = "Pipe" Then Sheets("Pipe").Select


'Civil
If ComboBox3 = "Sheet2" Then Sheets("Sheet2").Select
If ComboBox3 = "Plywood" Then Sheets("Plywood").Select


'Plumbing


End Sub




Private Sub CommandButton2_Click()


Dim ws As Worksheet
Dim irow As Long


Set ws = Sheets("Sheet2")
Set ws = Sheets("G9-Segulla")
Set ws = Sheets("Pipe")
Set ws = Sheets("Plywood")


irow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Offset(1, 0).Row




ws.Cells(irow, 1) = Me.TextBox1 'Date
ws.Cells(irow, 2) = Me.TextBox2 'name
ws.Cells(irow, 4) = Me.TextBox3 'Actual out


MsgBox "success", vbInformation, "Add"
TextBox1 = ""
TextBox2 = ""
TextBox3 = ""




End Sub

I make Dim ws to assign on different sheets in commandbutton2 wherein when i switch to other sheets the value of the textbox will remain consantly in Sheet2 only. I want to make happen when i select to a different sheet the value of my textbox will go to that sheets on the sheets that i selected. Thanks for the advance help.

 

[Fluff]

As you are selecting the sheet with the other button, try

Private Sub CommandButton2_Click()
   Dim irow As Long
   
   irow = Cells(Rows.count, 1).End(xlUp).Offset(1, 0).Row
   Cells(irow, 1) = Me.TextBox1 'Date
   Cells(irow, 2) = Me.TextBox2 'name
   Cells(irow, 4) = Me.TextBox3 'Actual out
   
   MsgBox "success", vbInformation, "Add"
   TextBox1 = ""
   TextBox2 = ""
   TextBox3 = ""
End Sub

This will write the data to the active sheet.

 

[anthonymedz]

As you are selecting the sheet with the other button, try

Private Sub CommandButton2_Click()
   Dim irow As Long
   
   irow = Cells(Rows.count, 1).End(xlUp).Offset(1, 0).Row
   Cells(irow, 1) = Me.TextBox1 'Date
   Cells(irow, 2) = Me.TextBox2 'name
   Cells(irow, 4) = Me.TextBox3 'Actual out
   
   MsgBox "success", vbInformation, "Add"
   TextBox1 = ""
   TextBox2 = ""
   TextBox3 = ""
End Sub

This will write the data to the active sheet.

Hi,

Thank you so much for the big help. Script now works perfectly. Thank you and thanks to those guys who help me on this. cheers!