Controlling an ActiveX Spin Button

[d.stephanou]

Hello,

I am using a spin button (Forms toolbar) to control the value of a
single decision variable (cell) in my spreadsheet. Unfortunately, the
incremental change allowed by the UserForms spin button is integer and

=1.

Ideally, I would like the change to be 0.1 but I understand this
requires VBA programming knowledge which, unfortunately, I don't have!

Can anyone help me get round this?

Thanks in advance,

Dennis

 

[Dave Peterson]

Maybe you could use a linked cell and a formula that refers to that linked cell:

=a1/10

and then use that cell in further calculations/displays.

 

[d.stephanou]

Thanks...

I thought about that myself, using a hidden cell and all, but I was
hoping to get some VBA assistance on this!

 

[Dave Peterson]

I put a spinner from the Forms toolbox on a worksheet -- not the spinner from
the control toolbox toolbar (aka activex).

I assigned it this macro:

Option Explicit
Sub testme()
Dim mySpinner As Spinner
With ActiveSheet
Set mySpinner = .Spinners(Application.Caller)
.Range("a1").Value = mySpinner.Value / 10
End With
End Sub

=====
Personally, I'd use the linked cell and the formula cell. You could always put
the linked cell in an out of the way location--even a hidden worksheet. It
seems a lot easier to me.

 

[d.stephanou]

Thanks for that I'll give it a shot.

The problem is that I am working on a big spreadsheet involving lots of
calculations, so I'm using the manual calculation option. The problem
is that this option prohibits the 'target' cell from updating itself -
unless I press F9 each time - so I am forced to use the automatic
option instead. This effectively means that for the slightest change
the spreadsheet will calculate itself over and over, practically making
the spin button a redundancy.

I'd rather first set the values my decision variables (3 in total) and
then have the spreadsheet execute the calculations by pressing F9.

 

[Dave Peterson]

Depending on how that cell is used in other calculations, it may be useful to
try the linked cell.

Excel is pretty smart. It knows which cells change and which cells need to be
recalculated.

But if that formula cell that refers to the linked cell is used in loads of
other cells, I see your point.