Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help creating link & formula from user input
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/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help creating link & formula from user input
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help creating link & formula from user input
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 & "!G279,""Discharge"",IF( & MyInput & "!G271,""Final""," & _ "IF(" & MyInput & "!G263,""Second"",IF(" & MyInput "!G255,""First""," & _ "IF(" & MyInput & "!G231,""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'5 5,"First",IF(turner!'G2'31,"Informational","")))) ) Whats wrong with the code? As I said I am new -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How get user input that can then be used in formula, as criteria? | Excel Worksheet Functions | |||
Can I request user in input variable value within formula? | Excel Worksheet Functions | |||
Have user input converted to uppercase in same cell as input? | New Users to Excel | |||
How can I calculate user input from a combo box into a formula? | Excel Worksheet Functions | |||
CODE to select range based on User Input or Value of Input Field | Excel Programming |