Create new sheet and hyperlink to it
OK I got it!!!
I had to add some code to create another variable.
This is up top with other Dims:
Dim NewSht As String
This is right after where I create the new sheet and Rename it:
NewSht = "'" & strDocName & "'!A1"
ActiveSheet.Hyperlinks.Add Anchor:=Cells(NewTopRow, 3), _
Address:="", SubAddress:=NewSht, TextToDisplay:=strDocName
This shows how great these forums are.
I pulled this from a post of Dave Peterson from June, 2002.
I found it using a Google search of these forums.
"Jim Jackson" wrote:
Not tried it but removing the quotes from "strDocName!A2" might do the trick.
--
Best wishes,
Jim
"RealmSteel" wrote:
I am working on a log workbook.
I have it working to use a word document template, but I am trying something
a bit more compact by using only Excel.
When I click on the next empty row in column A, it automatically puts a
value in the cell for the next consecutive number, it then inserts the date
in column B.
After this, it adds a new sheet to the workbook and renames the sheet with a
text string plus the number from column A. It puts the name of this new sheet
in column C and creates a hyperlink to the sheet at some specific cell.
My problem is figuring out the code to get to the newly named sheet.
Here are snippets of the code.
Dim NewTopRow As Long
Dim strDocName As String
Dim strTxtInsert As String
Dim bStarted As Boolean
Dim oApp As Word.Application
Worksheets("Sheet1").Unprotect
NewTopRow = ActiveSheet.UsedRange.Rows.Count + 1
If Target.Address = "$A$" & CStr(NewTopRow) Then
Application.EnableEvents = False
Cells(NewTopRow, 1).Value = Cells(NewTopRow - 1, 1).Value + 1
Cells(NewTopRow, 2).Value = Date
strDocName = "E681-RFI-" & CStr(Cells(NewTopRow, 1).Value)
strTxtInsert = Format$(Date, "m/ d/ yyyy")
'Locks 1st 2 cells of new row
Cells(NewTopRow, 1).Locked = True
Cells(NewTopRow, 2).Locked = True
' End of lock cell routine
Sheets("Template").Select
Sheets("Template").Copy After:=Worksheets(Worksheets.Count)
Sheets("Template (2)").Select
Sheets("Template (2)").Name = strDocName
ActiveSheet.Hyperlinks.Add Anchor:=Cells(NewTopRow, 3), _
Address:="", SubAddress:="strDocName!A2", TextToDisplay:=strDocName
Application.EnableEvents = True
End If
Worksheets("Sheet1").Protect
End Sub
5 lines above is where I think the problem is.
Address:="",SubAddress:..........
It keeps naming the hyperlink strDocName instead of the name for the new
sheet.
I do not know to use a variable name for the subaddress.
Thanks in advance.
This group has been a tremendous help so far.
Rich
|