Undo a scroll action

[joeeng]

Is there a way to undo a scroll action by a scrollbar on a userform? If I
try to manually reset scrollbar.value from within the Scrollbar_Change event
procedure, it triggers the scrollbar_change again, and reverts to the
scrolled scrollbar value upon completion of the stacked change event because
there form events cannot be disabled. I have tried the BeforeUpdate event,
which triggered as expected but either I don't know how to use the Cancel or
it does not work even though Microsoft Help says it applies to scrollbars.
The userform UndoAction only seems to undo text changes in the userform.

 

[Dave Peterson]

Maybe you can use a variable that serves as a go/no-go indicator:

'At the top of the module, but not in any procedure
Dim BlkProc as boolean

'in your procedure:
Private sub ...

if blkproc = true then exit sub

....other stuff

'change the value

blkproc = true
'do the change
blkproc = false

'do more stuff
End Sub

But if you don't want the scrollbar to be changed, why design the form to have a
scrollbar?

 

[joeeng]

I was trying to use a validation check (i.e., Are you sure you want to scroll
before saving current data?, vbYesNoCancel & vbQuestion) within the
Scrollbar_Change procedure and, if the user chooses Cancel, the form and
scrollbar revert back to the condition before the Scrollbar_Scroll action.
The problem, even with your suggestion, is that the Scrollbar change event is
always triggered upon unclicking the scrollbar and the Scrollbar change event
for the scroll change action is always the last to execute which negates the
manual change of the scrollbar position when Cancel is requested from within
the Scrollbar_Change procedure. It seems to be a hieracrchical thing; the
manual Scroll change completes its event procedure before the scroll action
change does because the manual change is called from within the scroll change
procedure.

 

[Jim Thomlinson]

Not sure if this helps but...

http://www.cpearson.com/excel/SuppressChangeInForms.htm

 

[Dave Peterson]

I made a small userform with a scrollbar and a label.

This seemed to work ok for me:

Option Explicit
Dim BlkProc As Boolean
Dim ScrollBar1Pos As Double
Private Sub ScrollBar1_Change()
Dim Resp As Long
If BlkProc = True Then Exit Sub

Resp = MsgBox(Prompt:="Are you sure", Buttons:=vbYesNo)

If Resp = vbYes Then
ScrollBar1Pos = Me.ScrollBar1.Value
Else
BlkProc = True
Me.ScrollBar1.Value = ScrollBar1Pos
BlkProc = False
End If
Me.Label1.Caption = Me.ScrollBar1.Value
End Sub
Private Sub UserForm_Initialize()
With Me.ScrollBar1
.Min = 0
.Max = 100
.LargeChange = 10
.SmallChange = 1
.Value = 0
ScrollBar1Pos = .Value
End With
Me.Label1.Caption = Me.ScrollBar1.Value
End Sub

 

[joeeng]

Dave,

After trying this again, it does not work. After ScrollBar1.change executes
with the scrollbar1.value changed to the starting value, it executes one more
time with blkproc=false and with the scrollBar1 ending value, probably pulled
from the stack, negating the change.

 

[Dave Peterson]

Sorry it didn't work for you.

I'm out of suggestions.

Dave,

After trying this again, it does not work. After ScrollBar1.change executes
with the scrollbar1.value changed to the starting value, it executes one more
time with blkproc=false and with the scrollBar1 ending value, probably pulled
from the stack, negating the change.