activate/deactivate the scroll button by vba
Don't know about a Scroll Lock function. Do you mean ScrollArea function
which limits the area that can be selected or scrolled into? If so then
create a button from the Control Toolbox toolbar (Not Forms toolbar) and
while in Design Mode (Design Mode is toggled on and off with a button that
looks like a blue set square with a ruler and pencil and it defaults to
Design On on when you create the button.)
Right click the button just created and select properties. Make a note of
the Name of the button (Top of list in dialog box). Click X to close dialog
box.
Right click button again and select View code and the VBA editor will open
with the following 2 lines:-
Private Sub CommandButton1_Click()
End Sub
Copy and the code below and paste in between the sub name and end sub.
Check the comments in green and edit code as required.
Close the VBA editor. (X with red background in top right corner )
Now every time you click the button it will toggle the ScrollArea on and off
and also it toggles the Caption on the button so that you only need one
button.
'Check command button name in properties
'and edit name if not CommandButton1
If ActiveSheet.CommandButton1.Caption = "Lock Scroll" Then
ActiveSheet.ScrollArea = "A1:K20"
'Edit range so it is within the ScrollArea
ActiveSheet.Range("A1").Select
ActiveSheet.CommandButton1.Caption = "Unlock Scroll"
Else
ActiveSheet.ScrollArea = ""
ActiveSheet.CommandButton1.Caption = "Lock Scroll"
'Edit range to select required cell when unlocked
ActiveSheet.Range("A1").Select
End If
--
Regards,
OssieMac
"MicrosoftNews" wrote:
hi all,
i'm looking for a code to activate the scroll lock function by vba.
i need to have two buttons on a excel sheet. one to activate this function
and one to switch off this function.
can someone give me a hand ?
thanks in advance
achim
XL2003/WINXPHome
|