vba sheet selector via combo box (forms toolbar)

Topic

New Reply

Hi,

I have a combo box that is designed as a sheet navigator. The Cell link is . I have set up the following code that uses the cell link “voice” to select the relevant sheet. This works but I am wondering if there is a more elegant approach than using multiple IF statements?

“voice” is a numeric. Is it possible to convert that from a numeric to a string that represents the sheet name in order to convert to a worksheet for selection, rather than using multiple IF’s ??

I hope this is clear.

[codebox]
Sub FullView()

Dim tm1 As Worksheet, tm2 As Worksheet, _
lge As Worksheet, lgb As Worksheet, lgg As Worksheet, lgf As Worksheet, _
pla As Worksheet, plg As Worksheet, pld As Worksheet, plm As Worksheet, pls As Worksheet, _
adm As Worksheet
Dim voice As String

Set adm = Worksheets(“Admin”)
Set tm1 = Worksheets(“Teams (1)”)
Set tm2 = Worksheets(“Teams (2)”)
Set lge = Worksheets(“Entire League”)
Set lgb = Worksheets(“Boys”)
Set lgg = Worksheets(“Girls”)
Set lgf = Worksheets(“Finance”)
Set pla = Worksheets(“Players”)
Set plg = Worksheets(“GK”)
Set pld = Worksheets(“DEF”)
Set plm = Worksheets(“MID”)
Set pls = Worksheets(“STR”)

voice = adm.Range(“D21”)

If voice = 1 Then
Exit Sub
End If

If voice = 2 Then
tm1.Select
End If

If voice = 3 Then
tm2.Select
End If

If voice = 4 Then
lge.Select
End If

If voice = 5 Then
lgb.Select
End If

If voice = 6 Then
lgg.Select
End If

If voice = 7 Then
lgf.Select
End If

If voice = 8 Then
pla.Select
End If

If voice = 9 Then
plg.Select
End If

If voice = 10 Then
pld.Select
End If

If voice = 11 Then
plm.Select
End If

If voice = 12 Then
pls.Select
End If

adm.Range(“D21”) = 1

End Sub
[/codebox]

Reply | Quote

Replies

Select Case might be easier than the multiple IF statements.

Reply | Quote

Hi Nathan

I may have over simplified this but here is the code:

[codebox]Sub FullView()

Dim ws

ws = Cells(3, 8).Value

Worksheets(ws).Select

End Sub[/codebox]

I created a look up list in range J2:K4 which you can extend to have all your worksheets in.
I then created a forms combo and linked it to Cell G3 to show the numerical value, in cell H3 I did a simple VLOOKUP and wrote this formula =VLOOKUP($G$3,$J$2:$K$4,2)

This value is now the look up variable for my code in the VBA (Cells (3,8)) and assigned the macro to the control, I have added the file for your delectation

Reply | Quote

Nice! Much better than Select Case.

Reply | Quote

I had considered Select Case, but it still seemed a little long winded. I had previously attempted to use hyperlinks without success.

Jerry’s solution is simple and works brilliantly. Thanks very much!

Jerry: “Dim ws” – left like this means it’s a variant. Right??

Reply | Quote

I had considered Select Case, but it still seemed a little long winded. I had previously attempted to use hyperlinks without success.

Jerry’s solution is simple and works brilliantly. Thanks very much!

Jerry: “Dim ws” – left like this means it’s a variant. Right??

Correct, but was an omission by me, you can change it to As String to make it correct

Reply | Quote

Thanks Jerry.

Reply | Quote