Userforms to select worksheets

[Waterpolo1]

I have the following worksheets:
6-8mm
8-10mm
15-20mm
25-35mm
40-50mm

I have a list box in my userform containing these numbers. I need to activate the sheet once it is selected. I have this. but it doesn't work.

Private Sub Submitbutton_Click()


If ListBox1.Value = "6 - 8mm" Then
Worksheets("Trigger Points 6 - 8mm Plate").Activate


ElseIf ListBox1.Value = "10 - 12mm" Then
Worksheets("Trigger Points 10 - 12mm Plate").Activate


ElseIf ListBox1.Value = "15 - 20mm" Then
Worksheets("Trigger Points 15 - 20mm Plate").Activate


ElseIf ListBox1.Value = "25 - 35mm" Then
Worksheets("Trigger Points 25 - 35mm Plate").Activate


ElseIf ListBox1.Value = "40 - 50mm" Then
Worksheets("Trigger Points 40 - 50mm Plate").Activate


End If


End Sub

Any help would be great,

Thanks

 

[My Aswer Is This]

Try this:
Assuming your listbox is named "listBox1"

Sheets(Listbox1.Value).Activate

 

[Waterpolo1]

Thank you but it still doesn't seem to work. You are correct, it is names "listbox1".

 

[Trebor76]

Why don't you just right click on the navigation bar at the bottom left hand corner of the workbook which will display each of the visible sheets - the active sheet will have a tick next to it and you can simply click any other tab you want.

 

[Waterpolo1]

Thanks 'Trebor'. The reason is that once the sheet is activated, there is a combo box that then inputs the selected value into a cell on that sheet. So I think (correct me if I am wrong), the sheet needs to be selected first, then the value inputed correctly into that sheet.

 

[Trebor76]

No, you don't have to select a sheet to populate it. The following will populate cell A1 of the selected sheet from ListBox1 with the value in ComboBox1:

Sheets(ListBox1.Value).Range("A1").Value = Me.ComboBox1.Value

Note the selections in ListBox1 must be exactly the same (unless you have code to convert the selection to a certain worksheet) as the sheet names as you're trying to populate which I think is your issue.

HTH

Robert

 

[My Aswer Is This]

I tested this just now and my script line does work. The name you choose in the listbox must not be a proper sheet name.
What does this mean "Worksheets("Trigger Points 40 - 50mm Plate").Activate
That's a big sheet name

Thank you but it still doesn't seem to work. You are correct, it is names "listbox1".

 

[My Aswer Is This]

If your sheet name is 6-8mm
Why do you have:
Worksheets("Trigger Points 6 - 8mm Plate").Activate
If you were going to use this approach it should say
Worksheets("6-8mm").Activate

 

[My Aswer Is This]

To ensure all the sheet names in your UserForm list box are correct I suggest putting this script in your UserForm.

Private Sub UserForm_Initialize()
Dim i As Integer
For i = 1 To Sheets.Count
ListBox1.AddItem Sheets(i).Name
Next
End Sub

 

[Waterpolo1]

Thank you all so much for the help it is much appreciated!To clear a few things up the sheets names were actually "trigger points 6-8mm plate" etc..

The user form has 3 questions. 1.) thickness of plate 2.) Hours completed 3.) Total hours completed.

So i need the code to activate the correct sheet (depending on what thickness has been selected) and input the figures in questions 2 and 3 into the correct cells on that relevant sheet.