Scrollbar with variable maximum and minimum

[Robert]

I would like to create a scrollbar which is linked tocells holding the
edge conditions (max & min). I did find some topics regarding this
subject, but none described exactly the problem I am experiencing.

I am new to writing VBcode but eager to learn. So far I came up with
the following code:

Private Sub ScrollBar1_Change()

Dim Top As Integer
Dim Dal As Integer

Top = Cells(3, "A").Value
Dal = Cells(2, "A").Value

With ScrollBar1
Dim Rng As Range
.Max = Top
.Min = Dal

Range("D1").Value = ScrollBar1.Value
End Sub

Is there anyone which is higher skilled in VBA as I am not (yet). What
am I missing.

 

[John Coleman]

try

Private Sub ScrollBar1_Change()
ScrollBar1.Min = Range("A2").Value
ScrollBar1.Max = Range("A3").Value
End Sub


In your code:
1) You lack an end with to terminate the with block (but why use that
construct to set or invoke just 3 properties?)
2) You never use Rng - so why declare it?
3) If D1 is the linked cell (linked by setting the property manually
when you made thescroll bar) then the line setting its value is not
needed - though it wouldn't hurt
4) A stylistic point: Range("A1") or Cells(1,1) are more idiomatic
then Cells(1,"A")

I hope you enjoy learning Excel VBA - its a fun language.

HTH

-John Coleman

 

[Robert]

Many thanks for the fast and good reply.

It really helped me. (didn´t know it was so easy).

Is there also a line of code which makes it possible to vary the
increment stepsize.

How do you know which parameters can be changed for let´s say this
scrollbar. Is there some sort of handbook and/or website containing
this?

 

[John Coleman]

Note that when you type Scrollbar1. (note the dot) in the IDE a drop-
down list of properties/methods appear. You can change any of them.
These should include all of the properties you see when, in design
mode, you right-click on the control and select properties. There
doesn't seem to be an increment property per se, but there is both a
smallchange and a largechange property, so it sounds like that it is
one of them you want to change:

Scrollbar1.SmallChange = whatever

The online help is often helpful. Turning on the macro recorder while
you play with a control then inspecting the code is often a helpful
experiment. This newsgroup is also a good source of information
(though due to the high volume you might not always get an answer, in
which case try again with a possibly more focused question in a day or
so).

Sooner or later you probably need a book on Excel Programming. I would
recommend "Excel VBA Programming for Dummies" by John Walkenbach (I
never liked the *name* of the "For Dummies" books - it makes them hard
to recommend without seeming condescending - but the books themselves
are often quite good and the Excel 97 version of this book was where I
learned VBA programming. All of his books are worthwhile).

Hope that helps,

John Coleman

 

[Robert]

I just buyed some e-books containing crash courses for dummies. I hope
it will be helpfull.

When changing the small- & largechange like you said Excel rounds it
to the value zero for some reason (the smallchange is 0,0001) . I
guess I should declare something about now? I am determined to fix
this problem! Trial and error goes a long way.

Many thanks, your tips are very helpful to me.

ScrollBar1.Min = Range("C2").Value
ScrollBar1.Max = Range("C3").Value
ScrollBar1.SmallChange = Range("C4").Value
ScrollBar1.LargeChange = Range("C4").Value * 2

Robert

 

[Tom Ogilvy]

scrollbar changes are whole numbers, so you can not use a decimal value.

You should scale the value of the scrollbar to achive your objectives

results = Scrollbar1.Value/1000