Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Programmatically creating hyperlinks
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6
Default Programmatically creating hyperlinks
I have one worksheet that lists a bunch of titles. Then for each title in the
first worksheet, I have another worksheet that corresponds to one of those
titles on the first worksheet. I could have up to 250 worksheets, one for
each title in the first worksheet. Can someone provide me with a way to
programmatically make the titles on the first sheet become hyperlinks that
point to its corresponding sheet in the workbook. Plus, this is a dynamically
workbook. Each week, it will have different titles and corresponding
worksheets for each, so I need to be able to save this code for use each week
when I create this workbook.

I've tried working on it myself, but am getting stuck on how to get to the
correct sheet for each title. My attempt is below if anyone needs a good
laugh :)

Any help will be greatly appreciated.

Code:
Sub LinkSheets()
    Dim myRange As range
    Dim lastCell As Object
    Dim lastLoop As Boolean
    Dim sheetNumber As Integer
    
    'find the last cell so we know when to stop.
    With ActiveSheet
        Set lastCell = .Cells(.Rows.Count, "A").End(xlUp)
    End With
    
    'Select the row in the first worksheet that I want.
    range("A:A").Select
    
    For Each cell In Selection
        If lastLoop Then
            Exit For
        Else
            If cell.Value = lastCell Then
                lastLoop = True
            End If
            
            'Make sure I am not trying to link the worksheet header to 
another worksheet.
            'All good titles will have a dash.
            If InStr(1, cell.Value, "-")  0 Then
                Cells.Find(What:=cell.Value, After:=ActiveCell, 
LookIn:=xlValues, _
                    LookAt:=xlPart, SearchOrder:=xlByColumns, 
SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False).Activate
                
                Dim workSheetCount As Integer
                Dim x As Integer
                workSheetCount = ActiveWorkbook.Worksheets.Count
                
                'Get stuck here trying to go to the next sheet and search 
for my title
                'to see if this is the corresponding sheet I need for this 
title.
                For x = 1 To workSheetCount
                    ActiveWorkbook.Sheets(x).Select
                    Cells.FindNext(After:=ActiveCell).Activate
                Next
                
                'Once I do find the correct sheet, I hope this will create 
the hyperlink I want
                ActiveSheet.Hyperlinks.Add Anchor:=cell, _
                    Address:="", SubAddress:=ActiveCell.Address, 
TextToDisplay:=cell.Value
                    
                'Then I want to create a "back" hyperlink to take the user 
back to the
                'master page (first page).
            End If
        End If
    Next cell
End Sub
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,311
Default Programmatically creating hyperlinks
Assuming your titles are in one column (column A beginning at row 1) and
there are no empty rows between titles:

For Each cell In Range("A1:A" & Range("A65536").End(xlUp).Row)
cell.Hyperlinks.Add Anchor:=Range("A" & cell.Row), Address:="",
SubAddress:= _
"'" & Sheets(cell.Value).Name & "'!A1",
TextToDisplay:=Sheets(cell.Value).Name
Next cell

HTH,
Paul
--

"Wannabe" wrote in message
...
I have one worksheet that lists a bunch of titles. Then for each title in
the
first worksheet, I have another worksheet that corresponds to one of those
titles on the first worksheet. I could have up to 250 worksheets, one for
each title in the first worksheet. Can someone provide me with a way to
programmatically make the titles on the first sheet become hyperlinks that
point to its corresponding sheet in the workbook. Plus, this is a
dynamically
workbook. Each week, it will have different titles and corresponding
worksheets for each, so I need to be able to save this code for use each
week
when I create this workbook.

I've tried working on it myself, but am getting stuck on how to get to the
correct sheet for each title. My attempt is below if anyone needs a good
laugh :)

Any help will be greatly appreciated.

