Changing spinbutton colors independantly or creating a custom one.

[AstroZelda]

Hello,

I am currently working for a group of person, and I figured that using spinbuttons could be a great way to manipulate my sheet.
However, despite having up and down arrows, they asked me if I could change the colors of the two buttons independently.

I know I can change the color of both if it is an ActiveX control, but then the two buttons are of the same color again...

I would like to know if I can do this with the object (and how), or if I have to make a custom spinbutton control (and -again- how).

Here is what I ideally would like to obtain :
http://i.imgur.com/u1RWbiO.png

Thank you !

Fran蔞is

[LJMetzger]

Hi Fran蔞is,

See the attached file which creates Faux two independent grouped shapes that look like your picture.

The following instructions for creating the shapes are included in the file:
a. Create a Forms CommandButton or any Shape of your Choice. Remove text from the Shape if needed.
b. Create an AutoShape Triangle. Fill the triangle with the appropriate color. Rotate the triangle if needed.
c. Place the triangle inside the CommandButton. Change the shape sizes as required.
d. Right Click the First Shape and Select Order > Send to Back. This will make sure the Triangle is always visible.
e. Left Click the First Shape. Press the CTRL key and Left Click the Triangle.
f. Right Click the Green Circle on the Triangle and Select Grouping > Group.
g. Rename the Grouped Shape to 'Group1SpinUp' or any name of your choice.
To rename the grouped shape, 'Right Click' the grouped shape. Press ALT F8 to make the Macro menu visible. 'Double Click' on 'RenameActiveShape'.
h. 'Right Click' the Grouped Shape and Select 'Assign Macro'. 'Double Click' on Group1SpinUpEventHandler or any existing macro of your choice.

i. Create 'Group1SpinDown' Shape using macro 'Group1SpinDownEventHandler' in a similar manner.

This should help you get started.
If you need additional help, more information is needed such as:
a. The number of SpinButtons you need. If you have a lot of SpinButtons, the event handler code can be made that only one Event Handler is needed to process any SpinButton event.
b. What the SpinButtons are supposed to do (codewise).

Lewis

[6StringJazzer]

One way to do this is to create two images that look just like the spin buttons, then associate each with a macro. This is basically building your own spin button from scratch. See attached. You can probably do a better quality graphic.

[AstroZelda]

Thank you for your help, that's very useful and extremely interesting !
I have like 35-40 spinbuttons in my worksheet, and I use them to increment or decrement values (more precisely, to set the number of defaults and errors on a production line)

[LJMetzger]

Hi Fran蔞is,

Thanks for the rep points.

If you upload a sample workbook that contains a few SpinButtons, and describe what each one is supposed to do, I might be able to show you how to manipulate the data with a minimum of code.

Lewis

[AstroZelda]

Hey Lewis,

Here you go !
I did this real quick, showing what the spinbuttons are used for.

spinSample.xlsm

It's a simple file, and the code is probably going to be simple too, but keep in mind that in my real file I have
a SHEETLOAD of spinbuttons

Thanks !

Fran蔞is

[LJMetzger]

Hi,

See the attached file which should be able to handle your large amount of SpinButtons.

How the Faux SpinButtons were created:
a. Create a Forms CommandButton or any Shape of your Choice. Remove text from the Shape if needed.
b. Create an AutoShape Triangle. Fill the triangle with the appropriate color. Rotate the triangle if needed.
c. Place the triangle inside the CommandButton. Change the shape sizes as required.
d. Right Click the First Shape and Select Order > Send to Back. This will make sure the Triangle is always visible.
e. Left Click the First Shape. Press the CTRL key and Left Click the Triangle.
f. Right Click the Green Circle on the Triangle and Select Grouping > Group.

To rename a grouped shape, 'Right Click' the grouped shape. Press ALT F8 to make the Macro menu visible. 'Double Click' on 'RenameActiveShape'.


Instructions for Creating Several Faux SpinButtons.
a. On Sheet 'Main', Create your cell structure such as exists on Row 3.
All cells should be the same size as the cells on row 2.
b. On Sheet 'Main', Create One MASTER Up Spin Button Group, and One Master Down SpinButton Group.
c. Put the Pair of Faux SpinButtons in Cell 'D2'.
d. Rename the UP Faux SpinButton In Cell 'D2' as 'FauxSpinUpD2' (using macro 'RenameActiveShape()'.
e. Rename the DOWN Faux SpinButton In Cell 'D2' as 'FauxSpinDownD2'.

f. At the top of Module 'ModShapeAsSpinButton', adjust the following items as required:
Public Const sFauxSpinButtonSheetName = "Main"
Public Const sFauxSpinButtonCells = "D2:D5"
Private Const nMinSpinButtonVALUE As Long = 0
Private Const nMaxSpinButtonVALUE As Long = 30000

g. Run macro CreateFauxSpinButtons() to create the 'Faux SpinButtons' (automatically runs the Delete routine).
h. Save the workbook. Done


How the Software Works:
a. Each 'Faux SpinButton' contains a 'Group Name' (e.g. FauxSpinUpD3') and individual 'Shape Names' (e.g. FauxSpinUpD3B')
that contain the Cell Address for the 'Faux SpinButton' (e.g. 'D3').
b. When an Up 'Faux SpinButton' is clicked, Event Handler FauxSpinUpEventHandler() is called.
c. When a Down 'Faux SpinButton' is clicked, Event Handler FauxSpinDownEventHandler() is called.
d. The Event Handler then calls ProcessFauxSpinButtonEvent() which will either increment or decrement the value in the cell to the left of the 'Faux SpinButton'.

Code module that process each 'Faux SpinButton' mouse click:

Please Login or Register  to view this content.

Code module used during software development to create and/or access the 'Faux SpinButtons':

Please Login or Register  to view this content.

Please let me know if you have any questions and/or problems and/or additional help.

Lewis

[AstroZelda]

OH WAW . This is highly accurate.

You now are a god to me

This is efficient, good looking, quick and detailed. Thank you a lot, you truly deserve your expert status !

Fran蔞is