linking the max and min values to a scrollbar 2

[sullivandavid]

Can one with VB code set the max and min properties of a scrollbar to a cell ?

ie max= a1
min= b1

Can one set the scroll bar color and/or the knob (slider)
in VB to change relative to a cell value or value in excel

ie last > 100 Blue or .80*(a1)
last < 40 Red or 1.20 (b1)


Thanks in advance

 

[SkipVought]

Hi,

You can specify the min/max values in a cell and then upon initialization, reference those values in code. But you can't in the Properties window -- I wish you could!

Private Sub UserForm_Activate()
  With ScrollBar1
    .Max = Sheet1.[A1]
    .Min = Sheet1.[A2]
  End With
End Sub

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

Hi,

oops! You said COLOR!

Private Sub ScrollBar1_Change()
    With ScrollBar1
        Select Case .Value
            Case Is > 0.8 * .Max
                nColor = vbBlue
            Case Is < 1.2 * .Min
                nColor = vbRed
            Case Else
                nColor = vbGreen
        End Select
        .BackColor = nColor
    End With
End Sub

Private Sub UserForm_Activate()
  With ScrollBar1
    .Max = Sheet1.[A1]
    .Min = Sheet1.[A2]
  End With
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[sullivandavid]

Skip
Very usefully , scroll with forms works great
ie
ActiveSheet.Shapes(&quot;Scroll Bar 1&quot.Select
With Selection
.Value = 200
.Min = 100
.Max = 1000
.SmallChange = 1
.LargeChange = 10
.LinkedCell = &quot;$C$1&quot;
.Display3DShading = True
however object doesn't support property or method using vb scrollbar will test your color code for forms

 

[SkipVought]

I don't know what you're saying. I can put a scrollbar on a Userform OR on a sheet and it works. The only difference is that with the userform, the activate event initializes the min/max values. you have to use some other event to initialize on a sheet, like the workbook_open event or the worksheet_activate event (assuming that it gets activated)

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[sullivandavid]

Thanks I think I got it
dbs

 

[VBAjedi]

sullivandavid,

<whisper>
Skip very much deserves a star for his excellent assistance! It's the least we can do around here. . .
</whisper>


VBAjedi