Make ActiveX Scrollbar background color transparent (or no fill)

[RincewindWIZZ]

I have a scrollbar whose values will be between 0 and 5
I would like to illustrate the value by having the background to the slider change colour - effectively 'fill up'
There seems no default way to do this

So I was going to make the slider cover 5 cells and set a conditional format for each cell to change colour.
But I cant find a way to make the background of the slider transparent

I've borrowed some code as follows

 Sub CtrlTollbx_Scrollbar()
    Dim oScr As MSForms.ScrollBar
    Dim objControl As Object

    Set objControl = Worksheets(1).Shapes("ScrollBar1").OLEFormat.Object
    Set oScr = objControl.Object
   

    oScr.Max = 5
    MsgBox "Minimum: " & oScr.Min  ' Max value is changed.
    oScr.BackColor = 16777215        'I found a post suggesting this made it transparent but it doesn't

    Set oScr = Nothing
    Set objControl = Nothing
End Sub

And somewhere else it suggested that there was a .Transparent property . . . but there isn't

So please how do I make the backcolor transparent?
TFAI

 

[Kyle123]

You don't really want to use ActiveX controls on worksheets, it's asking for trouble.

Since you always know how many values there will be, why not just use 2 buttons and display the cells below, or just use a label and increase the width/height as appropriate

 

[RincewindWIZZ]

Since you always know how many values there will be, why not just use 2 buttons and display the cells below, or just use a label and increase the width/height as appropriate

Well the intention was that
- the slider could be used with a finger and
- sliding is easier than tapping a button and
- the color underneath would provide visual feedback

But thank you, the idea sounds promising - I'll go have a play.

You don't really want to use ActiveX controls on worksheets, it's asking for trouble.

Why are activeX controls 'asking for trouble'?

 

[Kyle123]

They're very unstable on worksheets, people consistently have issues with them - particularly with resizing. IF you google "resizing controls on worksheets" you'll see what I mean - they're always best avoided, the forms controls don't have these issues

 

[RincewindWIZZ]

why not just use 2 buttons and display the cells below, or just use a label and increase the width/height as appropriate

OK so I see how two buttons would work but but. if I have understood your suggestion correctly, it will be very time consuming. So each 'slider' will be made up of two buttons each with a macro that increments or decrements a cell when clicked?

Problem is there are several hundred cells whose value can be changed in this way - though at any one time only about 20 or 30 will be available to be changed (exactly which values are visible depends on another parameter)

 

[Kyle123]

I’m afraid there isn’t really another answer. What you want to do isn’t possible, you can’t make part of the scroll bar transparent.

You could use a label instead of the cells, but you’d still have to increment manually. You can automate most of this so you don’t need to do it manually, but that depends on how proficient you are at coding.

Another option would be to look at redesigning your workbook so it doesn’t use this input method.

Finally, you aren’t expecting this to work on the mobile version of excel (android, iOS) or the web version are you? There’s no actives or macros. I’m sure you know, but I wanted to point it out before you got too far as you mentioned touch events.

NB you wouldn’t need a sub for each button, a single sub for each button would suffice

 

[RincewindWIZZ]

I’m afraid there isn’t really another answer. What you want to do isn’t possible, you can’t make part of the scroll bar transparent.

C'est la vie!

. . . . but that depends on how proficient you are at coding.

Not very . Certainly not enough!!

Another option would be to look at redesigning your workbook so it doesn’t use this input method.

Not an option.

NB you wouldn’t need a sub for each button, a single sub for each button would suffice

Then the button has to pass a parameter to tell sub which cells to increment? How do I do that?

Another option would be to look at redesigning your workbook so it doesn’t use this input method.

Sadly I am told this is not an option. More acceptable options are
- is just to show the value and not indicate it by any colour scheme
- have the cell with the value 'fill up' with colour

Finally, you aren’t expecting this to work on the mobile version of excel (android, iOS) or the web version are you? There’s no actives or macros. I’m sure you know, but I wanted to point it out before you got too far as you mentioned touch events.

No - but potentially on ipad

Seems best (nearest to requirement) option is a slider control and a data bar conditional format in the cell that has the number. Now to see if that works on an ipad!!

 

[Kyle123]

Erm... Good luck getting it working on an iPad!

 

[RincewindWIZZ]

Erm... Good luck getting it working on an iPad!

Indeed
But at least I now know what I am trying to make work!!

BTW seems the slider form does not hide and unhide with the row but the spin button does (I presume because move and size with cells is available?). Is there anyway to rotate the spin button through 90 degrees?