Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Substitute for OLE SpinButton?
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Substitute for OLE SpinButton?
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" wrote: 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Substitute for OLE SpinButton?
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. "Mike H" wrote: 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" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How:spinbutton to display negative integers(e.g. range -10 to 10) | Excel Discussion (Misc queries) | |||
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE.... max. limit 8 :( | Excel Discussion (Misc queries) | |||
Excel 2003 visual basic for 'spinbutton' | Excel Discussion (Misc queries) | |||
Spinbutton | New Users to Excel | |||
Spinbutton | Excel Discussion (Misc queries) |