Help creating link & formula from user input

[bturner2]

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!G2>79,"Discharge",IF(#REF!G2>71,"Final",IF(#REF!G2>63,"Second",IF(#REF!G2>55,"First",IF(#REF!G2>31,"
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.

 

[Chip Pearson]

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 & "!G2>79,""Discharge"",IF(" & MyInput &
"!G2>71,""Final""," & _
"IF(" & MyInput & "!G2>63,""Second"",IF(" & MyInput &
"!G2>55,""First""," & _
"IF(" & MyInput & "!G2>31,""Informational"","""")))))"



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[bturner2]

Thanks for the help Mr. Pearson. It all worked the way it was suppose
to work. I am however having one problem. The code for the IF statemen
formula is being run by the code itself. I actually needed to inser
the formula into a cell. Not saying that I didn’t find the informatio
you gave me useful. I did. It actually answered another question that
had.
Let me explain in more detail of what and why I am doing this.
This sheet is to track sick leave for approx 200 employees. On th
front sheet will be all of the employee’s names which are hyperlinks t
their own page. Beside the names (hyperlink) I needed to insert th
formula to give the person managing the sheet a quick reference as t
what state of discipline the person is in.
If the person has X amount of hours of sick leave it will displa
“Informational” beside their name, Z amount of hours it will display
Written" and so on and so forth.

I did play with what you posted concerning the IF statement, and I hav
it inserting the formula into the cell using the variable that is give
by the user. It however is placing single quotes around the cel
reference which causes the formula to malfunction.

Here is the code I am using for this step.
ActiveCell.FormulaR1C1 = "=IF(" & MyInput & "!G2>79,""Discharge"",IF(
& MyInput & "!G2>71,""Final""," & _
"IF(" & MyInput & "!G2>63,""Second"",IF(" & MyInput
"!G2>55,""First""," & _
"IF(" & MyInput & "!G2>31,""Informational"","""")))))"

Here is the result that is being placed in the cell.
=IF(turner!'G2'>79,"Discharge",IF(turner!'G2'>71,"Final",IF(turner!'G2'>63,"Second",IF(turner!'G2'>55,"First",IF(turner!'G2'>31,"Informational","")))))

Whats wrong with the code? As I said I am new