Can I make a horizontal scroll bar inside a userform represent a ratio split?

[RockandGrohl]

Hi all,

Let's say I have a counter of two different brands, JG and OM. I have a horizontal scroll bar on a userform which I want to show the split between the brand allocation.

If I have 300 allocations, and 200 are JG and 100 are OM, I would like the bar to look as follows:

[COLOR=#ff0000][FONT=monospace]███[/FONT][FONT=monospace]█[/FONT][FONT=monospace]█[/FONT][FONT=monospace]█[/FONT][FONT=monospace]█[/FONT][FONT=monospace]█[/FONT][FONT=monospace]████████[/FONT][/COLOR][FONT=monospace]█[/FONT][COLOR=#0000ff][FONT=monospace]█[/FONT][FONT=monospace]█[/FONT][FONT=monospace]█[/FONT][FONT=monospace]█[/FONT][FONT=monospace]█[/FONT][FONT=monospace]███[/FONT][/COLOR]

This would update gradually as the userform is refreshed. If I pick more OM tours, the blue portion of the bar would increase as the red portion decreases.

Is this, or something like it, even possible? I just want to give a visual representation of how the allocations are being spread out.

Thanks guys.

 

[MickG]

What you can do is overlay the "Scroll area" of the scroll bar with a textbox of the same width & height, Color the scrollbar "Red" and the textbox "Blue" then by running the following code the textbox length will change to suit your scroll position which is based on your "JG number in Textbox2

See Codes (adjust as necessary!!!

Private Sub ScrollBar1_Change()
ScrollBar1.BackColor = vbRed
TextBox1.BackColor = vbBlue
TextBox1.Width = ScrollBar1.Value / ScrollBar1.Max * (ScrollBar1.Width - 40)
End Sub

Private Sub TextBox2_Change()
Dim JG As Long
'NB:- Max allocations = 300
 
 If Not TextBox2.Value = "" And TextBox2.Value <= 300 Then
        JG = TextBox2.Value
         ScrollBar1.Value = JG / 300 * ScrollBar1.Max
End If
End Sub

[/CODE]

 

[RockandGrohl]

Whoa, that's a great idea. I'm about to go on Lunch but I'll try this in the afternoon. Thanks!

 

[MickG]

You're welcome

 

[RockandGrohl]

What you can do is overlay the "Scroll area" of the scroll bar with a textbox of the same width & height, Color the scrollbar "Red" and the textbox "Blue" then by running the following code the textbox length will change to suit your scroll position which is based on your "JG number in Textbox2

See Codes (adjust as necessary!!!

Private Sub ScrollBar1_Change()
ScrollBar1.BackColor = vbRed
TextBox1.BackColor = vbBlue
TextBox1.Width = ScrollBar1.Value / ScrollBar1.Max * (ScrollBar1.Width - 40)
End Sub

Private Sub TextBox2_Change()
Dim JG As Long
'NB:- Max allocations = 300
 
 If Not TextBox2.Value = "" And TextBox2.Value <= 300 Then
        JG = TextBox2.Value
         ScrollBar1.Value = JG / 300 * ScrollBar1.Max
End If
End Sub

[/CODE]

Mick, she works, code below, adapted and redacted:

Private Sub Slider_Change()Slider.BackColor = RGB(182, 0, 95)
OMCOL.BackColor = RGB(35, 47, 95)
OMCOL.Width = Slider.Value / Slider.Max * (Slider.Width - 40)


End Sub


Private Sub JGCount_Change()


Dim JG As Long
Dim OM As Long
Dim TotalJGOM As Long


c = Split((Columns(ActiveCell.Column).Address(, 0)), ":")(0)
TotalJGOM = Application.WorksheetFunction.CountIfs(Range(c & "4:" & c & "1300"), "JG") + Application.WorksheetFunction.CountIfs(Range(c & "4:" & c & "1300"), "OM")
If Not JGCount.Value = "" And JGCount.Value <= TotalJGOM Then
    JG = JGCount.Value
    Slider.Value = JG / TotalJGOM * Slider.Max
End If
End Sub

 

[RockandGrohl]

Ah, I had to change a bit because where there were no "G's or OM's it was erroring out.


Just thinking, can you control the length of textboxes from both the left and right side? Wouldn't this work with two textboxes overlaid?

 

[MickG]

By controlling the position on the control in from the left "Left property " and the Width "Width property" you are in face controlling the controls size from left and right.
That sound rather complicated when the "ScrollBar" and text box Size/position method, are controlled specifically by one value i.e. The Scrollbar value.
With all these thing its a lot of trial and error !!!!