Prevent scrolling except when following hyperlink

[alexsheehan]

Hi guys & gals


Related to my other posts (in that it's the same spreadsheet I'm working on);-

http://chandoo.org/forums/topic/change-in-cell-tickcheck-box-value-in-multiple-ranges-using-selectionchange


and


http://chandoo.org/forums/topic/set-data-validation-box-to-top-value-within-search-criteria

I have a worksheet called "Indicator" which contains hyperlinks which, when someone clicks on them, activates my "Database2" worksheet and takes them directly to the appropriate bit of data to be able to edit it.


In my "Database2" worksheet, I would like to be able to prevent the user from scrolling vertically/horizontally, but ONLY once the hyperlink has taken them to the appropriate data. In other words, if they have clicked on a hyperlink in the "Indicator" worksheet, it correctly takes them to cell(s) "$C$171", "$C$205", "$C$239" etc. etc. in my "Database2" worksheet, but once there, it prevents them from scrolling up/across to prevent them from seeing redundant data.


I have all my hyperlinks working correctly, navigating their merry way to the correct location of my "Database2" worksheet, I just need some assitance with the coding to "lock" the window in pace once there.


Does anyone have any ideas?


Thanks very much in advance.


Alex

 

[prasaddn]

Hi,


The one option I think of is hiding all the rows and columns of database2 sheet. Then in your code of hyperlink which takes you to database2, make the respective rows and columns unhide.


Once the editing is done and when the user has to saved, hide those rows and columns and take the focus back to main sheet.


I would look forward for any better solutions, if any.


Regards,

Prasad

 

[Luke M]

This may be a bit drastic, but these will lock/unlock movement:

[pre]

Sub FreezeSheet()
ActiveSheet.ScrollArea = ActiveCell.Address
End Sub

Sub UnfreezeSheet()
ActiveSheet.ScrollArea = ""
End Sub
Scroll area controls what all you can select/view. So, in it's current form, this macro won't let you select any other cell. If you need more, might need to do something like

Sub FreezeSheet()
ActiveSheet.ScrollArea = ActiveCell.Resize(50, 10).Address
End Sub

[/pre]
This defines the usable range as activecell + 50 rows down + 10 columns to right. Using this, plus a combination of the Offset method, should let you freeze whatever area you want.

 

[alexsheehan]

Thanks Prasad and Luke - that sounds like the sort of thing I want, I will play around with both options and see which works best for me. Will try tomorrow as I am at home now.


Cheers fellas!

 

[alexsheehan]

Thanks fella's


I have gone with Luke's suggestion above with the following amendments (locking movement but with select range(s) allowed;-

[pre]

Private Sub Worksheet_Activate()
ActiveSheet.ScrollArea = ActiveCell.Resize(26, 15).Address
End Sub

Private Sub Worksheet_Deactivate()
ScrollArea = ""
End Sub

[/pre]

Firing the "lock" code when the user click on the hyperlink and it activates the relevant sheet/cell range, then firing the "unlock" code when the user navigates back to the menu when finished editing.


The good thing with this is that I don't need any "offset" value(s). My hyperlink activates the sheet and selects the appropriate range to view (and sets the currently active cell to the top of the Excel Window), then only fires the "lock" code after the relevant section of the sheet has been reached.


Nice!


Although I can see where Prasad was coming from, Luke's code just seemed a little "cleaner". Thanks Prasad and Luke for your continued assistance, my inferior brain salutes you!


Best regards