I am new to VB ( so be nice <G)and I am hoping someone can help solve a
problem I can’t figure out.
What I am trying to do is set up a script that will setup an excel
workbook that is customized for several different departments at work.
I want to take a name from the user and create a new sheet with the
name supplied by the user and format it properly. I have this part
working. I then need the script to create a hyperlink on sheet1 that
will point to the newly created sheet. This part I can’t get to work.
If I leave the subaddress in quotes the link won’t work and if I take
the quotes out it croaks because of the cell reference. Here is the
code I have so far.

Dim MyInput As String
MyInput = InputBox("Enter the name")
Range("k11").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="",
SubAddress:= _
MyInput!a1, TextToDisplay:=MyInput
End Sub

I also need to customize a formula beside the hyperlink that is
dependent on the newly created sheet. Can anyone suggest how I can take
the input that I have already gotten from the user and plug it into and
write the formula. Here is the formula. #REF will equal the name of the
newly created sheet.

=IF(#REF!G279,"Discharge",IF(#REF!G271,"Final",I F(#REF!G263,"Second",IF(#REF!G255,"First",IF(#RE F!G231,"
Informational","")))))

Any help would be greatly appreciated. I have already spent several
hours trying to figure this (reading mostly) out and since it is being
done on my own time I would like to spend as little time as needed.


---
Message posted from http://www.ExcelForum.com/