Try something like
ActiveSheet.Hyperlinks.Add Anchor:=Selection,Address:="", _
SubAddress:=MyInput & "!A1", TextToDisplay:=MyInput
For the formula, use code like
Dim MyInput As String
MyInput = InputBox("Enter Sheet Name")
Selection(1, 2).Formula = _
"=IF(" & MyInput & "!G279,""Discharge"",IF(" & MyInput &
"!G271,""Final""," & _
"IF(" & MyInput & "!G263,""Second"",IF(" & MyInput &
"!G255,""First""," & _
"IF(" & MyInput & "!G231,""Informational"","""")))))"
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
"bturner2 " wrote in
message ...
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,"S
econd",IF(#REF!G255,"First",IF(#REF!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/