Click and hold event on a form control spinner breaks my code

[mansfin]

Hi, this is my first post! Please let me know if I'm doing it wrong

So I have 5 form control spinners on a worksheet linked to cell values. The 5 cells are also linked such that if one of the values is increased or decreased, the other 4 also increase/decrease by some amount. I assigned a macro to the spinners so that if the user toggles up one of the spinners and the rounded cell value is greater than 10.1, then the spinner automatically toggles itself back down. For example,

If RowNumber = 27 And (WorksheetFunction.Round(Sheets(ImpactsSheet).Range("AH" & RowNumber) - 0.1, 1) >= 10 Or _
WorksheetFunction.Round(Sheets(ImpactsSheet).Range("AH28") - 0.1, 1) >= 10 Or _
WorksheetFunction.Round(Sheets(ImpactsSheet).Range("AH29") - 0.1, 1) >= 10 Or _
WorksheetFunction.Round(Sheets(ImpactsSheet).Range("AH30") - 0.1, 1) >= 10 Or _
WorksheetFunction.Round(Sheets(ImpactsSheet).Range("AH31") - 0.1, 1) >= 10) Then
      InvalidValue = Selection.Value
      Selection.Value = InvalidValue - 1
End If

Selection.Value is the value of the cell that the spinner is linked to, which was referenced using Application.Caller

This works fine if the user single clicks the toggles. However, if the user clicks and holds the toggle, then the linked cell values get increased before the macro that's assigned to the spinner can automatically adjust the value back down.

Is there any way to disable the click and hold event on a form control spinner? Or is there some code I can write that will allow the spinner to run through the assigned macro for each increment when the user clicks and holds the spinner? Thanks a bunch!

[Leith Ross]

Hello mansfin,

Welcome to the Forum!

With everything linked together when the values change, what did you expect to happen?

If you explain what you want to achieve then a workable solution can probably be found.

[Tsjallie]

Hard to say what's happening. Can we take a look at your workbook?

[mansfin]

I should have explained that if the user increments one of the spinners up and the one of the cell values rounded to to the nearest tenth is equal to 10, then all of the cell values increase from the click of the spinner. After that happens, the code from the macro that's attached to the spinner executes and automatically adjusts the spinner down one increment, so the user ends right where he/she started before clicking the spinner.

However, when the user clicks and holds the spinner, the linked cell increments multiple times before the macro that is attached to the spinner is executed.

I uploaded my workbook. First, increase the "Offers a variety of products" spinner by 20 increments (there is a 20 increment limit on the spinners). This will allow the spinners on the left to surpass 10.0. Next, increase "Clearly explains the programs and offerings they provide" spinner by 12 increments. Now the linked cell value is at 10.0. Next, click on it one more time. You can see the other linked cells increase, but then the assigned macro executes and the values go back down to the starting value. This is normal functionality.

Now if you click and hold the "Clearly explains the programs and offerings they provide" spinner, then a problem arises. The spinner increments even though the cell value is already at 10.0, which is undesired. Any ideas? I'm trying to avoid using the ActiveX spinners.

[mansfin]

Having trouble with the attachment manager. The file isn't uploading properly.

[Leith Ross]

Hello mansfin,

So have I. Keep trying.

[mansfin]

Here's the file. Changing the filename fixed the problem with the attachment manager

[Tsjallie]

The macro is only executed when you release the spinbutton, but the spinbutton code is executed immediately and repeated as long as you hold the botton. So there's your problem.
Guess you should move control entirely to the macro. Means that you would not link the spinbuttons to a cell but have the macro assign the spinbutton values to these cells.
Should be something like

[ImpAgg_PE!$Z$27] = ActiveSheet.Shapes("Spinner20").ControlFormat.Value
[ImpAgg_PE!$Z$28] = ActiveSheet.Shapes("Spinner21").ControlFormat.Value
[ImpAgg_PE!$Z$29] = ActiveSheet.Shapes("Spinner22").ControlFormat.Value
[ImpAgg_PE!$Z$30] = ActiveSheet.Shapes("Spinner23").ControlFormat.Value
[ImpAgg_PE!$Z$31] = ActiveSheet.Shapes("Spinner24").ControlFormat.Value

Tried this myself but ran into some more required adjustments. So still working on it. But this sure will be easier for you being more familiar to the code

Extra adjustements needed come from - afaiks now - this line

RowNumber = Right(Selection.LinkedCell, 2)

So you would also need an alternative to determine the rownumber. Probably something like this

Select Case Application.Caller
    Case "Spinner20"
        RowNumber = 27
    Case "Spinner21"
        RowNumber = 28
    Case "Spinner22"
        RowNumber = 29
etc ...

[mansfin]

Thanks a bunch!

[Tsjallie]

You're welcome. Thx for the rep
If this solved your problem, pls mark the thread [Solved] (see Thread Tools).

BTW: the --() function is a so called double unary operator. This translates TRUEs and FALSEs to their numeric values 1 and 0 resp.