Macro to activate Hyperlink not working

[Rick23]

Hello,

I am trying to record a macro that when a button is pushed goes to another sheet and clicks on a hyperlink to go to another worksheet within the same workbook. This hyperlink has an IF statement, where depending on 2 scanarios will change the hyperlink to go to 1 of 2 other tabs. When I record the macro and click on the hyperlink it goes to the tab the hyperlink says, but when I go change the scenario to the other hyperlink/tab it still goes to the recorded macro tab. How to I make the macro go to the tab I want depending on what the IF statement is saying? This is what my code looks like. Thanks again

Please Login or Register  to view this content.

[Tsjallie]

Use

Please Login or Register  to view this content.

Where B1 contains the address or name of the cell to jump to.
A scenario can put the right address or name in B1.

[Rick23]

Thank you so much for the reply. Unfortunately, when I tested the macro with this new formula provided and changed the IF statement to the other hyperlink (tab), it still took me to the same page when I originally recorded the macro. Is there something in the VBA you have to change to let the mouse click on the hyperlink that contains the formula (if-statement) when you record the macro? Below are the 2 recorded macros I have tried. thanks again for the help.


This one goes to the same tab everytime after clicking the hyperlink during the recorded macro no matter what hyperlink it says

Please Login or Register  to view this content.

[Rick23]

Or if there is another easier way of doing this I am open to any suggestions. I basically want to click on a shape and depending on if cell A1 starts with AAA it will take me to tab 2, if A1 starts with BBB I would like it to take me to tab 3

[Tsjallie]

Can you upload your workbook (or a relevant part of it) so I can take a look.
Here's an example with a button jumping to a location based on a scenario.
Blad1 contains a button which jumps you to a location.
Blad2 contains the dynamic hyperlink in A1 but actually not necessary. F1 contains the name of the location to jump to. F1 is filled by a scenario.
Blad3 contains location 1.
Blad4 contains location 2.

[Rick23]

This is perfect! Thank you so much, I will go off of this workbook. thanks again

[Rick23]

Thanks again for your help on this one. I have another situation in my project where same situation as above, I have 2 hyperlinks that change depending on an if statement, one link goes to another sheet (tab), but this time the other link will run a macro. I have already recorded the macro and ready to put it in the VBA code, but just not sure how this would look. Any suggestions? thanks again

[Tsjallie]

Here's the same example with a scenario running a macro.
Not sure if this strategy will do any good to the stability of your workbook.
What actually are you trying to achieve?

[Rick23]

