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:
Code module used during software development to create and/or access the 'Faux SpinButtons':
Please let me know if you have any questions and/or problems and/or additional help.
Lewis
Bookmarks