Spin button event handling

[d3hartm2]

Hello,

First, I apologize if this has been asked and solved before. I did a quick search, but couldn't find anything similar to what I'm asking, and I've spent many hours looking through various pages Google has told me were relevant (and most of the time, weren't relevant at all).

The issue I'm having is with the ActiveX Spin Button (in 2007, formerly in the Control toolbox in 2003). I need the Spin Button to call a certain macro whenever it is clicked up or down. I know how to do this with one spin button, the problem is that the workbook I'm creating could potentially have dozens (or even hundreds) of these spin buttons that all need to call the same macro. It doesn't seem feasible to have to create separate Change event functions for each of these.

My company used to use the simpler form controls for this process, but a situation has come up where we need the ability to make the buttons invisible, or at least appear disabled, and that doesn't seem doable with the form controls.

So my question is, is there a way to specify in my workbook that whenever a spin button is clicked, this particular macro is to be called? Or even, whenever an ActiveX object is clicked, call the macro, because the spin buttons will be the only ActiveX objects in the workbook. I've read a bit about how to create global event handlers for worksheets and workbooks, but I can't find anything related to spin buttons specifically.

Any help is appreciated, even if it's just to tell me that it can't be done. Thanks very much!

[Andy Pope]

You need to build an event handler. This can be done using a class object and collection.

code for a class module called CSpnEvt

Please Login or Register  to view this content.

in a standard code module use

Please Login or Register  to view this content.

And to load collection use this in the workbook open event

Please Login or Register  to view this content.

[d3hartm2]

Hi Andy,

Thank you very much for your speedy response. I am having some difficulty, however. I pasted the code in where you said to, re-opened the workbook, and nothing happened. I tried stepping through the code, and got an error message saying that CSpnEvt is not a defined type. The exact details of what's going on with this code are a bit beyond me as well.

Again, thanks for your help, sorry to trouble you with follow-ups.

[Andy Pope]

Here is the test file I created.

[d3hartm2]

Ah! I didn't have my Class module named correctly. I had left it as the default. Thanks again! This has been a tremendous help to me

[d3hartm2]

Sorry for the follow-up, but I've been messing around with the spin buttons this morning and have encountered a new problem. How can I get the spin buttons to have all their normal functionality (min and max values linked to a cell on the worksheet), as well as the additional functionality I want it to have (calling a macro to say paste a certain phrase into a cell, though the actual macro it would be calling is much more complicated)?

Ideally, I would like it to be set up so that when the workbook is opened, the macro I would like it to call is added to the event handler, and then afterwards, I can adjust the linked cell, min and max values, visibilty, etc.

Thanks in advance.

[Andy Pope]

The Min/Max properties can not be linked directly to cells.

You could have code that reads a cell and applies that value.
This code even be done in the Worksheet change event to automatically update the control when the cell changes, as long as you can identify which control to change when the cell changes.

This sets the min/max values with creating event handlers.

[d3hartm2]

Thanks Andy. I guess I didn't state that very clearly. What you sent last week worked perfectly, the problem is that when I try to change the LinkedCell property, the macro being called by the event handler that was set up no longer works. The situation is this:

The page will have several spin buttons set up. Each of these spin buttons needs to call the same macro every time it is clicked. The spin button will also be linked to another worksheet, and the min and max values will be read in by VBA from that sheet. The linked cell and the min and the max will only need to be set up once, and after that will remain static.

So I was hoping there was a way to, once the workbook is open, just run a macro that would select each spin button on the page, link it to another sheet, and set the min and max values. Hopefully that makes sense.

[Andy Pope]

Did you try the latest attachment.
That's waht it does, sents the min/max when the workbook is first opened.

[d3hartm2]

I did try it. I was thinking that ideally I'd like to have in a separate macro that can be run after the workbook is open, because on different sheets, the cells that it's supposed to be reading the min and max values from might change. Is there any way to loop through the spinners after the workbook is open, run some code to set the linked cell, min, and max, without altering the change event that was previously set up? Everytime I change the linked cell, the change event no longer works.

[Andy Pope]

I have added some more properties to the class to allow you set read and write the Value, Min and Max.

[d3hartm2]

Perfect! Thanks again Andy for all your help with this. This should work just fine for what I'm doing.

[jjlao]

Andy, I saw this and found this extremely useful. I was trying to recreate this and am trying to workout where along the code does the spinner reference the cell to its immediate left? I.e. if i copy and paste a spinner, that spinner will change the cell I copied the respective spinner. If I add a new spinner, it doesn't change any cells. Thanks.

Jeremy