Consulting

Results 1 to 6 of 6

Thread: ActiveX Option Buttons No Longer Function After Hide Then Unhide Rows

  1. #1

    ActiveX Option Buttons No Longer Function After Hide Then Unhide Rows

    I have a spreadsheet with ActiveX option buttons in groups. They are ActiveX buttons, not Form Controls buttons. Those buttons are in rows 7:30. I have a command button (also ActiveX) with the caption "Hide" that will hide rows 7:30 and then change the caption to "Show." When I click on that command button again, it unhides rows 7:30 and changes the caption to "Hide." After hiding/unhiding those rows, the option buttons no longer work. When I click on one, a "ghost" option button appears below the selected option button on mouse-down, and then the ghost option button disappears on mouse-up.

    Command button code (pretty basic):
    Option Explicit
    
    Private Sub CommandButton1_Click()
    
    
        If CommandButton1.Caption = "Hide" Then
            Rows("2:6").EntireRow.Hidden = True
            CommandButton1.Caption = "Show"
        Else
            Rows("2:6").EntireRow.Hidden = False
            CommandButton1.Caption = "Hide"
        End If
    
    
    End Sub
    In order to get the option buttons to work again, I must close and reopen the workbook.

    I created a second test spreadsheet (attached) to see if this behavior was unique, and the second test spreadsheet displays the same behavior.

    If anyone knows what may be causing this and, more importantly, how to keep it from happening, that would be wonderful!
    Attached Files Attached Files

  2. #2
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Why hiding/unhiding rows ?

    Better coding:
    Private Sub CommandButton1_Click()
      with CommandButton1
       .Caption = iif(.caption="Hide","Show","Hide")
       Rows("2:6").Hidden = .caption="Show"
      end with
    End Sub

  3. #3
    That is a more elegant form - thanks!

    In the sheet where I discovered the problem, there are 45 sets of option buttons being used as filter options for a list. I display results at the bottom of the screen. Those 45 sets of options take up a lot of real estate, so once the user applies the filters selected I hide that real estate so that more results appear on screen, making it easier to view those results in a meaningful way.

  4. #4
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Use an 'intelligent' table.
    45 sets op optionbuttons is ridiculous.

  5. #5
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Alternative:
    Add all Filter options to a UserForm

    Use Ribbon Menu
    Sub MenuItem_Click
    'Not for real
       UserFormFilters.Show
    End Sub
    Use Command Button "ShowFilters"
    Sub cbutShowFilters_Click()
       UserFormFilters.Show
    End Sub
    Use Mouse
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
       Cancel = True
       UserFormFilters.Show
    End Sub
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  6. #6
    I did end up using a UserForm, since this is apparently a defect in Excel. Thanks again for the suggestions.

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
  •