Code:
 Sub LinkSheets()
    Dim myRange As range
    Dim lastCell As Object
    Dim lastLoop As Boolean
    Dim sheetNumber As Integer

    'find the last cell so we know when to stop.
    With ActiveSheet
        Set lastCell = .Cells(.Rows.Count, "A").End(xlUp)
    End With

    'Select the row in the first worksheet that I want.
    range("A:A").Select

    For Each cell In Selection
        If lastLoop Then
            Exit For
        Else
            If cell.Value = lastCell Then
                lastLoop = True
            End If

            'Make sure I am not trying to link the worksheet header to
 another worksheet.
            'All good titles will have a dash.
            If InStr(1, cell.Value, "-")  0 Then
                Cells.Find(What:=cell.Value, After:=ActiveCell,
 LookIn:=xlValues, _
                    LookAt:=xlPart, SearchOrder:=xlByColumns,
 SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False).Activate

                Dim workSheetCount As Integer
                Dim x As Integer
                workSheetCount = ActiveWorkbook.Worksheets.Count

                'Get stuck here trying to go to the next sheet and search
 for my title
                'to see if this is the corresponding sheet I need for this
 title.
                For x = 1 To workSheetCount
                    ActiveWorkbook.Sheets(x).Select
                    Cells.FindNext(After:=ActiveCell).Activate
                Next

                'Once I do find the correct sheet, I hope this will create
 the hyperlink I want
                ActiveSheet.Hyperlinks.Add Anchor:=cell, _
                    Address:="", SubAddress:=ActiveCell.Address,
 TextToDisplay:=cell.Value

                'Then I want to create a "back" hyperlink to take the user
 back to the
                'master page (first page).
            End If
        End If
    Next cell
 End Sub



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6
Default Programmatically creating hyperlinks
Thank you for that piece, but I also need to be able to go to the page the
hyperlink is referencing and place a hyperlink back to the calling (first)
page. Thanks.

"PCLIVE" wrote:

Assuming your titles are in one column (column A beginning at row 1) and
there are no empty rows between titles:

For Each cell In Range("A1:A" & Range("A65536").End(xlUp).Row)
cell.Hyperlinks.Add Anchor:=Range("A" & cell.Row), Address:="",
SubAddress:= _
"'" & Sheets(cell.Value).Name & "'!A1",
TextToDisplay:=Sheets(cell.Value).Name
Next cell

HTH,
Paul
--

"Wannabe" wrote in message
...
I have one worksheet that lists a bunch of titles. Then for each title in
the
first worksheet, I have another worksheet that corresponds to one of those
titles on the first worksheet. I could have up to 250 worksheets, one for
each title in the first worksheet. Can someone provide me with a way to
programmatically make the titles on the first sheet become hyperlinks that
point to its corresponding sheet in the workbook. Plus, this is a
dynamically
workbook. Each week, it will have different titles and corresponding
worksheets for each, so I need to be able to save this code for use each
week
when I create this workbook.

I've tried working on it myself, but am getting stuck on how to get to the
correct sheet for each title. My attempt is below if anyone needs a good
laugh :)

Any help will be greatly appreciated.

Code:
  Sub LinkSheets()
     Dim myRange As range
     Dim lastCell As Object
     Dim lastLoop As Boolean
     Dim sheetNumber As Integer
 
     'find the last cell so we know when to stop.
     With ActiveSheet
         Set lastCell = .Cells(.Rows.Count, "A").End(xlUp)
     End With
 
     'Select the row in the first worksheet that I want.
     range("A:A").Select
 
     For Each cell In Selection
         If lastLoop Then
             Exit For
         Else
             If cell.Value = lastCell Then
                 lastLoop = True
             End If
 
             'Make sure I am not trying to link the worksheet header to
  another worksheet.
             'All good titles will have a dash.
             If InStr(1, cell.Value, "-")  0 Then
                 Cells.Find(What:=cell.Value, After:=ActiveCell,
  LookIn:=xlValues, _
                     LookAt:=xlPart, SearchOrder:=xlByColumns,
  SearchDirection:=xlNext, _
                     MatchCase:=False, SearchFormat:=False).Activate
 
                 Dim workSheetCount As Integer
                 Dim x As Integer
                 workSheetCount = ActiveWorkbook.Worksheets.Count
 
                 'Get stuck here trying to go to the next sheet and search
  for my title
                 'to see if this is the corresponding sheet I need for this
  title.
                 For x = 1 To workSheetCount
                     ActiveWorkbook.Sheets(x).Select
                     Cells.FindNext(After:=ActiveCell).Activate
                 Next
 
                 'Once I do find the correct sheet, I hope this will create
  the hyperlink I want
                 ActiveSheet.Hyperlinks.Add Anchor:=cell, _
                     Address:="", SubAddress:=ActiveCell.Address,
  TextToDisplay:=cell.Value
 
                 'Then I want to create a "back" hyperlink to take the user
  back to the
                 'master page (first page).
             End If
         End If
     Next cell
  End Sub




Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,311
Default Programmatically creating hyperlinks
For Each cell In Range("A1:A" & Range("A65536").End(xlUp).Row)
cell.Hyperlinks.Add Anchor:=Range("A" & cell.Row), Address:="",
SubAddress:= _
"'" & Sheets(cell.Value).Name & "'!A1",
TextToDisplay:=Sheets(cell.Value).Name