Thanks again for the spreadsheet. I basically have about 6 buttons and to make it easy they are named 1-6, each button will have a manual recorded macro to will put the corresponding number in one specific cell. Depending on what the number is it will change between 2 hyperlinks (lets just call the hyperlinks odd or even. If the hyperlink says odd, then the macro/hyperlink will take me to another sheet in the spreadsheet, if the hyperlink says even then it will run another macro that I have prerecorded already that will basically just copy and paste a couple sentences into another sheet. My first issue is how can I run a recorded macro to put the number in the specific cell and then would automatically push the hyperlink button (or I could see the VBA code on that macro) and also how would the 2nd macro (hyperlinks) look going to another cell or copy and pasting a couple sentences. Thanks again for the help. I tried the spreadsheet you provided and I pushed the macro hyperlink button and the vba code came up. i went back to the jump button and it no longer worked. Thanks again for all of your help.

[Rick23]

Or if you think there is an easier way to accomplish what I am trying to do I am open to any suggestions, as maybe there is something out there that is easier to accomplish rather than a hyperlink.

[Tsjallie]

So if I understand it correctly, you're looking to build a control&navigation sheet.
I'm attaching a new example with your solution in Blad5 together with some comments. An alternative - more direct - approach goes in Blad6.

I tried the spreadsheet you provided and I pushed the macro hyperlink button and the vba code came up.

After I posted the previous example I ran into this too. Reposted the example with an improved formula for hyperlink in Blad2.A1 which prevents you from inadvertedly winding up in the VB Editor.

[Rick23]

Thanks again for taking the time to make up another spreadsheet. I have already learned alot. I think the example I gave you was a crappy one to what I am exactly trying to do. I think I almost have it though. Below is my code of what I have so far. As you can see, Sheets("Formulas").Range("K18") is where it will either say "IRTermOTA" or "IRTermAA" When K18 says IRTermOTA, I am able to click on the hyperlink in the cell next to it that does take to me the cell I desire. No problems there. When K18 says "IRTermAA" and I click on the hyperlind #IRTERMAA it takes me to the VBA code and doesnt run the prerecorded macro I have named, just like the spreadsheet you sent me. (i opened up the new spreadsheet sent to me and it still brought up the vba code after clicking on the #macrotorun hyperlink and also I clicked on the "jump" shape in Blad1 and didnt do anything. The name of my macro that I wish to run when K18states "IRTermAA", is typed exactly what my macro is called in K19. Any ideas on where I am going wrong?

Also, I liked your code ([O5] = Blad5.Shapes(Application.Caller).TextFrame.Characters.Text) I would like to incorporate this into my code below. The shapes that i have that i would like the Text on the shapes to be entered in a cell is on another sheet called "IRTERM" and most of my formulas and code above is in the "formulas" sheet. Once the text of the shape is entered into the desired cell, this will change if K18 is either "IRTermOTA" or IRTermAA"

My ultimate goal is to click on one of the 6 buttons in the sheet "IRTerm" which puts the text in that particular shape into a desired cell, then depending on what that text is in that desired cell will determine what K18 is "IRTermOTA" or "IRTermAA". if it is "IRTermOTA" then I would like to go to the sheet TermTentOTAdueto".Range("C6") - which I think I have working cause when I click on the "#"$K18 hyperlink and it takes me to the correct cell (Also, I did put IRTermOTA destination in the Name Manager Formula found in the Ribbon, I am just having trouble starting the prerecorded macro when K18 says "IRTERMAA" (the prerecorded macro just takes some text in the formulas sheet and copies it and pastes it into a complete other cell.

thanks again for the help, I really wouldn't be nearly as far if it werent for the help.



Sub TENTtermmacroAAOTA()
'
' TENTtermmacroAAOTA Macro

' Sheets("Formulas").Select
Select Case Sheets("Formulas").Range("K18")
Case "IRTermOTA"
LocationToJumpTo = Sheets("TermTentOTAdueto").Range("C6")
Application.Goto Reference:=LocationToJumpTo
Case "IRTermAA"
MakroToRun = Sheets("Formulas").Range("K19")
Application.Run MakroToRun
End Select
End Sub

[Tsjallie]

When K18 says "IRTermAA" and I click on the hyperlind #IRTERMAA it takes me to the VBA code and doesnt run the prerecorded macro I have named, just like the spreadsheet you sent me. (i opened up the new spreadsheet sent to me and it still brought up the vba code after clicking on the #macrotorun hyperlink

Please check if Blad2 looks like this DynamicHyperlinkWithMacro_Blad2.JPG when the scenario "ExecMacro" is selected. Notice that in this case cell A1 is empty and thus not containing a hyperlink.

also I clicked on the "jump" shape in Blad1 and didnt do anything.

Sorry, cannot reproduce that.
Maybe you can upload your workbook, so I can have a look?

[Rick23]

I pulled up the excel sheet from this post and went to Blad2 and it still has the IF statement instead of the ALS with (,) instead of ( between the arguments. I then typed in exactly what you had and it gives me an error and highlights "jump".

let me know what you think about why I see the IF statement instead of the ALS. If you are unsure about this I will send you my workbook so you can take a look. thanks again

[Rick23]

the wink you see in the 2nd line is supposed to be a semi colon

[Rick23]

Ok, I did some more playing around and I think I have accomplished what I want. Below is the code and it does work as far as getting to where I would like to go and do. I scratched the hyperlinks and just went with macros. After pressing a shape it started one of the 2 macros in A27. I really liked your code where when the shape is pressed it puts the corresponding text in from that shape into a specific cell. i would like that text from the shape to go into A26. So overall, my last question is what would the code look like if I wanted to start with pressing a shape, putting the corresponding text from the shape into A26, then from there A27 would either populate IRTERMMacro or IRTermOTA depending on what A26 showed and would finish the sequence with just one push of a button. Thanks again Tsjallie


Sub IRTERMtoAAOTA_macro()


If Range("A27").Value = "IRTERMAAMacro" Then
Call IRTERMAAMacro
ElseIf Range("A27").Value = "IRTermOTA" Then
Call IRTermOTAMacro
Else
Return
End If

End Sub

[Rick23]

Also, I added a - Call macro before the "If Range ("A27).Value line and it did run the macro, then went to the next set of "IF" macros, so everything worked great. I see that your code for assigning text to a cell from the corresponding shape is under the "set numbers," but if I wanted to have 1 macro for all of the shapes on one sheet that is generic like the "[O5] = Blad5.Shapes(Application.Caller).TextFrame.Characters.Text" you provided is that possible? Then I can just copy and paste this for my "call macro" before the if statement macros start. thanks again

[Tsjallie]

why I see the IF statement instead of the ALS

Sorry, my slip. That's because I'm using a Dutch version of Excel. "IF" in Dutch is "ALS". Consider it a free course of Dutch for Beginners
In your version of Excel when scenario ExecMacro is selected, cell F1 in Blad2 (=Sheet2) should contain the string "MacroToRun", cell G1 should contain the string "Macro", cell A1 should contain the formula

Please Login or Register  to view this content.

and the value of cell A1 should be "" (empty) because G1<>"Jump".

if I wanted to have 1 macro for all of the shapes on one sheet that is generic like the "[O5] = Blad5.Shapes(Application.Caller).TextFrame.Characters.Text" you provided is that possible?

Yes, that should work as "Application.caller" refers to the shape kicking off the macro and .TextFrame.Characters.Text refers to the text in that shape. So with this statement you can copy the text of a shape to a cell. Change [O5] to any cell you want and - of course - change Blad5 to the name of the sheet the shape is on in your workbook.

[Rick23]

I am not sure why I am having a hard time with this macro. i pretty muched copied and pasted your code

Sub SetNumber()
'Put the number on the button in cell A26
[A26] = IRTERM.Shapes(Application.Caller).TextFrame.Characters.Text
End Sub

and it highlights the A26 line and does not work. My ultimate goal is to take the "setnumber" macro and stick it in my main macro that will put the text in the cell and then run the if statement. My code is below. Any idea how to get the codes working?

Sub IRTERMtoAAOTA_macro()


If Range("A27").Value = "IRTERMAAMacro" Then
Call IRTERMAAMacro
ElseIf Range("A27").Value = "IRTermOTA" Then
Call IRTermOTAMacro
Else
Return
End If

End Sub

[Tsjallie]

Can you upload your worksbook so I can have a look at it.

[Rick23]

ok, for some reason I tried it out today and I got it to work. Not sure what the difference was from the last time. Thanks again Tsjallie. You really helped me out alot.

[Tsjallie]

Not sure what the difference was from the last time.

Probably because that was on an odd day. Sometimes Excel is almost human ...:-)