Substitute for OLE SpinButton?

[Daystrom]

Hi:

I'm building a spreadsheet on a PC that contains a SpinButton with the
following code:

Private Sub SpinButton1_SpinDown()
Me.OLEObjects("SpinButton1").Object.Max = _
Range("TotalRecords").Value
Me.OLEObjects("SpinButton1").Object.Min = 4
Range("RowIndex").Value = Me.OLEObjects("SpinButton1").Object.Value
End Sub

Private Sub SpinButton1_SpinUp()
Me.OLEObjects("SpinButton1").Object.Max = _
Range("TotalRecords").Value
Me.OLEObjects("SpinButton1").Object.Min = 4
Range("RowIndex").Value = Me.OLEObjects("SpinButton1").Object.Value
End Sub

All it needs to do is update its maximum value from a cell on the worksheet
called "Total Records." (The minimum value is always 4.) Then it deposits its
value to a cell on the sheet named "RowIndex."

This all works really well on the PC, but when some users in my department
open the workbook on their Macs, the SpinButton isn't accessible, and the
code returns a bizarro error indicating that the OLE objects could not be
created. I did some digging around in the help menu, and it seems like this
uses ActiveX and, as a result, won't work on the Macs.

I don't really know much about this sort of thing. I've tried using a
standard spin control from the Forms toolbar (you know, that you can assign a
"cell link" to), but I can't figure out how to set its maximum value so that
it updates from the "TotalRecords" cell. (And even in the course of trying to
figure out THAT issue, I can't seem to write a macro that will run and leave
the control unselected when its done.)

I've far exceeded my stupidity threshold. If anyone could tell me what I'm
doing wrong, I'd really appreciate it. It seems like I should be able to do
something like this so that it runs well on both the PC and the Mac, but
clearly I can't. Help!

 

[Mike H]

Hi,

Slightly diferent for the Forms objects

With ActiveSheet.Shapes("Spinner 1").ControlFormat
.Min=0
.Max = 999
.SmallChange = 22
.LinkedCell = "$D$4"
End With

Mike

 

[Daystrom]

Thanks for the fast reply! Unfortunately, it still won't go. Even making the
changes on a Mac, the code keeps bombing out on me. I tried the following as
a starter, not even bothering with the named-ranges thing and picking an
arbitrary cell to link to. ("Spinner 42" is the name XL assigned when I
created the new spinner using the forms toolbar.):

Sub Funtime()
ActiveSheet.Shapes("Spinner 42").Select
With Selection
.Value = 0
.Min = 4
.Max = 30000
.SmallChange = 1
.LinkedCell = $E$4
.Display3DShading = True
End With
End Sub

I tried variations on this (you know, with selecting the control, without
selecting it, etc. etc.), and then just tried recording a macro while
changing the control properties. Oddly, even playing back the recorded macro
resulted in the same same "application defined or object defined" error. If
you have any more ideas, that would be great. I do believe I may cry.