Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 30

Thread: Hide rows with option buttons

  1. #1
    VBAX Regular
    Joined
    Jan 2021
    Posts
    65
    Location

    Hide rows with option buttons

    I have a group of 3 option buttons and each option button relates to a group of few rows. In my code option buttons names are "Gumbmogućnosti".
    What Im trying to do is to hide rows depending on which option button is chosen. I would like Excel to automatically display (i.e. unhide) 3 rows and hide the other 9 rows (i.e. the other 2 sets of rows).
    I tried to find solution on internet but nothing works for me. This is one example what I tried but I got "error 424- Object required".
    Private Sub Gumbmogućnosti2_Klikni()
    If Gumbmogućnosti2.Value = True Then
    Rows("11:14").EntireRow.Hidden = False
    Rows("16:18").EntireRow.Hidden = True
    Rows("20:23").EntireRow.Hidden = True
    End If
    End Sub
    
    
    Private Sub Gumbmogućnosti3_Klikni()
    If Gumbmogućnosti3.Value = True Then
    Rows("11:14").EntireRow.Hidden = True
    Rows("16:18").EntireRow.Hidden = False
    Rows("20:23").EntireRow.Hidden = True
    End If
    End Sub
    
    
    Private Sub Gumbmogućnosti4_Klikni()
    If Gumbmogućnosti4.Value = True Then
    Rows("11:14").EntireRow.Hidden = True
    Rows("16:18").EntireRow.Hidden = True
    Rows("20:23").EntireRow.Hidden = False
    End If
    End Sub
    Attached Files Attached Files

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    You're using Form controls (not ActiveX ones) so get each button to call the same macro in that sheet's own code-module:
    Sub blah()
    Set OptBtns = Me.OptionButtons
    Rows("11:14").EntireRow.Hidden = Not (OptBtns(1).Value = 1)
    Rows("16:18").EntireRow.Hidden = Not (OptBtns(2).Value = 1)
    Rows("20:23").EntireRow.Hidden = Not (OptBtns(3).Value = 1)
    End Sub
    To make it a bit more future proof you could use the likes of:
    Rows("11:14").EntireRow.Hidden = Not (OptBtns("Option Button 2").Value = 1)
    instead of:
    Rows("11:14").EntireRow.Hidden = Not (OptBtns(1).Value = 1)
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    VBAX Regular
    Joined
    Jan 2021
    Posts
    65
    Location
    Great, work perfectly. Thank you sir

  4. #4
    VBAX Regular
    Joined
    Jan 2021
    Posts
    65
    Location
    Can someone help me to change this code so that OptionButton on Sheet1 show or hide rows on Sheet2?

  5. #5
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Wherever you see
    Rows
    Change it to
    Sheet2.Rows
    Or
    Sheets ("Sheet2").Rows
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  6. #6
    VBAX Regular
    Joined
    Jan 2021
    Posts
    65
    Location
    Quote Originally Posted by p45cal View Post
    Wherever you see
    Rows
    Change it to
    Sheet2.Rows
    Or
    Sheets ("Sheet2").Rows
    Thanx

  7. #7
    VBAX Regular
    Joined
    Jan 2021
    Posts
    65
    Location
    Why doesn't it work when I try to repeat the same thing multiple times. I create a new sub that has the same code but hides other lines and I get error 400. An example is attached.

    Sub blah()
    Set OptBtns = Me.OptionButtons
    Rows("11:14").EntireRow.Hidden = Not (OptBtns("Option Button 2").Value = 1)
    Rows("16:18").EntireRow.Hidden = Not (OptBtns(2).Value = 1)
    Rows("20:23").EntireRow.Hidden = Not (OptBtns(3).Value = 1)
    End Sub
    
    
    Sub test()
    Set OptBtns = Me.OptionButtons
    Rows("34:36").EntireRow.Hidden = Not (OptBtns(6).Value = 1)
    End Sub
    vbaExpress68592Hiding rows.xlsm

  8. #8
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Because there is no OptBtns(6). There are only 5 option buttons on the sheet.
    As mentioned in msg#2, it would be more robust if you used the option buttons' names, so that line would become either:
    Rows("34:36").EntireRow.Hidden = Not (OptBtns("Option Button 7").Value = 1)
    or maybe:
    Rows("34:36").EntireRow.Hidden = Not (OptBtns("Option Button 6").Value = 1)
    (I'm not sure which button you want it to refer to.)
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  9. #9
    VBAX Regular
    Joined
    Jan 2021
    Posts
    65
    Location
    Thanks for quick response. Stupid mistake as I was hastily making an example. Here in the attachment is a specific excel file with which I have the described problem. On sheet 2, line 60, there is a group frame with two radio buttons assigned the specified macro command, but it does not work.
    Attached Files Attached Files

  10. #10
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Exactly the same, there are only 9 option buttons. You need to use its name; "Option Button 13" or "Option Button 14"
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  11. #11
    VBAX Regular
    Joined
    Jan 2021
    Posts
    65
    Location
    Quote Originally Posted by p45cal View Post
    Exactly the same, there are only 9 option buttons. You need to use its name; "Option Button 13" or "Option Button 14"
    Ah, such a stupid mistake. Sorry for the trouble and thank you very much.

  12. #12
    VBAX Regular
    Joined
    Jan 2021
    Posts
    65
    Location
    Now I realize that I will need to add a little more to this code. Namely, the sheet will be locked, so I should insert a code that will unlock specific rows, hide them and lock them again. And vice versa if they are hidden, unlock them, reveal them and lock them again.

  13. #13
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Check out worksheet.protect and its argument called UserInterfaceOnly
    You only need to use it once after opening the workbook and it remains in force until the workbook is closed (or you actually change it back in the code).
    See: https://docs.microsoft.com/en-us/off...ksheet.Protect
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  14. #14
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    In
    Rows("11:14").EntireRow.Hidden = Not (OptBtns("Option Button 2").Value = 1
    entirerow is redundant.

    either
    Rows("11:14").Hidden
    or
    range("11:14").EntireRow.Hidden
    In this case:
    Sub M_snb()
      With Range("11:14,16:18,20:23")
         .EntireRow.Hidden = 1
         .Areas(Abs(3 * (OptionButtons(1) = 1) + 2 * (OptionButtons(2) = 1) + 1 * (OptionButtons(3) = 1))).EntireRow.Hidden = False
       End With
    End Sub

  15. #15
    VBAX Regular
    Joined
    Jan 2021
    Posts
    65
    Location
    Quote Originally Posted by snb View Post
    In
    Rows("11:14").EntireRow.Hidden = Not (OptBtns("Option Button 2").Value = 1
    entirerow is redundant.

    either
    Rows("11:14").Hidden
    or
    range("11:14").EntireRow.Hidden
    In this case:
    Sub M_snb()
      With Range("11:14,16:18,20:23")
         .EntireRow.Hidden = 1
         .Areas(Abs(3 * (OptionButtons(1) = 1) + 2 * (OptionButtons(2) = 1) + 1 * (OptionButtons(3) = 1))).EntireRow.Hidden = False
       End With
    End Sub
    This code also works great. Thank you.

  16. #16
    VBAX Regular
    Joined
    Jan 2021
    Posts
    65
    Location
    No matter how hard I try, I can't adapt the code to respond to me in another part of the program that I want to add. I note that I have nothing to do with programming and I just want to make my everyday life easier at work. The problem is that I have two option buttons and if one works properly when activated the other macro has opposite commands. Basically, it is about three lines that should be hidden or revealed depending on which of the two options is selected. I would really appreciate your help with this. I have attached a file with an example of what I want to do. Thank you guys.

    test.xlsm

  17. #17
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    2022-08-22_221455.jpg

    So the blue rows are always hidden?
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  18. #18
    VBAX Regular
    Joined
    Jan 2021
    Posts
    65
    Location
    Yes, because in the green and red field I have another set of option buttons that will reveal the blue field part by part. But there is probably some sort of command collision. I can make a complete example to fully see the problem. If I understand correctly, this code works with the reverse logic "EntireRow.Hidden=Not", can it be changed to "EntireRow.Hidden=True" or something similar?

  19. #19
    VBAX Regular
    Joined
    Jan 2021
    Posts
    65
    Location
    But yes, my problem is when the option button is marked "Yes", it means that the green and blue fields will be hidden, otherwise both will be visible. And when I apply the same code to the "No" option button, it should hide the red and blue fields, but it won't hide the blue because it contradicts the previous command, that is, if "No" = 1 (hide blue) then "Yes" = 2 (show blue). If you understand what I want to say. It is probably a trivial problem, but it is difficult for me to solve it.

  20. #20
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Best attach a workbook with all related option buttons in it. No need for unrelated code or data. Explain what you want to happen and which option button group you want to override others.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Posting Permissions

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