VBA - change SELECT CASE code associated with a CommandButton to a Hyperlink

[manc]

Good morning,

I have this Select Case code:

Sub Button_Goto_Correct_Consignee()Dim BR As Long


    BR = Sheets("H").Range("AL" & Sheets("H").Shapes(Application.Caller).TopLeftCell.Row - 5).Value


    Select Case BR


        Case Is = 1: Application.GoTo (ActiveWorkbook.Sheets("MDZ_AD").Range("A1"))
        Case Is = 2: Application.GoTo (ActiveWorkbook.Sheets("MDZ_IFG").Range("A1"))
        Case Is = 3: Application.GoTo (ActiveWorkbook.Sheets("MDZ_KFF").Range("A1"))
        Case Is = 4: Application.GoTo (ActiveWorkbook.Sheets("MDZ_OSBJ").Range("A1"))
        Case Is = 5: Application.GoTo (ActiveWorkbook.Sheets("MDZ_OSBD").Range("A1"))
        Case Is = 6: Application.GoTo (ActiveWorkbook.Sheets("MDZ_SMD").Range("A1"))
        Case Is = 7: Application.GoTo (ActiveWorkbook.Sheets("MDZ_SMJ").Range("A1"))
        Case Is = 8: Application.GoTo (ActiveWorkbook.Sheets("MDZ_SAT").Range("A1"))
        Case Is = 9: Application.GoTo (ActiveWorkbook.Sheets("MDZ_WSC").Range("A1"))
   
    End Select


End Sub

Instead of having it associated Shapes(Application.Caller), i am looking for help in adapting it to a hyperlink.

This bit of code is really important and would still apply:

BR = Sheets("H").Range("AL" & Sheets("H").Shapes(Application.Caller).TopLeftCell.Row - 5).Value

with the hyperlink always being on TopLeftCell.Row - 5

Any help greatly appreciated
Best regards
manc

 

[My Aswer Is This]

Would you care to say in words what your ultimate goal is?
Why do we need a shape or a Hyperlink?

 

[manc]

The goal is so the user can click on text that says "add/edit" and it takes them to the correct customer page.
It worked with a shape, but not when using excel online.
I therefore figure that a hyperlink will work better.

Best regards
manc

 

[My Aswer Is This]

How about you have a sheet named "Master"

And in column A we enter all the sheet names.
Then we have a Hyperlink to all these sheet names in column A of sheet named Master

Then in Row(2) for example column A you would have "George" this is a sheet name.
When you click on George you would be taken to sheet named George

Would something like that work.

I could write you a script which would set up the Hyperlinks for you.

 

[My Aswer Is This]

If my ideal would work for you use this script.

I have assigned the script to a sheet named "Master"
If you want the sheet named something else modify the script where you see my note:

Sub AddHyperLinks()
'Modified 4-20-18 8:20 AM EST
Dim C As Range
Dim i As Long
Dim ans As String
ans = "Master" 'Modify this name if needed all sheet names will be put in Column "A" of this sheet
Sheets(ans).Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).Clear
    For i = 2 To Sheets.Count
    Sheets(ans).Cells(i, 1).Value = Sheets(i).Name
    Sheets(i).Cells(1, 1).Value = Sheets(ans).Name
    Sheets(i).Cells(1, 1).Hyperlinks.Add Anchor:=Sheets(i).Cells(1, 1), Address:="", SubAddress:="'" & Sheets(i).Cells(1, 1).Value & "'!A1"
    Next

With Sheets(ans)
    For Each C In .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
        .Hyperlinks.Add Anchor:=C, Address:="", SubAddress:="'" & C.Value & "'!A1"
    Next C
End With
End Sub

My script also enters a link in each sheet Range("A1") which if you click on will take you back to sheet named Master

 

[manc]

Thanks My Aswer Is This, but it doesn't work with the design of the sheet - it's a bit more complicated than that. I was hoping that someone could just modify the code and i could take it and amend it to fit in where it should sit.

I'll try and make this as easy as possible to follow.

Each row is a master row that we call "a job".
Rows 1 and 2 are headers, so our first job is on row 3.
Rows 4-39 are grouped to row 3, and when expanded, they show all the information relating to the master row 3.
So if row 3 is job#1, then job#2 is on row 40.
Row 41- 76 are then grouped to row 40.
And so on and so on.

So if we then go back to job#1 and row 3, AL3 is where my drop-down sits where the customer is chosen.
5 rows down from row 3, currently in column N is where it reads "add/edit".
It has a transparent shape over it, with the above VBA code associated to it.
The user then believes they are clicking on "add/edit" and it takes them to the correct customer product worksheet containing all product codes, weights, prices etc.

However, this shape and code relationship doesn't seem to work with Excel Online, so I want to delete the shape and instead have a hyperlink.
If it is easier, then a hyperlink associated with Column N would work, rather than cell N8 (if AL3), N43 (if AL40), etc..

Hope this makes sense and explains in more detail.

Hoping that someone can point me in the right direction.
Best regards
manc

 

[My Aswer Is This]

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.