Results 1 to 7 of 7
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    I have a combo box that is designed as a sheet navigator. The Cell link is <Admin!$D$21>. 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]

  2. #2
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Post

    Select Case might be easier than the multiple IF statements.
    Chuck Reimer
    I'm from the Government and I'm here to help...

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    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
    Jerry

  4. #4
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Nice! Much better than Select Case.
    Chuck Reimer
    I'm from the Government and I'm here to help...

  5. #5
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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??

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='VegasNath' post='793592' date='16-Sep-2009 21:01']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??[/quote]

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

  7. #7

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •