Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jan 2007
    Thanked 1 Time in 1 Post

    Exclamation 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.

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Pa, USA
    Thanked 776 Times in 707 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
    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
    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
        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
            .Visible = False
        End If
    End With
    End Sub
    Attached Files Attached Files

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    zeddy (2016-03-05)

  4. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Newcazzle, UK
    Thanked 645 Times in 613 Posts
    Hi Maud

    ..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.


Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts