link to sheet

  • Thread starter Thread starter rlo
  • Start date Start date
R

rlo

In Sheets("Students") a list was made with the names of alle students.

Every student has his own sheet

Next script makes a new sheet with students with the same skill

Works fine.

But... I don't want to have just the names, but the names must also becomes
hyperlinks to the students own sheet.



How can I change

Sheets("Preslijst").Cells(r2, 2) = Cells(r1, 1)

to a hyperlink.

Ronald

Arnhem

The Netherlands



Script:

Sub PreslijstMaken()

Dim r1 As Integer, r2 As Integer, last As Integer

Dim skill As String, vak1 As String, doc As String



'to startsheet with names of students

Sheets("Students").Select

Range("A1").Select



vak = Sheets("Students ").Range("x2")

If vak = "" Then Exit Sub

last = Range("A65536").End(xlUp).Row

r2 = 4

For r1 = 1 To last

If Application.WorksheetFunction.CountIf(Rows(r1),
vak) > 0 Then

Sheets("Preslijst").Cells(r2, 2) =
Cells(r1, 1)

r2 = r2 + 1

End If

Next r1

End Sub
 
I think I'd use the =hyperlink() worksheet function. Here's a version that puts
that function in the cell to the right. I stole a subroutine from Chip Pearson
that tests to see if there is a worksheet by that name, too.



Option Explicit
Sub testme()
Dim r1 As Long

With Worksheets("Preslijst")
For r1 = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row
With .Cells(r1, 1)
If WorksheetExists(.Value, ActiveWorkbook) Then
.Offset(0, 1).Formula _
= "=hyperlink(""#""&cell(""address"",'" _
& .Value & "'!$a$1),""" & .Value & """)"
Else
.Offset(0, 1).Value = "Oopsie!"
End If
End With
Next r1
End With

End Sub

Function WorksheetExists(SheetName As String, _
Optional WhichBook As Workbook) As Boolean
'from Chip Pearson
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) > 0)
End Function

You may want to look at the way David McRitchie does the same kind of thing in
his "table of contents" routine:

http://www.mvps.org/dmcritchie/excel/buildtoc.htm
 
Back
Top