Assigning Settings to a Scrollbar (Form Control)

J

JP777

New Member
Joined
Apr 8, 2012
Messages
24
I know re-posting from another forum is discouraged, but I am in a crunch for some help, and no one has responded all day on the other forum.
I am trying to assign certain max, min, incremental settings, and cell link to a scrollbar I have put on a worksheet. As this scrollbar is clicked, it should step through time at certain steps, and the cells on that same work sheet, as well as a chart, will be updated with each click of the scrollbar. I tried the below code but got an error every time I clicked the scrollbar saying "Object variable or With block variable not set":

Code: 
Sub PhasorControl()     
Dim Bar As ScrollBar     
With Sheets("Sheet3")         
Bar = Sheet3.ScrollBars("Scroll Bar 1")         
Bar.Max = Sheet1.Range("D6").Value         
Bar.Min = Sheet2.Range("A2").Value     
End With 
End Sub
I have also tried this, but got absolutely nothing:
Code: 
Sub PhasorControl()     
Sheet3.Shapes("Scroll Bar 1").ControlFormat.Min = Sheet2.Range("A2").Value     
Sheet3.Shapes("Scroll Bar 1").ControlFormat.Max = Sheet1.Range("D6").Value 
End Sub
Any suggestions on getting the scrollbar settings assigned? Ideally, I would like to be able to have it start at a value in cell A2 and each increment would be the value of the next cell down (A3, A4, A5, etc.) until the max is reached (A?? - amount of data in column A varies).
Since this forum doesn't allow attachments, let me know if you need the sample spreadsheet, let me know and perhaps I can email it. Thanks in advance for your help!

to be compliant with the rules, here is the link to this same question on the other forum:
http://www.excelforum.com/excel-pro...ing-settings-to-a-scrollbar-form-control.html
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
A scroll bar is an object. To assign a variable to a scroll bar, one needs the keyword Set

Code: 
Set Bar = Sheet3.ScrollBars("Scroll Bar 1")
Also, when working with Forms controls, the ControlFormat object has to be accessed.

Code: 
With Worksheets("Sheet1").Shapes("Scroll Bar 1")
    With .ControlFormat
        .Max = Sheet1.Range("A1").Value
        .Min = Sheet1.Range("A2").Value
    End With
End With
P.S. I had a bear of a time with this test code until I realized that the cell entries had max and min swapped. For production, I'd add a test that the two cells have numeric values and that the Max is > the Min.
 
Upvote 0
Cross posting is okay as long as you notify which you did here. Well done. You should notify in the other forum

Try using the Set command...

Set Bar = Sheet3.ScrollBars("Scroll Bar 1")
 
Last edited:
Upvote 0
Thanks to the both of you for the input. I am still having problems though. Not quite sure where I'm going wrong...after I made the changes, when I go to the worksheet and try to operate the scrollbar, I get this error message:

"object doesn't support this property or method"

Here is my code as of right now:
Code: 
Sub PhasorControl()
    Dim Bar As ScrollBar
    Dim Fract, Freq, minval
    
    'determine which value should be minimum
    If Sheet2.Range("A2").Value = 0.0625 Then
        minval = Sheet2.Range("A6").Value
    Else
        minval = Sheet2.Range("A3").Value
    End If
    
    Fract = Sheet1.Range("B6").Value
    Freq = Sheet1.Range("D6").Value
    Set Bar = Sheet3.ScrollBars("Scroll Bar 1")
    
    With Worksheets("Sheet3").Shapes("Scroll Bar 1")
        With .ControlFormat
            .Max = Freq
            .Min = minval
            .SmallChange = 1 / Fract
            .LargeChange = 4 / Fract
            .ControlSource = Sheet3.Range("B17")
        End With
    End With
End Sub
Were you able to access the sample spreadsheet on the other forum? If you can, hopefully that can help. I'm drawing a blank here....thanks again for your help!
 
Upvote 0
  1. Relocate your PhasorControl procedure in a standard module. Not in the worksheet code module. Then reassign the that procedure to the scrollbar.
  2. Remove the line .ControlSource = Sheet3.Range("B17"). The .ControlSource property is used with an ActiveX-type scrollbar. You are using a Form Control-type scrollbar. Overview of forms, Form controls, and ActiveX controls on a worksheet. You have already Linked the scrollbar to Sheet3.Range("B17"). You wouldn't need to re-link it in code.
 
