Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scrollbar with variable maximum and minimum
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scrollbar with variable maximum and minimum
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 On Feb 7, 5:23 am, "Robert" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scrollbar with variable maximum and minimum
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scrollbar with variable maximum and minimum
On Feb 7, 7:18 am, "Robert" wrote:
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 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scrollbar with variable maximum and minimum
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scrollbar with variable maximum and minimum
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 -- Regards, Tom Ogilvy "Robert" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maximum and Minimum of a cell that changes | Excel Discussion (Misc queries) | |||
maximum/minimum value in formula | Excel Discussion (Misc queries) | |||
Spinners - minimum and maximum | Excel Discussion (Misc queries) | |||
Recording maximum and minimum | Excel Programming | |||
Maximum and minimum cell value | Excel Discussion (Misc queries) |