2 Numbers, 1 Spin Button

Okay so the background is a bit of the following, I'm trying to edit 1 number by an incriment value of 1, while another number is also edited using a backwards calculation dependant upon the value of the first number being edited. The problem here is two-fold: first the number in the second column doesn't change unless I click 'run', and secondly, the number it keeps returning is wrong (I think it's just doing it the once).

The code I'm using is the following:

Please Login or Register  to view this content.

The first number, and the number the spinner is currently tied to is "D3", the second number that is supposed to change based on that value is M4. I also want to eventually tie a restriction that M4 cannot be less than zero for changes to occur in D3, but that might take a bit longer.

The second code I have is trying to edit the minimum value of the spinbutton when the user clicks a checkbox (hence declaring they are done with this portion of the edit). The code I'm using is:

Please Login or Register  to view this content.

But it keeps returning 424 error. Any help with either of these things would be appreciated. Thanks. By the way.. if there is a list of functions that can be run in excel using VB that would be extremely helpful ^^

Hi Evalis,

Try this...

Please note that this is untested

Please Login or Register  to view this content.

...and this

Please Login or Register  to view this content.

It's ok...the code I gave you doesn't do what you want anyway. I did test the code below and it worked for me, with the exception of the fact that you didn't specify what you wanted to happen when the value in D3 is greater than 17 but less than 100.

Now, as for it not working when you press the spin button. Make sure that this code is placed in the class module for the worksheet in which the spin button is embedded and that you are not in design mode. These events will not be triggered if the code is placed in a standard code module

Updated Code:

Please Login or Register  to view this content.

Your not retarded. What I should have said was that any time you place a control (like a spinner or a textbox) onto a worksheet, the macros that you use to drive that control have to live on that worksheet's class module. Each sheet has it's own class module, and you can access the class module by going into VBA (hit Alt+F11) and double clicking on the sheet's name in the Project window, or by just double clicking on the control (your spinner).

If you want to attach an example workbook of your project, I'd be happy to take a look at it for you.

Ryan,

This syntax is not valid, and if it were, it would have gaps at the whole numbers:

Please Login or Register  to view this content.

shg you are quite right. I'm not sure why I didn't get a syntax error when I ran it, but I definitely should have caught the fact that the cell was not updating correctly.

Nevertheless...Evalis, I downloaded your workbook and the reason that your macros aren't working is because you are using the wrong kind of spinner. There are two types of controls that you can put onto a worksheet: Forms Controls and ActiveX controls. You can go into Excel's help documentation for a pretty good explanation of the difference between the two.

I think there may be a non-VBA solution here. I'm going to take your workbook home and play with it for a bit, free of distraction, so that I don't post any more junk solutions for you. If someone comes up with a suitable fix for you in the meantime, then good on them. I will still post a solid solution for you to ease my karma after leading you in the wrong direction earlier.

Best of luck with your project!