'This line will create a hyperlink in cell A1 that links back to the
original page. It uses the value that is in A1. If you want to use a
different cell, then modify as needed.
Sheets(cell.Value).Range("A1").Hyperlinks.Add
Anchor:=Sheets(cell.Value).Range("A1"), Address:="", SubAddress:= _
"'" & Sheets(1).Name & "'!A1",
TextToDisplay:=Sheets(cell.Value).Range("A1").Valu e
Next cell


HTH,
Paul
--

"Wannabe" wrote in message
...
Thank you for that piece, but I also need to be able to go to the page the
hyperlink is referencing and place a hyperlink back to the calling (first)
page. Thanks.

"PCLIVE" wrote:

Assuming your titles are in one column (column A beginning at row 1) and
there are no empty rows between titles:

For Each cell In Range("A1:A" & Range("A65536").End(xlUp).Row)
cell.Hyperlinks.Add Anchor:=Range("A" & cell.Row), Address:="",
SubAddress:= _
"'" & Sheets(cell.Value).Name & "'!A1",
TextToDisplay:=Sheets(cell.Value).Name
Next cell

HTH,
Paul
--

"Wannabe" wrote in message
...
I have one worksheet that lists a bunch of titles. Then for each title
in
the
first worksheet, I have another worksheet that corresponds to one of
those
titles on the first worksheet. I could have up to 250 worksheets, one
for
each title in the first worksheet. Can someone provide me with a way to
programmatically make the titles on the first sheet become hyperlinks
that
point to its corresponding sheet in the workbook. Plus, this is a
dynamically
workbook. Each week, it will have different titles and corresponding
worksheets for each, so I need to be able to save this code for use
each
week
when I create this workbook.

I've tried working on it myself, but am getting stuck on how to get to
the
correct sheet for each title. My attempt is below if anyone needs a
good
laugh :)

Any help will be greatly appreciated.

Code:
  Sub LinkSheets()
     Dim myRange As range
     Dim lastCell As Object
     Dim lastLoop As Boolean
     Dim sheetNumber As Integer
 
     'find the last cell so we know when to stop.
     With ActiveSheet
         Set lastCell = .Cells(.Rows.Count, "A").End(xlUp)
     End With
 
     'Select the row in the first worksheet that I want.
     range("A:A").Select
 
     For Each cell In Selection
         If lastLoop Then
             Exit For
         Else
             If cell.Value = lastCell Then
                 lastLoop = True
             End If
 
             'Make sure I am not trying to link the worksheet header to
  another worksheet.
             'All good titles will have a dash.
             If InStr(1, cell.Value, "-")  0 Then
                 Cells.Find(What:=cell.Value, After:=ActiveCell,
  LookIn:=xlValues, _
                     LookAt:=xlPart, SearchOrder:=xlByColumns,
  SearchDirection:=xlNext, _
                     MatchCase:=False, SearchFormat:=False).Activate
 
                 Dim workSheetCount As Integer
                 Dim x As Integer
                 workSheetCount = ActiveWorkbook.Worksheets.Count
 
                 'Get stuck here trying to go to the next sheet and 
  search
  for my title
                 'to see if this is the corresponding sheet I need for 
  this
  title.
                 For x = 1 To workSheetCount
                     ActiveWorkbook.Sheets(x).Select
                     Cells.FindNext(After:=ActiveCell).Activate
                 Next
 
                 'Once I do find the correct sheet, I hope this will 
  create
  the hyperlink I want
                 ActiveSheet.Hyperlinks.Add Anchor:=cell, _
                     Address:="", SubAddress:=ActiveCell.Address,
  TextToDisplay:=cell.Value
 
                 'Then I want to create a "back" hyperlink to take the 
  user
  back to the
                 'master page (first page).
             End If
         End If
     Next cell
  End Sub






Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6
Default Programmatically creating hyperlinks
That is pretty close to what I want. Thank you very much. I believe I can
take it from here.

"PCLIVE" wrote:

For Each cell In Range("A1:A" & Range("A65536").End(xlUp).Row)
cell.Hyperlinks.Add Anchor:=Range("A" & cell.Row), Address:="",
SubAddress:= _
"'" & Sheets(cell.Value).Name & "'!A1",
TextToDisplay:=Sheets(cell.Value).Name

