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

RockandGrohl

Active Member
Joined
Aug 1, 2018
Messages
384
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:


Code:
[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.
 
Last edited:

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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!!!
Code:
Private Sub ScrollBar1_Change()
ScrollBar1.BackColor = vbRed
TextBox1.BackColor = vbBlue
TextBox1.Width = ScrollBar1.Value / ScrollBar1.Max * (ScrollBar1.Width - 40)
End Sub
Code:
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

Active Member
Joined
Aug 1, 2018
Messages
384
Whoa, that's a great idea. I'm about to go on Lunch but I'll try this in the afternoon. Thanks!
 

RockandGrohl

Active Member
Joined
Aug 1, 2018
Messages
384
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!!!
Code:
Private Sub ScrollBar1_Change()
ScrollBar1.BackColor = vbRed
TextBox1.BackColor = vbBlue
TextBox1.Width = ScrollBar1.Value / ScrollBar1.Max * (ScrollBar1.Width - 40)
End Sub
Code:
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:

Code:
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

Active Member
Joined
Aug 1, 2018
Messages
384
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

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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 !!!!
 

Forum statistics

Threads
1,081,994
Messages
5,362,609
Members
400,684
Latest member
Vie

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top