Automatic insertion of an Hyperlink by VBA

[Richard in Redditch]

Hello
I have a small project, which is giving me a large problem, and I have battled with it all afternoon (sigh!). I would be so grateful for some help, please!
The project is to keep track of Clients, and to maintain a summary of amounts invoiced, the type of work done, and so on. The summary sheet has a list of Client names in its A column, and each Client has his/her individual worksheet. I have coded a macro which sets up the client details, both in the summary sheet and in the new Client's worksheet. Not a particularly difficult project so far, but I want to include a hyperlink from the Client name in the summary sheet to the individual's personal worksheet which is given the Client's name. The code I have written is as follows:
' Insert hyperlink to new worksheet, insert new blank row

Sheets("Summary").Activate
Cells(NewSumryRow, 1).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
Sheets(ClientName).ClientName & "!A1", TextToDisplay:=ClientName
Rows("newsumryrow:newsumryrow").Select
Selection.Insert Shift:=xlDown
Unload UserForm1

MsgBox "Client '" & ClientName & "' now set up"

When I run the macro it falls over at the bold section above with "runtime error 438 - Object doesn't support this property or method". I have tried putting single and double quote marks around ClientName (which is a string variable), and using one and two words for the client's name, but nothing seems to affect matters. When I insert the HL manually, there is no trouble at all - but I would like to have it automated!

 

[Perpa]

Richard,

I found a hint on an old post from this forum ..."When you have spaces in the sheet names you must include the single quotes."
Try changing the line of code to:

[B] ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= [COLOR=#ff0000]'[/COLOR] & ClientName & "[COLOR=#ff0000]'[/COLOR]!A1", TextToDisplay:=ClientName[/B]

Perpa

 

[My Aswer Is This]

Assuming you have a sheet name Summary.
And in column A starting in row 2 you have sheet names.

This script will enter a link in those cells in column A and when you click on this link you will be taken to that sheet.

Sub My_Link()
'Modified 4-19-18 12:30 AM EDT
With Sheets("Summary")
    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

 

[Richard in Redditch]

Hello Perpa and My Aswer is this.

Thank you both for your fast response to my query.
Things now work as they should. The code snippet I used is as follows:

                                  ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", _
                                        SubAddress:="'" & ClientName & "'!A1", TextToDisplay:=ClientName

It is wonderful to have access to a forum such as this, as my textbooks on Excel 2013 do not make (as far as I can see) any reference to such small, but vital, nuances of syntax. Is there any encyclopaedia of the detailed syntax of VBA?

Thanks again from a happy bunny.

 

[My Aswer Is This]

I assume some how you came up with your own answer to your question.
I would be interested to see your complete code.
I have no ideal what client name is.

Glad you have things working for you.

 

[Richard in Redditch]

Hello My Aswer

Thanks for your post.
The key to getting things to run correctly was that my version of Excel 2013 VBA requires the first single quote to be inside a pair of double quotes. After noticing that, a bit of experimentation got the exact syntax correct.
The complete code just sets up a new worksheet, adds details from a userform to the next available row in the summary worksheet, and (now) has a hyperlink from the summary sheet to the corresponding individual's worksheet.
"ClientName" is a string variable which contains the name off the client - for example "Joe Soap"

Thanks again