Unlocking/locking ScrollArea when following Hyperlinks

[alexsheehan]

Hi all


I have a "Dashboard" worksheet (menu) with hyperlinks to all schools within the area, each hyperlink goes to the appropriate section of an "IncidentsDashboard" tab which contains incidents of Bullying/Racism for a particular school - for example, clicking on the first school name will take me to range "W2:AC17" in "IncidentsDashboard" and clicking on the second school name will take me to range "W20:AC35" within "IncidentsDashboard".


In my "IncidentsDashboard", I have a breakdown of all Bullying/Racism incidents/types for each school (Verbal, Cyber, Physical etc.) so that if someone clicks on "Racism" for a particular school, it will take them to the "Racism" section of the sheet for that school (for the first school this is "AC2:AI17") and within this section, they can click on a hyperlink for RacismType ("Verbal" for instance) and it will take them to a further breakdown of perpetrator(s)/victim(s)/date/time etc. of the incident(s).


My question is, am I able to follow the first hyperlink, lock the ScrollArea until another hyperlink is clicked, follow hyperlink to another range, lock ScrollArea again etc. etc.?


The code I have so far is below, which works fine to intially lock the scroll area for the first range selected, but I am unsure how to "unlock" it when another hyperlink is clicked within the same worksheet and also, I am not sure if I am using the correct syntax or whether I have some unnecessary code in there or not - ;-)


Can someone please help me? What I want is to be able to dynamically set the ScrollArea to whatever range my hyperlink goes to ("W2:AC17", "W20:AC35" etc.), then unlock ScrollArea ONLY when a Hyperlink is clicked, take me to the Hyperlink location ("AC2:AI17", "AC20:AI35" etc.) and then lock the ScrollArea again.


I've tried using this for zooming/locking;-


'Private Sub Worksheet_Activate()


ActiveSheet.ScrollArea = ActiveCell.Resize(15, 6).Address

ActiveSheet.Range(ActiveCell, ActiveCell.Offset(15, 6)).Select

ActiveWindow.Zoom = True


End Sub'


Which does the trick for locking/setting ScrollArea for my first range "W2:AI17", however, I am going insane trying to think of a way to unlock the range when a another Hyperlink is clicked (for instance the "Back to Menu" / "Racism" / "Bullying" Hyperlinks won't function when ScrollArea is locked??). Is this possible? Am I making sense? Am I being completely dense? Probably..., does anyone want to shoot me now? I don't blame you! Put me out of my misery!


Does this have something to do with the WorkSheet_SelectionChange?


My current Worksheet_SelectionChange looks like this;-


'Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Select Case True


Case Target.Count = 1 And ( _

Target(1, 1).Value = "Bullying" Or _

Target(1, 1).Value = "Racism" Or _

Target(1, 1).Value = "Bullying Nil Return" Or _

Target(1, 1).Value = "Racism Nil Return" Or _

Target(1, 1).Value = "Nil Return for Bullying and Racism" _

)

ActiveCell.Select


End Select


ActiveWindow.ScrollRow = ActiveCell.Row


End Sub'


But I don't know how to tell it to say "if target is hyperlink, unlock ScrollArea, follow hyperlink, lock ScrollArea to current range again"


Cheers guys

 

[Luke M]

You can use the FollowHyperlink event. Since we need several events to call a "locking" macro, I've moved that to a separate sub. Overall, should look something like this:

[pre]

Private Sub Worksheet_Activate()
Call LockScroll
End Sub

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

ActiveSheet.ScrollArea = ""
'Have to turn this off so we don't get stuck in an infinite loop
'of FollowHyperlink events
Application.EnableEvents = False
Target.Follow
Application.EnableEvents = True
Call LockScroll
End Sub

Private Sub LockScroll()
ActiveSheet.ScrollArea = ActiveCell.Resize(15, 6).Address
ActiveSheet.Range(ActiveCell, ActiveCell.Offset(15, 6)).Select
ActiveWindow.Zoom = True
End Sub

[/pre]

 

[alexsheehan]

Thanks Luke


This works, to a point... in that I can successfully navigate from "Dashboard" to "IncidentsDashboard" via my hyperlinks, and this successfully selects the correct range (in this instance "W40:AC55") "IncidentsDashboard" but when I select a hyperlink within the selected range (Hyperlink for Bullying is in cell "X43", Hyperlink for Racism is in cell "X44" etc.), both the Bullying and Racism Hyperlink should go to cell "AC40" and subsequently set range "AC40:AI55" as the Viewable/Scrollable range.


Unfortunately the links just seem to select a range 15 x 6 from the currently clicked hyperlink i.e., when the Hyperlink for Bullying in "X43" is clicked, the range "X43:AC58" is selected and the view/scroll area set incorrectly to this range, rather than the hyperlink going to correctly to cell "AC40", and then selecting range "AC40:AI55" and then setting viewing area / scroll lock to "AC40:AI55".


Any ideas? The hyperlinks, wehn all the VBA is disabled, correctly locates to X43 so I don't understand why it is not working when the VBA kicks in?


Sorry for being a pain and thank yo ufor your help.


Cheers

 

[Luke M]

Since you're dealing with multiple sheets, things could get a little tricky. Make sure that all the event macros are on the correct worksheet module, and that any commonly used macros are in a regular module as labeled as Public.

 

[alexsheehan]

ok, the code I have (in my "IncidentsDashboard" WorkSheet so far is...


'Option Explicit


Private Sub Worksheet_Activate()


Call LockScroll


End Sub


Private Sub Worksheet_Deactivate()

With Worksheets("IncidentsDashboard")

ActiveSheet.ScrollArea = ""

ScrollArea = ""

End With

End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Select Case True

Case Target.Count = 1 And ( _

Target(1, 1).Value = "Bullying" Or _

Target(1, 1).Value = "Racism" Or _

Target(1, 1).Value = "Bullying Nil Return" Or _

Target(1, 1).Value = "Racism Nil Return" Or _

Target(1, 1).Value = "Nil Return for Bullying and Racism" _

)

ActiveCell.Select


End Select


ActiveWindow.ScrollRow = ActiveCell.Row

ActiveSheet.ScrollArea = ActiveCell.Resize(15, 6).Address

ActiveSheet.Range(ActiveCell, ActiveCell.Offset(15, 6)).Select

ActiveWindow.Zoom = True


End Sub


Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)


ActiveSheet.ScrollArea = ""


Application.EnableEvents = False

Target.Follow

Application.EnableEvents = True

Call LockScroll

End Sub


Private Sub LockScroll()

ActiveSheet.ScrollArea = ActiveCell.Resize(15, 6).Address

ActiveSheet.Range(ActiveCell, ActiveCell.Offset(15, 6)).Select

ActiveWindow.Zoom = True

End Sub'


Is there anything obvious you can see that is stopping this from working properly?


Cheers Luke