Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 37
Default Dynamic Hyperlinks

Hi Guys, this is the first question i have posted and as my name suggests i'm
a noob

i have this problem, i am trying to create a workbook that logs the mistakes
that could be made on a given day at work by any given employee.

i have 2 worksheets the first is a summary page that holds the names of the
employees and the second is a template that the sheets for each employee are
derived from.

i have written a little VBA sub that will look at the list and create a new
worksheet (copy and then rename the template) for each employee on the list,
the problem is, is that i want the employee names on the summary page to
become hyperlinks to their corrisponding worksheet, this is the code i have
written so far;

Sub HyperLink()

Dim WkSh As Worksheet
Dim sze As Long
Dim i As Long

i = 4
sze = Sheets("Summary").Cells(Rows.Count, 3).End(xlDown).Row

For Each WkSh In Worksheets
While sze i
If WkSh.Name = Sheets("Summary").Cells(i, 3).Value Then
Sheets("Summary").Cells(i, 3).Select
WkSh = WkSh.Name
Sheets("Summary").Cells(i, 3).Hyperlinks.Add Anchor:=Selection,
Address:="", SubAddress:= _
WkSh, TextToDisplay:=Sheets("Summary").Cells(i, 3).Value
End If
i = i + 1
Wend
i = 4
Next WkSh

End Sub

Any assistance that anyone could provide would be very much apprieciated,
thanks

The Noob

  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,480
Default Dynamic Hyperlinks

Hi

I would use something like the following, to read through each name in
column A, starting at A4, and adding the hyperlinks

Sub InsertHyperlinks()
Dim i As Long, lr As Long
lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
For i = 4 To lr
Cells(i, 1).Hyperlinks.Add Anchor:=Cells(i, 1), Address:="",
SubAddress:= _
Cells(i, 1).Text & "!A1", TextToDisplay:=Cells(i, 1).Text
Next i
End Sub

Add your own additional tests if required

--
Regards
Roger Govier

"Noob McKnownowt" wrote in
message ...
Hi Guys, this is the first question i have posted and as my name suggests
i'm
a noob

i have this problem, i am trying to create a workbook that logs the
mistakes
that could be made on a given day at work by any given employee.

i have 2 worksheets the first is a summary page that holds the names of
the
employees and the second is a template that the sheets for each employee
are
derived from.

i have written a little VBA sub that will look at the list and create a
new
worksheet (copy and then rename the template) for each employee on the
list,
the problem is, is that i want the employee names on the summary page to
become hyperlinks to their corrisponding worksheet, this is the code i
have
written so far;

Sub HyperLink()

Dim WkSh As Worksheet
Dim sze As Long
Dim i As Long

i = 4
sze = Sheets("Summary").Cells(Rows.Count, 3).End(xlDown).Row

For Each WkSh In Worksheets
While sze i
If WkSh.Name = Sheets("Summary").Cells(i, 3).Value Then
Sheets("Summary").Cells(i, 3).Select
WkSh = WkSh.Name
Sheets("Summary").Cells(i, 3).Hyperlinks.Add Anchor:=Selection,
Address:="", SubAddress:= _
WkSh, TextToDisplay:=Sheets("Summary").Cells(i, 3).Value
End If
i = i + 1
Wend
i = 4
Next WkSh

End Sub

Any assistance that anyone could provide would be very much apprieciated,
thanks

The Noob

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 37
Default Dynamic Hyperlinks

Thanks alot matey, will test later, but thanks for the suggestion i was
getting annoyed.

Noob

"Roger Govier" wrote:

Hi

I would use something like the following, to read through each name in
column A, starting at A4, and adding the hyperlinks

Sub InsertHyperlinks()
Dim i As Long, lr As Long
lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
For i = 4 To lr
Cells(i, 1).Hyperlinks.Add Anchor:=Cells(i, 1), Address:="",
SubAddress:= _
Cells(i, 1).Text & "!A1", TextToDisplay:=Cells(i, 1).Text
Next i
End Sub

Add your own additional tests if required

--
Regards
Roger Govier

"Noob McKnownowt" wrote in
message ...
Hi Guys, this is the first question i have posted and as my name suggests
i'm
a noob

i have this problem, i am trying to create a workbook that logs the
mistakes
that could be made on a given day at work by any given employee.

i have 2 worksheets the first is a summary page that holds the names of
the
employees and the second is a template that the sheets for each employee
are
derived from.

i have written a little VBA sub that will look at the list and create a
new
worksheet (copy and then rename the template) for each employee on the
list,
the problem is, is that i want the employee names on the summary page to
become hyperlinks to their corrisponding worksheet, this is the code i
have
written so far;

Sub HyperLink()

Dim WkSh As Worksheet
Dim sze As Long
Dim i As Long

i = 4
sze = Sheets("Summary").Cells(Rows.Count, 3).End(xlDown).Row

For Each WkSh In Worksheets
While sze i
If WkSh.Name = Sheets("Summary").Cells(i, 3).Value Then
Sheets("Summary").Cells(i, 3).Select
WkSh = WkSh.Name
Sheets("Summary").Cells(i, 3).Hyperlinks.Add Anchor:=Selection,
Address:="", SubAddress:= _
WkSh, TextToDisplay:=Sheets("Summary").Cells(i, 3).Value
End If
i = i + 1
Wend
i = 4
Next WkSh

End Sub

Any assistance that anyone could provide would be very much apprieciated,
thanks

The Noob

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

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
Dynamic Hyperlinks PJFry Excel Worksheet Functions 2 December 18th 08 09:06 PM
Dynamic hyperlinks Ray Excel Discussion (Misc queries) 0 August 21st 07 06:03 PM
Dynamic hyperlinks not working Rohan Excel Programming 4 July 31st 06 03:06 PM
Dynamic hyperlinks problem Mr.Plankton Excel Worksheet Functions 0 June 21st 06 03:20 PM
Dynamic Hyperlinks (URL) problem in Excel Sujith Kumar Excel Programming 4 October 26th 04 05:25 PM


All times are GMT +1. The time now is 02:48 PM.

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

About Us

"It's about Microsoft Excel"