Hyperlink to activate macro that jumps to different part of same sheet

[RichTea88]

This one probably seems like a stupid request, since I could just use a regular hyper-link to jump to a different part of the same sheet.

However, I'd like to lock people into certain areas of the sheet until the hyper-link is clicked. So when they enter the page the scroll area is set to A1:FP66. When the hyper-link is clicked, it would change the scroll area to A103:FP170 and take the user to that part of the worksheet. There would be a hyper-link within this range that would return them. Is this possible?

I know its possible to activate a macro using a hyper-link. Currently got this for my code but it doesn't work at all.

Please Login or Register  to view this content.

[ragulduy]

I would expect that target.range.address would be something like $A$1 rather than what you are testing.
It's normally best to use intersect, for example:

Please Login or Register  to view this content.

[RichTea88]

Hey Yudlugar, Yeah I originally had it as that but had no luck. Thought I'd try specifying it a bit more but to no avail either.

Could you elaborate on the above method a little more?

[RichTea88]

Also in this context am I specifying the cell which the hyperlink is in, or is the target range address the cell which the hyperlink is going to? I've tried both with no results, but it would help knowing for sure!

Please Login or Register  to view this content.

[ragulduy]

Intersect Method
See AlsoApplies ToExampleSpecificsReturns a Range object that represents the rectangular intersection of two or more ranges.

expression.Intersect(Arg1, Arg2, ...)
expression Optional. An expression that returns an Application object.

Arg1, Arg2, ... Required Range. The intersecting ranges. At least two Range objects must be specified.

Example
This example selects the intersection of two named ranges, rg1 and rg2, on Sheet1. If the ranges don't intersect, the example displays a message.

Worksheets("Sheet1").Activate
Set isect = Application.Intersect(Range("rg1"), Range("rg2"))
If isect Is Nothing Then
MsgBox "Ranges do not intersect"
Else
isect.Select
End If




-----------

so if not intersect(rng1,rng2) is nothing will return true for the if statement if the two ranges intersect.

[ragulduy]

I didn't really follow the code that well, I'm not sure why there is a 2 in the followhyperlink event?

I would probably try something like:

Please Login or Register  to view this content.

which would set the scroll area to A1:FP66 if the destination of the hyperlink is within that range.

[RichTea88]

Sorry, I'll try give you a bit more context.

When the sheet is activated, the scroll area is set to A1:FP66. Within that part of the sheet there is a hyperlink to $A$167

When I click that hyperlink, It'd like it to execute a macro to change the scroll area to A103:FP170 and also take me to $A$167.

I'd have a hyperlink within that range that does the same but takes me back to $A$1 and re-sets the scroll area to A1:FP66

[ragulduy]

Please Login or Register  to view this content.

[RichTea88]

Hmmm, Doesn't seem to be working I'm afraid.

I'm not entirely sure how to use the intersect properly for this, I'm absolutely rubbish at understanding the syntax of VB

[ragulduy]

Can you upload an example workbook?

Target.subaddress is where the hyperlink links to, I believe, so it tests if the hyperlink destination is within your different scrollareas, and if it is, sets it to that scroll area.

[RichTea88]

if its any help the hyperlink to $A$167 is in cell 'Z41'
and the hyperlink back to $A$1 is in Cell 'F155'

[ragulduy]

ok I tried to set it up myself and this seems to work ok:

Please Login or Register  to view this content.

[RichTea88]

That seems to be working dude!

I understand how this works now. I was wondering, If I had another hyperlink within the first range that linked to a different part of the worksheet again. Would that be possible or would that completely mess with what we've just written?

[ragulduy]

you would need to define each "area" separately. My suggestion would be to do this with named ranges.

for example, create named ranges called "scrollarea1", "scrollarea2" and "scrollarea3"

then your macro would be:

Please Login or Register  to view this content.