Hyperlink Code

[oberon.black]

I have a code that allows me to create a worksheet from a user form.
want to edit the code so that it will hide the new worksheet and add
hyperlink to the cell that the userform drops the worksheet name int
on the active sheet.

is this possible? If so what do I need to add to my code.


Code
-------------------

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim newSheetName As String
Set ws = Worksheets("cgs")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a part number
If Trim(Me.LstNm.Value) = "" Then
Me.LstNm.SetFocus
MsgBox "Please enter last name"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.LstNm.Value
ws.Cells(iRow, 2).Value = Me.FrstNm.Value
newSheetName = ws.Cells(iRow, 1) & "," & ws.Cells(iRow, 2)

'clear the data
Me.LstNm.Value = ""
Me.FrstNm.Value = ""
Me.LstNm.SetFocus

For Each ws In Worksheets
If ws.Name = newSheetName Or _
newSheetName = "" Or _
IsNumeric(newSheetName) Then
MsgBox "Sheet already exists or name is invalid", vbInformation
Exit Sub
End If

Next
Sheets("Student Sheet").Copy before:=Sheets(1)
Sheets(1).Name = newSheetName
Sheets(newSheetName).Move After:=Sheets(Sheets.Count)

'close userform
Unload Me
End Sub

 

[oberon.black]

help, ehlp, hel

 

[Nick Hebb]

Oberon,

If I understand you correctly, you want to:
(1) Create a new sheet based on a cell's content's
(2) Hide the new worksheet
(3) Add a hyperlink to the main sheet (cgs) to the new worksheet.
(4) Make the new sheet visible when clicking on the hyperlink

If so, for (3) just record a macro while inserting a hyperlink link to
another sheet. The code is fairly straightforward. When prompted for
the hyperlink target, select the "Place in the Document" option.

Then for (4), in the cgs sheet, add the following to the worksheet
events:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

Dim sSheet As String
Dim i As Integer
Dim ws As Worksheet

sSheet = Target.TextToDisplay
i = InStr(loc, "!")
If i > 0 Then
loc = Left(loc, i - 1)
End If
For Each ws In Application.ActiveWorkbook.Worksheets
If ws.Name = sSheet Then
ws.Visible = xlSheetVisible
ws.Select
End If
Next
End Sub

HTH,

Nick Hebb
BreezeTree Software
http://www.breezetree.com