'This line will create a hyperlink in cell A1 that links back to the
original page. It uses the value that is in A1. If you want to use a
different cell, then modify as needed.
Sheets(cell.Value).Range("A1").Hyperlinks.Add
Anchor:=Sheets(cell.Value).Range("A1"), Address:="", SubAddress:= _
"'" & Sheets(1).Name & "'!A1",
TextToDisplay:=Sheets(cell.Value).Range("A1").Valu e
Next cell


HTH,
Paul
--

"Wannabe" wrote in message
...
Thank you for that piece, but I also need to be able to go to the page the
hyperlink is referencing and place a hyperlink back to the calling (first)
page. Thanks.

"PCLIVE" wrote:

Assuming your titles are in one column (column A beginning at row 1) and
there are no empty rows between titles:

For Each cell In Range("A1:A" & Range("A65536").End(xlUp).Row)
cell.Hyperlinks.Add Anchor:=Range("A" & cell.Row), Address:="",
SubAddress:= _
"'" & Sheets(cell.Value).Name & "'!A1",
TextToDisplay:=Sheets(cell.Value).Name
Next cell

HTH,
Paul
--

"Wannabe" wrote in message
...
I have one worksheet that lists a bunch of titles. Then for each title
in
the
first worksheet, I have another worksheet that corresponds to one of
those
titles on the first worksheet. I could have up to 250 worksheets, one
for
each title in the first worksheet. Can someone provide me with a way to
programmatically make the titles on the first sheet become hyperlinks
that
point to its corresponding sheet in the workbook. Plus, this is a
dynamically
workbook. Each week, it will have different titles and corresponding
worksheets for each, so I need to be able to save this code for use
each
week
when I create this workbook.

I've tried working on it myself, but am getting stuck on how to get to
the
correct sheet for each title. My attempt is below if anyone needs a
good
laugh :)

Any help will be greatly appreciated.

Code:
   Sub LinkSheets()
      Dim myRange As range
      Dim lastCell As Object
      Dim lastLoop As Boolean
      Dim sheetNumber As Integer
  
      'find the last cell so we know when to stop.
      With ActiveSheet
          Set lastCell = .Cells(.Rows.Count, "A").End(xlUp)
      End With
  
      'Select the row in the first worksheet that I want.
      range("A:A").Select
  
      For Each cell In Selection
          If lastLoop Then
              Exit For
          Else
              If cell.Value = lastCell Then
                  lastLoop = True
              End If
  
              'Make sure I am not trying to link the worksheet header to
   another worksheet.
              'All good titles will have a dash.
              If InStr(1, cell.Value, "-")  0 Then
                  Cells.Find(What:=cell.Value, After:=ActiveCell,
   LookIn:=xlValues, _
                      LookAt:=xlPart, SearchOrder:=xlByColumns,
   SearchDirection:=xlNext, _
                      MatchCase:=False, SearchFormat:=False).Activate
  
                  Dim workSheetCount As Integer
                  Dim x As Integer
                  workSheetCount = ActiveWorkbook.Worksheets.Count
  
                  'Get stuck here trying to go to the next sheet and 
   search
   for my title
                  'to see if this is the corresponding sheet I need for 
   this
   title.
                  For x = 1 To workSheetCount
                      ActiveWorkbook.Sheets(x).Select
                      Cells.FindNext(After:=ActiveCell).Activate
                  Next
  
                  'Once I do find the correct sheet, I hope this will 
   create
   the hyperlink I want
                  ActiveSheet.Hyperlinks.Add Anchor:=cell, _
                      Address:="", SubAddress:=ActiveCell.Address,
   TextToDisplay:=cell.Value
  
                  'Then I want to create a "back" hyperlink to take the 
   user
   back to the
                  'master page (first page).
              End If
          End If
      Next cell
   End Sub






Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
help creating new hyperlinks programmatically J Links and Linking in Excel 1 January 14th 08 08:15 AM
Programmatically inserting hyperlinks FrigidDigit[_2_] Excel Programming 8 October 19th 05 03:50 PM
Programmatically creating a map from data in Excel David Brockus Excel Programming 0 April 8th 05 09:57 PM
Programmatically Creating Worksheet Event The Vision Thing Excel Programming 0 October 15th 04 03:52 PM
Creating Sub CommandButton1_Clk programmatically Jag Man Excel Programming 7 December 14th 03 08:49 PM


All times are GMT +1. The time now is 12:45 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement