Image Hyperlink Subaddress - Variable Cell Reference (VBA)

[JHusk]

Ok, all, first time posting on any forum. Just been sitting in the back, reading and learning. But now I'm stuck and decided to ask for help.

I need to assign a created image with a hyperlink to a hidden sheet, using a variable address based on the value of a cell. EV36 would be the location of the text that matches the name of the hidden sheet.

Sub HyperlinkIcon()
  
    ActiveSheet.Shapes.Range(Array("ViewInvoiceIcon")).Select
    Selection.Copy
   
    Range("AC" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial
    Selection.ShapeRange.IncrementLeft 2.25
    Selection.ShapeRange.IncrementTop -0.75
    Selection.ShapeRange.Name = Range("FL36")
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        "'" & ("EV36") & "'!A1"
        
      
    Range("AE37").Select
     Selection.Copy
    Range("AC" & Rows.Count).End(xlUp).Offset(1).Select
     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
End Sub

 

[swapnilk]

Try this:

Sub HyperlinkIcon()
    Dim sheetName as String

    sheetName = Range("EV36").Value

    ActiveSheet.Shapes.Range(Array("ViewInvoiceIcon")).Select
    Selection.Copy
  
    Range("AC" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial
    Selection.ShapeRange.IncrementLeft 2.25
    Selection.ShapeRange.IncrementTop -0.75
    Selection.ShapeRange.Name = Range("FL36")
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        "'" & sheetName & "'!A1"
        
    Range("AE37").Select
    Selection.Copy
    Range("AC" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

 

[JHusk]

Try this:

Sub HyperlinkIcon()
    Dim sheetName as String

    sheetName = Range("EV36").Value

    ActiveSheet.Shapes.Range(Array("ViewInvoiceIcon")).Select
    Selection.Copy
 
    Range("AC" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial
    Selection.ShapeRange.IncrementLeft 2.25
    Selection.ShapeRange.IncrementTop -0.75
    Selection.ShapeRange.Name = Range("FL36")
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        "'" & sheetName & "'!A1"
       
    Range("AE37").Select
    Selection.Copy
    Range("AC" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

Getting a "Run-time Error '5': Invalid procedure call or argument".

It shouldn't matter that "EV36" is a the result of an Index () formula, should it?

 

[JHusk]

@swapnilk What am I missing here? I've been at this for 3-4 days now, going through every combination.

Changed sheetName to shtName, when I get the run-time error, I hover over the .value and it gives the proper cell value.

Changed ActiveSheet.Hyperlinks.Add to Sheet28.Hyperlinks.Add

I've concatenated the text on Sheet28 in hidden columns to make every possible combination of ways to replicate the name of the new sheets being created. I've permanently made visible the newly created sheets. Still, nothing works and I get a run-time error at the same exact spot. Literally 100% of every other ounce of code does exactly what I've asked it to do.

ActiveSheets.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"'" & shtName & "'!A1"

or (the alternate version)

Sheet28.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'" & shtName & "'!A1"

 

[JHusk]

I figured it out!!!

The "Anchor" was only set to Selection but needed to be Selectioin.Ranage.item(1)

Dim shtName As String
shtName = Range("EV36").Value
        
ActiveSheet.Shapes.Range(Array("ViewInvoiceIcon")).Select
    Selection.Copy
  
Range("AC" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial
    Selection.ShapeRange.IncrementLeft 2.25
    Selection.ShapeRange.IncrementTop -0.75
    Selection.ShapeRange.Name = Range("FL36")
       
With ActiveSheet
    .Hyperlinks.Add Anchor:=Selection.ShapeRange.Item(1), Address:="", SubAddress:= _
        "'" & shtName & "'!A1"
End With
Range("AE37").Select
    Selection.Copy
Range("AC" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

 

[JHusk]

So......Just an update on this for anyone who looks in the future. The solution worked as far as actually creating and associating the hyperlink properly. However, when clicking the icon, the hidden sheet still wouldn't unhide. It worked like a charm when I would Break everything and unhid the newly created sheet, but as this sheet needed to be hidden at most times, this wasn't an option.

End result was that I needed to created a series of "GoTo" Macros for as many possible new sheets I could imagine being created for this particular application. In this case, it was 25. Great part was that I already had to reference information in the hidden columns for the "sheetName". From there, it was only a matter of telling every where to look.

Not that it matters for this particular thread, but maybe I help save some poor sap the 6-7 days I spent looking for these answers, here is the code I ended up using, with some extras...

Dim sheetName As String
sheetName = Range("EV36").Value
        
ActiveSheet.Shapes.Range(Array("ViewInvoiceIcon")).Select
    Selection.Copy
  
Range("AC" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial
    Selection.ShapeRange.IncrementLeft 2.25
    Selection.ShapeRange.IncrementTop -0.75
    Selection.ShapeRange.Name = Range("FL36")
    Selection.OnAction = Range("FY36")
Range("AE37").Select
    Selection.Copy
Range("AC" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
ActiveSheet.Shapes.Range(Array("PrintInvoiceIcon")).Select
    Selection.Copy
    
Range("EA" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial
    Selection.ShapeRange.IncrementLeft 3.75
    Selection.ShapeRange.IncrementTop 0.75
    Selection.ShapeRange.Name = Range("GL36")
    Selection.OnAction = Range("GY36")
Range("AE37").Select
    Selection.Copy
Range("EA" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

The "ViewInvoice" module:

Sub GotoViewInvoiceIcon1()

Dim shtName As String
shtName = Range("Accounting!EV37").Value

Sheets(shtName).Visible = True
Sheets(shtName).Activate

End Sub

The "PrintInvoice" module:

Sub GotoPrintInvoiceIcon1()

With Application
            .EnableEvents = False
            .ScreenUpdating = False
        End With

Dim shtName As String
shtName = Range("Accounting!EV37")

Sheets("Data").Visible = True
    Sheets("Data").Select
    ActiveSheet.Shapes.Range(Array("Logo")).Select
    Selection.Copy
    Sheets(shtName).Visible = True
    Sheets(shtName).Select
    Range("D3:R6").Select
    ActiveSheet.Paste
    Selection.ShapeRange.Height = 65.52
    Selection.ShapeRange.Width = 133.2
    
    Range("A1:BI69").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    ActiveSheet.Shapes.Range(Array("Logo")).Select
    Selection.Delete
   
    Sheets("Accounting").Visible = True
    Sheets("Accounting").Select
With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub