Is it possible to read a form button location or cell link?

[ifwhitfi]

I am working on a large spreadsheet in which I have 200 form buttons (spin buttons) that can be used to change the value of the cell next to each individual button. I want to have a macro run on the values in the same row as the button and the cell that the button is linked to. Instead of creating 200 individual macros I would like to have one macro that reads either the row of the button or the row of the cell that the button is linked to and then runs a function on that row only. There is a simplified example attached for clarification. Any help will be greatly appreciated.

Example.xls

[rvasquez]

Are the spin buttons on your worksheet form controls or ActiveX controls. The ones you have on your example worksheet are form controls which are a little harder to deal with.

[ifwhitfi]

Form controls currently, if ActiveX controls are required I could change this though

[rvasquez]

Hello there,

I believe the following code will work for you, assuming that your data is like your example spreadsheet, where your data with spin buttons starts at row 2 and continues until the Last row of data, A contains the first number, B contains the number you want to change with the spin button, C is where the spin button is going to be placed and D is the formula A+B. Finally, the row height of the rows is 15 that contain a spin button is 15.

Please Login or Register  to view this content.

To use this code in your workbook:

1. Select and delete the Spin Buttons you have on your worksheet in column C
2. Press Alt + F8
3. Clear the macro name field and then type AddSpin
4. Select the Create option
5. In between the Sub AddSpin() and End Sub copy and paste the above code
6. Anything that appears in green is a comment meant to help you understand the code
7. Close out of Visual Basic
8. Press Alt+F8
9. Select the AddSpin macro
10. Select the Run option

This code should add activex spin buttons whose linked cell is in the current row that the spinbutton is.

Let me know if this works for you.

Attached is your original file for testing.

Press Alt+F8, Select Macro1 and then select Run to watch it work.

Thanks!

[ifwhitfi]

rvasquez,

Thank you for the prompt assistance. The code worked beautifully for the example spreadsheet. Will start working on incorporating it into the larger one now.

Much obliged

[rvasquez]

No problem, please don't forget to mark the thread solved and if I helped maybe give a little star tap.

Thanks!