Data Validation with Scroll Bar

[Taemex]

Using Excel 2003. Windows 7.

My Problem:

On my sheet, I have a cell that the user can increment with a scroll bar by 1-point at a time up or down, minimum of 0, maximum of 99, starts at 1. The scroll bar outputs to, lets say cell B1. Cell C1 has a dynamic number that cannot be exceeded. B1 has Data Validation that says that cell cannot be greater than cell C1, nor less than 0.

When the user types in the number in cell B1 manually, Data Validation works dandy, giving an error message if the number is greater than cell C1, however when the user uses the scroll bar to increment the number, data validation fails to give the error message.

I've spent about 1-hour on google trying to find an answer to this, but for some really strange reason, I seem to be the only person in the world to have discovered this problem... So unless there is a way to alter the way the scroll bar works, I'm pretty sure I have to do this with VBA.

BTW, I might be using a spinner instead of a scroll bar, but am having difficulty figuring out which one it is just by looking at it, and right-clicking it does not tell me what it is, nor does pressing F5 on it (oh how I miss my Visual Studio at times like this).

EDIT: Derp, it's a spinner. I opened it to edit its macro and it's called Spinner286. So I have 30 spinner's and plan on using this code with each one, problem is, I'm not sure how to address the spinner with the "With" command, i.e. do I call it SpinButton286, or Spinner286?

Sub Spinner286_Change()
With SpinButton1
If .Value > [c1] Then .Value = [c1]
End With
End Sub

[AlvaroSiza]

Best guess: The scroll bar is a control, the manipulation of which would likely need to be monitored (B1 as Target) via a worksheet_change event. Googleing worksheet_change events should give you enough to read through Christmas.

Cheers

Edit: Posted after OP edited. Likely of little use.

[Taemex]

Thanks for the reply. I just edited my response after finding out something. I think for the most part I figured it out but need a little more help nailing it before marking this resolved.

EDIT: alright I changed it to this:

Sub Spinner286_Change()
With Spinner286
If .Value > [aw210] Then .Value = [aw210] 'Error messege here, from IF to THEN...
End With
End Sub

But I get a debug error at the IF statement. No ideas why.

Just tried it multiple ways. Even if I put a solid number in, or add Spinner286 to value I get the error, i.e.:

If Spinner286.Value > 3 Then .Value = [aw210]

[Taemex]

Wow.... alright, I had to add a space between Spinner and 286...., but it would not work with WITH, so I had to competly alter my code as thus if anyone is interested:

Dim oSpinner As Shape

Set oSpinner = ActiveSheet.Shapes("Spinner 286")

oSpinner.ControlFormat.Max = [aw210]
oSpinner.ControlFormat.Min = 0