activate/deactivate the scroll button by vba
 

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

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

activate/deactivate the scroll button by vba
 

Hi Ossie,

I want to switch on and off the scroll led with the following code
-------------------------------------------------------------------------------------
Private Declare Sub keybd_event Lib "user32" ( _
ByVal bVk As Byte, _
ByVal bScan As Byte, _
ByVal dwFlags As Long, _
ByVal dwExtraInfo As Long)
Private Const VK_OEM_SCROLL = &H91
Private Const KEYEVENTF_KEYUP = &H2
Declare Function GetKeyState Lib "user32.dll" ( _
ByVal nVirtKey As Long) As Integer

Sub SCROLL_aktiv() 'SCROLL-Lock aktivieren (falls deaktiviert)
If Not (GetKeyState(vbKeyOEM_SCROLL) = 1) Then
keybd_event VK_OEM_SCROLL, 1, 0, 0
keybd_event VK_OEM_SCROLL, 1, KEYEVENTF_KEYUP, 0
End If
End Sub

Sub SCROLL_inaktiv() ' SCROLL-Lock deaktivieren (falls aktiviert)
If (GetKeyState(vbKeyOEM_SCROLL) = 1) Then
keybd_event VK_OEM_SCROLL, 1, 0, 0
keybd_event VK_OEM_SCROLL, 1, KEYEVENTF_KEYUP, 0
End If
End Sub
----------------------------------------------------------------------------------------

with the scroll_aktiv procedure i can toggle between on and off. but it
should only switch on the led. with scroll_inaktiv i can't switch of the
led - and i don't know why

the following procedure for the num lock led works fine
------------------------------------------------------------------------------
Private Declare Sub keybd_event Lib "user32" ( _
ByVal bVk As Byte, _
ByVal bScan As Byte, _
ByVal dwFlags As Long, _
ByVal dwExtraInfo As Long)
Private Const VK_NUMLOCK = &H90
Private Const KEYEVENTF_KEYUP = &H2
Declare Function GetKeyState Lib "user32.dll" ( _
ByVal nVirtKey As Long) As Integer

Sub NUM_TOGGLE()
' NUM-Lock drücken
' Zunächst niederdrücken und dann wieder loslassen
keybd_event VK_NUMLOCK, 1, 0, 0
keybd_event VK_NUMLOCK, 1, KEYEVENTF_KEYUP, 0
End Sub

Sub NUM_aktiv() 'NUM-Lock aktivieren (falls deaktiviert)
If Not (GetKeyState(vbKeyNumlock) = 1) Then
keybd_event VK_NUMLOCK, 1, 0, 0
keybd_event VK_NUMLOCK, 1, KEYEVENTF_KEYUP, 0
End If
End Sub

Sub NUM_inaktiv() ' NUM-Lock deaktivieren (falls aktiviert)
If (GetKeyState(vbKeyNumlock) = 1) Then
keybd_event VK_NUMLOCK, 1, 0, 0
keybd_event VK_NUMLOCK, 1, KEYEVENTF_KEYUP, 0
End If
End Sub
------------------------------------------------------------------------------------
thanks for any help ..



"OssieMac" schrieb im Newsbeitrag
...
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

activate/deactivate the scroll button by vba
 

Hi again,

I totally misunderstood what you were trying to do and I can't help with
your problem
--
Regards,

OssieMac


"MicrosoftNews" wrote:

Hi Ossie,

I want to switch on and off the scroll led with the following code
-------------------------------------------------------------------------------------
Private Declare Sub keybd_event Lib "user32" ( _
ByVal bVk As Byte, _
ByVal bScan As Byte, _
ByVal dwFlags As Long, _
ByVal dwExtraInfo As Long)
Private Const VK_OEM_SCROLL = &H91
Private Const KEYEVENTF_KEYUP = &H2
Declare Function GetKeyState Lib "user32.dll" ( _
ByVal nVirtKey As Long) As Integer

Sub SCROLL_aktiv() 'SCROLL-Lock aktivieren (falls deaktiviert)
If Not (GetKeyState(vbKeyOEM_SCROLL) = 1) Then
keybd_event VK_OEM_SCROLL, 1, 0, 0
keybd_event VK_OEM_SCROLL, 1, KEYEVENTF_KEYUP, 0
End If
End Sub

Sub SCROLL_inaktiv() ' SCROLL-Lock deaktivieren (falls aktiviert)
If (GetKeyState(vbKeyOEM_SCROLL) = 1) Then
keybd_event VK_OEM_SCROLL, 1, 0, 0
keybd_event VK_OEM_SCROLL, 1, KEYEVENTF_KEYUP, 0
End If
End Sub
----------------------------------------------------------------------------------------

with the scroll_aktiv procedure i can toggle between on and off. but it
should only switch on the led. with scroll_inaktiv i can't switch of the
led - and i don't know why

the following procedure for the num lock led works fine
------------------------------------------------------------------------------
Private Declare Sub keybd_event Lib "user32" ( _
ByVal bVk As Byte, _
ByVal bScan As Byte, _
ByVal dwFlags As Long, _
ByVal dwExtraInfo As Long)
Private Const VK_NUMLOCK = &H90
Private Const KEYEVENTF_KEYUP = &H2
Declare Function GetKeyState Lib "user32.dll" ( _
ByVal nVirtKey As Long) As Integer

Sub NUM_TOGGLE()
' NUM-Lock drücken
' Zunächst niederdrücken und dann wieder loslassen
keybd_event VK_NUMLOCK, 1, 0, 0
keybd_event VK_NUMLOCK, 1, KEYEVENTF_KEYUP, 0
End Sub

Sub NUM_aktiv() 'NUM-Lock aktivieren (falls deaktiviert)
If Not (GetKeyState(vbKeyNumlock) = 1) Then
keybd_event VK_NUMLOCK, 1, 0, 0
keybd_event VK_NUMLOCK, 1, KEYEVENTF_KEYUP, 0
End If
End Sub

Sub NUM_inaktiv() ' NUM-Lock deaktivieren (falls aktiviert)
If (GetKeyState(vbKeyNumlock) = 1) Then
keybd_event VK_NUMLOCK, 1, 0, 0
keybd_event VK_NUMLOCK, 1, KEYEVENTF_KEYUP, 0
End If
End Sub
------------------------------------------------------------------------------------
thanks for any help ..



"OssieMac" schrieb im Newsbeitrag
...
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