ScrollBar Sheet problem

[daniel.hurst]

Hi,


Following procedures
-------------------------------------------------------------
Private Sub ScrollBar1_Change()

Range("A1").Value = ScrollBar1.Value

End Sub
-------------------------------------------------------------
Private Sub ScrollBar1_Scroll()

Range("A1").Value = ScrollBar1.Value

End Sub
-------------------------------------------------------------
for ScrollBar1 (with parameters Min=0, Max=10) on sheet change values
in cell A1 from 0 to 10 using step 1. How to write procedures that
change in cell A1 value from 1,00 to 10,00 using step 0,01, but without
indirect cell (like in example in John Walkenbach's book)? And second
problem. When I set in cell A1 new value and click scrollbar arrow,
value in cell changes from last 'remembered' by scrollbar value not
from new value. How to resolve such problem?

Best wishes

Dan

 

[Tom Ogilvy]

change the max to 900 and keep the min at zero, then use

Private Sub ScrollBar1_Change()
Range("A1").Value = ScrollBar1.Value / 100 + 1
End Sub

Don't use any other events.

 

[daniel.hurst]

Tom,

Great thanks. There is still problem when I set value to the cell and
push arrow, scrollbar start from old remebered one.

Regards
Dan

 

[Tom Ogilvy]

'this line must be at the top of the module
Public bBlockEvents As Boolean

Private Sub ScrollBar1_Change()
If bBlockEvents Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
Range("A1").Value = ScrollBar1.Value / 100 + 1
ErrHandler:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal target As Range)
On Error GoTo ErrHandler
If target.Address = "$A$1" Then
Select Case target.Value
Case 1 To 10
bBlockEvents = True
ScrollBar1.Value = (Range("A1").Value - 1) * 100
Case Else
Application.EnableEvents = False
target.Value = ScrollBar1.Value / 100 + 1
End Select
End If
ErrHandler:
bBlockEvents = False
Application.EnableEvents = True
End Sub


If some events stop working it is probably because application.EnableEvents
has remained set to false or bBlockEvents has been set to True. If you can't
fix them any other way, close and reopen Excel. I wouldn't expect this to
happen unless you are altering/editing and testing code.

 

[daniel.hurst]

Tom,

thanks for help. How you certainly observed, I'm beginner in Excel
object model, so some of my quastions are rather trivial.

Regards

Dan