Results 1 to 3 of 3
2016-03-04, 14:29 #1
- Join Date
- Jan 2007
- Thanked 1 Time in 1 Post
Excel 2010 Spin Buttons vanishing! Workaround, or try Excel 2007/2013? Mission-critical
I am working on something mission-critical and am encountering buggy behaviour with Excel 2010. I cannot reproduce it rigorously, but now have seen it four times over two days, and then have read here
that others have had the same issue in Excel 2010. (I followed all advice there, to no avail.) It happens too often and yet randomly that I now don't trust using Spin Buttons (Format, not ActiveX, controls) to control the value of a cell.
Here's the problem.
In Excel 2007 I created a complex model which I am often asked to explain. It is not more complex than it needs to be, but is still complex, and I have to continue to be sure that any perhaps initially counterintuitive results are due to the complexity of what's being modelled (and are explicable) and not due to bugs of my own or Microsoft's.
It has many Form (not Active X) Control Spin Buttons. Each is used to place validated (e.g., whole numbers between 1 and 5) values in specific cells, in the normal way (using the Cell Link found under the Control Tab of the Format Control dialogue which you can choose when you right click the button.) I've used these for years without any problem.
I no longer have my Win 7 64 PC with Excel 2007. This week, I've been working on successive versions of the file on a new machine, Win 7 64, in Excel 2010. I created a new sheet with lots of buttons. The sheets also require a lot of Grouping (by rows) down to a max of 4 levels.
I have noticed that if I create such a linked button on a new sheet, and then group / ungroup the row that it contains, sooner or later (after some sort of random number of times doing this) the buttons will vanish.
This causes a huge problem, as what was once a self-explanatory GUI is now something of a mystery -- the user has no idea how to change things.
One option is for me to remain in Excel 2010 and workaround by redesigning the GUI so that users will now be told to type 1-5 (or whatever) into Validated cells. This ain't great, but should work. ( I don’t want to try drop down lists or anything “fancy” etc. for a specific reason).
Another option is to change versions of Excel -- but is this dangerous? (In my work in Excel 2010, I've not used any new features of Excel 2010 which were not in Excel 2007). I'm already a bit nervous because, well, this bug shouldn't be there, but do I risk bringing on more problems due to the version history? Should I
-- go back to Excel 2007 (where these buttons have always worked fine, and which can be bought legally online in the UK) but might I be tempting fate by trying to resume, in Excel 2007, the work I've been doing in Excel 2010 ? (The work has been nothing fancy -- just some basic logic and a lot of, well, Spin Buttons and Grouped Rows...). I don't know specifically why this would be problematic, but that doesn't mean there aren't risks I don't know about, just as I didn’t anticipate this present bug. As mentioned, this is mission-critical. I believe that if I did so, I would have to first uninstall Office 2010 before installing Office 2007.
-- go ahead to Excel 2013 and try my luck there? Though the only way to know if I've solved the problem is to put in all the buttons again and see if they behave properly.
To be honest I'm not crazy at all at having to buy a new version of Excel (or an old version again) just in an attempt to get out of his hole. I bought Excel 2010 a few years ago, installed it last week, and now instantly have to pay to (maybe) get the non-buggy new version.
Any advice here? I’d be very grateful.
2016-03-04, 21:50 #2
- Join Date
- Aug 2010
- Pa, USA
- Thanked 621 Times in 566 Posts
As an alternative, you can use one active x spinbutton for all your cells. When you click on specific cells, the spin button will appear to its right then disappear when any other cell is selected
Add an Active x spinbutton called Spinbutton1 to your sheet then copy the following code in the sheet module
Private Tcell As String Private Sub SpinButton1_SpinDown() On Error GoTo errorhandler If Range(Tcell) > 0 Then Range(Tcell) = Range(Tcell) - 1 Else Exit Sub errorhandler: Tcell = ActiveCell.Address End Sub Private Sub SpinButton1_SpinUp() On Error GoTo errorhandler If Range(Tcell) < 5 Then Range(Tcell) = Range(Tcell) + 1 Exit Sub errorhandler: Tcell = ActiveCell.Address End Sub Private Sub Worksheet_Activate() Tcell = 0 End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Count > 1 Then Exit Sub With SpinButton1 '------------------------------------------- 'CHANGE THE RANGE BELOW FOR YOUR SPECIFIC CELLS If Not Intersect(Target, Range("A2,D3,F7,F9")) Is Nothing Then Tcell = Target.Address .Left = Target.Offset(0, 1).Left .Top = Target.Offset(0, 1).Top .Visible = True Else: .Visible = False End If End With End Sub
The Following User Says Thank You to Maudibe For This Useful Post:
2016-03-05, 06:02 #3
- Join Date
- Mar 2002
- Newcazzle, UK
- Thanked 466 Times in 444 Posts
..a delightful method of using a single spin button.
..if you have just recently installed Excel2010, make sure you first have ALL the latest updates.
My Excel2010 version number is 14.0.7166.5000 and if your version is earlier than this then you seriously need to run Windows Update to get the latest service packs etc etc. I suspect that would resolve your issues, as button sizing issues etc etc were resolved a long time ago.