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

Create new sheet and hyperlink to it
 

It is amazing how much I have discovered here by doing searches and by asking
questions. There have been times when I actually found the answer myself
just by putting the question in text form.

I am glad you found your solution. Thanks for letting me know how you did.
--
Best wishes,

Jim


"RealmSteel" wrote:


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