?
Solved

Returning to  'calling' hyperlink cell from another hyperlink.

Posted on 2002-05-01
10
Medium Priority
?
293 Views
Last Modified: 2012-02-07
I'm sure this is quite easy to explain, but as usual I’ll drag it out a bit :)  sorry.

In sheet1 of excel I have hyperlinks to sheet2. In sheet 2 I have one hyperlink "BACK".

How do I make the BACK hyperlink go to the cell in sheet1 that just called it? The problem is that the cells in sheet1 that contain the hyperlinks to sheet2 are likely to move about so no 'hard coding' of cell references is possible.

I have tried to think of a way top make VB do this but failed so I turned to the Microsoft Script Editor, but that's written in Java (at a guess) and I’m even worse with that than I am with VB.

Can anyone make a suggestion of how to do this or if I’ve made myself unclear please post a comment or two asking me questions.

Thanks ppl.
0
Comment
Question by:craigewens
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6983665
Create a global variable of type range.
dim rngHyp as range
When you do click on hyperlink 1, set that variable to cell with hyperlink.
On sheet 2, call hyperlink "back" and do something like:

rngHyp.select


Hope it helps
0
 
LVL 2

Author Comment

by:craigewens
ID: 6985487
Thanks for your comment Richie and i understand what your saying (partially) but all the hyperlinks are created through excel rather than VB, this means (as far as i can figure out) that VB has no knowledge of them.

If you have the time, could you add a little more to your previous comment because atm the vb sheet is blank... :(

Thanks

Craig.
0
 
LVL 2

Author Comment

by:craigewens
ID: 6999158
Richie, i've played about even more with what you suggested but all the stuff i create in VB through excel requires to be 'run' (macro).

Can you think of a way to do this still?

If your unsure open up excel and try what i am trying to do (takes about 10 seconds), you'll soon see where i'm having the troubles.

Thanks.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6999239
For reason thta is unknown, i did not receive your first comment notif.
Let me see....
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6999342
Just a question: why did you use a hyperlink to get this work?
0
 
LVL 16

Accepted Solution

by:
Richie_Simonetti earned 400 total points
ID: 6999420
we could add a custom commandbar and work with two buttons.
the code behind them would be:

'in a module
Public Sub MovePrevious()
On Error GoTo errTrap
Sheets(ActiveSheet.Index - 1).Activate
Exit Sub
errTrap:
If Err = 9 Then
    Resume Next
End If
End Sub
Public Sub MoveNext()
On Error GoTo errTrap
'Set rngActive = ActiveSheet.ActiveCell
Sheets(ActiveSheet.Index + 1).Activate
Exit Sub
errTrap:
If Err = 9 Then
    Resume Next
End If
End Sub
0
 
LVL 2

Author Comment

by:craigewens
ID: 6999560
Thanks ever so much for replying so quickly, once EE started sending out the notifications again :)

But i found this on another question:

Sub BackToWhenceYouCame()
  On Error GoTo skip
  CommandBars("Web").Controls("Back").Execute
  skip:
End Sub

It seems to do what i'm after and works from calling this sub from any command button click.

Can you think of any reasons or situations why i shouldn't use this? (version dependant, OS dependant etc etc)

Of course you can still have the points anyway as almost every question i ask or search for you seem to have given the accepted answer :)

Thanks.
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6999596
I don't know if it would works on office97.
Dont care about points, i like points ONLY if i deserve them.
Cheers
0
 
LVL 2

Author Comment

by:craigewens
ID: 6999633
Well i tested your code and it is just as effective as the other suggestion i found. Seeing as you have your doubts on if it'll work in other versions of office i guess you ONLY deserve the points after all  :)

Thx
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 7001324
Well, thanks for "A" grade!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month9 days, 19 hours left to enroll

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question