Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Navigation
I have a workbook with 12 worksheets. I want to be able to hyperlink from
one worksheet to another, then use a command button to return to the previous button (i.e. fire HL on WS1 which goes to specific location on WS2, then fire a macro via command button to return to original location in WS1). Not sure how to do this. Can you pass variables to/from PSubs in the same module? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Navigation
First, in ThisWorkbook's code module, put in this event procedu
Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink) Set LinkBack = Target.Range End Sub LinkBack is a Global variable defined in a separate code module: Public LinkBack as Range Finally, the code for the buttons would be like this: Sub Button1_Click() LinkBack.Parent.Activate ' to activate the source sheet LinkBack.Activate ' to activate the source range End Sub "subseaguy" wrote: I have a workbook with 12 worksheets. I want to be able to hyperlink from one worksheet to another, then use a command button to return to the previous button (i.e. fire HL on WS1 which goes to specific location on WS2, then fire a macro via command button to return to original location in WS1). Not sure how to do this. Can you pass variables to/from PSubs in the same module? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Navigation
OK.. let me make sure I understand...
On WS1 (where I am initiating HL) tab, "View Code" and insert Public LinkBack as Range Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink) Set LinkBack = Target.Range End Sub Then in workbook, insert code for button; Sub Button1_Click() LinkBack.Parent.Activate ' to activate the source sheet LinkBack.Activate ' to activate the source range End Sub Have I understood your suggestion? Thanks, SubseaGuy "K Dales" wrote: First, in ThisWorkbook's code module, put in this event procedu Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink) Set LinkBack = Target.Range End Sub LinkBack is a Global variable defined in a separate code module: Public LinkBack as Range Finally, the code for the buttons would be like this: Sub Button1_Click() LinkBack.Parent.Activate ' to activate the source sheet LinkBack.Activate ' to activate the source range End Sub "subseaguy" wrote: I have a workbook with 12 worksheets. I want to be able to hyperlink from one worksheet to another, then use a command button to return to the previous button (i.e. fire HL on WS1 which goes to specific location on WS2, then fire a macro via command button to return to original location in WS1). Not sure how to do this. Can you pass variables to/from PSubs in the same module? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Navigation
Not quite: The main requirement is that the Workbook_SheetFollowHyperlink
Sub must go in ThisWorkbook's code module. That is, when you are in the VB editor, make sure you can see the Project Explorer and double-click on the line that says "ThisWorkbook." You can make sure you are typing it in the right place by checking the title bar of the VB editor: it should say something like "Microsoft Visual Basic - Book1 - [ThisWorkbook (Code)]" The reason the sub must be in here is that it is an event procedure that runs automatically whenever the specified event takes place - in this case whenever a hyperlink is followed. But that operates for the workbook as a whole and so it has to be in the code module for the workbook for it to be recognized and to function. For the Button_Click code, the easiest thing to do is create the button from the forms toolbar and then when the "Assign Macro" dialog comes up, specify "New..." It will create a new module and you can type the code in there. But you can also type that code in any module (ThisWorkbook, Sheet1, ...) and manually assign the macro to your button as long as you make it a Public Sub. Same thing with the global variable LinkBack - it can go in any module as long as it is declared Public. -- - K Dales "subseaguy" wrote: OK.. let me make sure I understand... On WS1 (where I am initiating HL) tab, "View Code" and insert Public LinkBack as Range Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink) Set LinkBack = Target.Range End Sub Then in workbook, insert code for button; Sub Button1_Click() LinkBack.Parent.Activate ' to activate the source sheet LinkBack.Activate ' to activate the source range End Sub Have I understood your suggestion? Thanks, SubseaGuy "K Dales" wrote: First, in ThisWorkbook's code module, put in this event procedu Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink) Set LinkBack = Target.Range End Sub LinkBack is a Global variable defined in a separate code module: Public LinkBack as Range Finally, the code for the buttons would be like this: Sub Button1_Click() LinkBack.Parent.Activate ' to activate the source sheet LinkBack.Activate ' to activate the source range End Sub "subseaguy" wrote: I have a workbook with 12 worksheets. I want to be able to hyperlink from one worksheet to another, then use a command button to return to the previous button (i.e. fire HL on WS1 which goes to specific location on WS2, then fire a macro via command button to return to original location in WS1). Not sure how to do this. Can you pass variables to/from PSubs in the same module? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Navigation
KD,
I did what you said and I get an "Object Required" error (424). Any thoghts? "subseaguy" wrote: I have a workbook with 12 worksheets. I want to be able to hyperlink from one worksheet to another, then use a command button to return to the previous button (i.e. fire HL on WS1 which goes to specific location on WS2, then fire a macro via command button to return to original location in WS1). Not sure how to do this. Can you pass variables to/from PSubs in the same module? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Tab navigation in a protected worksheet | Excel Discussion (Misc queries) | |||
Creating navigation buttons on a worksheet | New Users to Excel | |||
Worksheet Navigation | Excel Programming | |||
Worksheet Navigation | Excel Discussion (Misc queries) | |||
Excel worksheet tabs navigation | Excel Worksheet Functions |