VBA creating hyperlink to sheet

[tropis]

Hello,
That is my first post here and also first contact with VBA.
I have created one basic code, very simple, but, it works

The name of new client is to be inserted in worksheet 1, "Baza", cell A4.
Then, this macro creates new Sheet, the name of this sheet is the same as a name of new client typed in sheet 1, "Baza", cell A4.
Then, new row is added in sheet 1, "Baza", (A9) and name of new client from "Baza" A4 is inserted.

And I am stucked here. I would like this macro to create a hyperlink on freshly inserted client name in row 9, sheet 1, "Baza" - when click on new name, it should take me to the related sheet.

This is what I got so far.


Sub Dodawanie()


'stop if emtpy
If Range("A4").Value = "" Then
Exit Sub
End If


'insert new row
Range("A9").EntireRow.Insert
Range("A4").Copy Range("A9")


'copy oswiadczenie
Sheets("Oświadczenie wzór").Select
Sheets("Oświadczenie wzór").Copy After:=Sheets(2)


'change of sheet name
Sheets(3).Name = Sheets(1).Range("A4")


'clear A4
Sheets(1).Range("A4").ClearContents


'activation of main sheet
Worksheets("Baza").Activate


End Sub


Could you pls give some advice how to create hyperlink on A9 in sheet 1 to take me to the sheet with the same name ??
I will really appreciate your help.

Cheers,
Peter

 

[fhqwgads]

to make a hyperlink in a cell use the formula =HYPERLINK(location,name) where "name" is what clickable text in the cell will appear as. to link to the sheet named "Baza" you could use =HYPERLINK("#Baza!A1,"Baza")

in your code i think it might look like this

replace

'insert new row
Range("A9").EntireRow.Insert
Range("A4").Copy Range("A9")

with

'insert new row
Range("A9").EntireRow.Insert
Range("A9").Value = "=HYPERLINK(" & Chr(34) & "#" & Range("A4").Value & "!A1" & Chr(34) & "," & Chr(34) & Range("A4").Value & Chr(34) & ")"

hope that worked

 

[fhqwgads]

=HYPERLINK("#Baza!A1,"Baza")

should be =HYPERLINK("#Baza!A1","Baza")

 

[Rick Rothstein]

You may find this alternative to your maintaining a hyperlink list of your added sheets to be of interest. The code in my mini-blog article at this link ...

http://www.excelfox.com/forum/showthread.php/1830-A-Neat-quot-Go-To-Sheet-quot-Selector

will automatically adjust itself to any worksheets you add to the workbook plus it automatically adjusts the worksheet names it gives you to pick from as you type the letters of the name you are looking for.

 

[tropis]

'insert new row
Range("A9").EntireRow.Insert
Range("A9").Value = "=HYPERLINK(" & Chr(34) & "#" & Range("A4").Value & "!A1" & Chr(34) & "," & Chr(34) & Range("A4").Value & Chr(34) & ")"

hope that worked

Yes, it works. Thank you. Unfortunately, it works only when name in A4 contains on single word. When there are two words, like name and surname, it reports and error in hyperlink.

 

[tropis]

I have already figured out that location in the hyperlink should be between ' ' if the name of the sheet contains more than one word.
Any sugestions how to do it ??

 

[tropis]

Ok, I have already found a solution. Now it looks like this:

Range("A9").Value = "=HYPERLINK(" & Chr(34) & "#" & Chr(39) & Range("A4").Value & Chr(39) & "!A1" & Chr(34) & "," & Chr(34) & Range("A4").Value & Chr(34) & ")"

Thank you all for help

 

[Rick Rothstein]

Ok, I have already found a solution.

Just wondering if you looked at the mini-blog article link I posted in Message #4 ?

 

[tropis]

Just wondering if you looked at the mini-blog article link I posted in Message #4 ?

Yes, I did. Unfortunately it looked too complicated for me
I am not trying to learn VBA, just need it for this one case.
Thanks

 

[Rick Rothstein]

Yes, I did. Unfortunately it looked too complicated for me
I am not trying to learn VBA, just need it for this one case.

Did you download the example workbook that I made available at bottom of the article just to see how it worked?