Upvote 0
AlphaFrog,
thanks for the feedback. I did what you said, but the scrollbar still doesn't work. it's almost like it doesn't like some of my settings because when I go back to look at it's properties, it has set both increments to 0, and the minimum is 0 (should be a decimal number starting with either 0.0625 or 0.25). the only setting it seemed to take was the max limit. I deleted the scrollbar, added a new one, and updated the code for its name, and the same thing happened. Any ideas? I figure I can create a userform with a scrollbar that controls that worksheet, but I already have several userforms in my program and wanted to make this portion a little easier to deal with by having the scrollbar in the worksheet...
I thought about using an ActiveX scrollbar, but from what I've read, you can't assign specific macros to it. thoughts??
 
Last edited:
Upvote 0
I attempted an ActiveX scrollbar, and came up with the same problem: the min and both increments change to 0, but the max accepts my code. Here is my code for the ActiveX scrollbar, in the Sheet3 module:
Code: 
Private Sub ScrollBar1_Change()
    Dim Bar As OLEObject
    Dim Fract, Freq, minval, incrsmall, incrlarge
    
    'determine which value should be minimum
    If Sheet2.Range("A2").Value = 0.0625 Then
        minval = Sheet2.Range("A6").Value
    Else
        minval = Sheet2.Range("A3").Value
    End If

    Fract = Sheet1.Range("B6").Value
    Freq = Sheet1.Range("D6").Value
    incrsmall = 1 / Fract
    incrlarge = 4 / Fract
    
    Set Bar = Sheet3.OLEObjects("ScrollBar1")
    With Bar
        .Object.Max = Freq
        .Object.Min = minval
        .Object.SmallChange = incrsmall
        .Object.LargeChange = incrlarge
    End With
End Sub

I would prefer to use the form control in case a Mac user needs to use my program, but if I can get the ActiveX working and not the form control, then it will do for now...Thanks in advance for everyone's help!
 
Upvote 0
fyi, $10 via PayPal to the person who can help me figure out where I'm going wrong....just private message me the email address on your PayPal account...
 
Upvote 0
The scrollbar can only use integer values.

Try something like this...
Code: 
Sub PhasorControl()

    Dim Bar As ScrollBar
    Dim Fract As Double, Freq As Double
    
    Set Bar = Sheet3.ScrollBars("Scroll Bar 1")
    Fract = Sheet1.Range("B6").Value
    Freq = Sheet1.Range("D6").Value
    
    With Bar
        .Max = Freq * 1000
        .Min = Sheet2.Range("A2").Value * 1000
        .SmallChange = 1000 / Fract
        .LargeChange = 4000 / Fract
    End With
    
End Sub

The result value in the linked cell Sheet3.Range("B17") will be 1000 time larger than you want. Put this formula in an adjacent cell...
=B17/1000
...or divide the B17 value by 1000 for any formula that references it.

Also, you only would need to run this code when you want to define the limits of the scrollbar. It doesn't necessarily need to be assigned to the scrollbar to run every time you click on it.
 
Last edited:
Upvote 0
alphafrog,
this seems to be working. However, the calculations don't give the exact numbers from column A on sheet2. I had a suggestion about indexing for B17 that seems to be a good idea:
Code: 
=INDEX(Sheet2!$A$2:$A$241,$B$18)
where B18 is the cell that is linked to the scrollbar. The only problem is that the number of rows in column A could vary. Is there a way to use INDEX and code/formulate it so that it can handle a variable amount of rows?
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Catching the Mouse Click Error in Modal User Form
Replies
4
Views
381
huiettcm
H
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
506
AndyEd
A
D
  • Question
Developer form controls not working
Replies
0
Views
769
DogsbodyBoy
D
A
  • Solved
Calendar issue
Replies
1
Views
1K
AndyEd
A
L
  • Question
VBA works on work PC but not home PC
Replies
3
Views
2K
rollis13
rollis13

Forum statistics

Threads
1,216,126
Messages
6,129,021
Members
449,480
Latest member
yesitisasport

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top