Hide rows with option buttons

[hrzagi]

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

[p45cal]

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)

[hrzagi]

Great, work perfectly. Thank you sir :thumb

[hrzagi]

Can someone help me to change this code so that OptionButton on Sheet1 show or hide rows on Sheet2?

[p45cal]

Wherever you see
Rows
Change it to
Sheet2.Rows
Or
Sheets ("Sheet2").Rows

[hrzagi]

Wherever you see
Rows
Change it to
Sheet2.Rows
Or
Sheets ("Sheet2").Rows

Thanx :thumb

[hrzagi]

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


30022

[p45cal]

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

[hrzagi]

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.

[p45cal]

Exactly the same, there are only 9 option buttons. You need to use its name; "Option Button 13" or "Option Button 14"

[hrzagi]

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.

[hrzagi]

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.

[p45cal]

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/office/vba/api/Excel.Worksheet.Protect

[snb]

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

[hrzagi]

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

[hrzagi]

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.

30082

[p45cal]

30083

So the blue rows are always hidden?

[hrzagi]

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?

[hrzagi]

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.

[p45cal]

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.

[hrzagi]

Ok, this is complete example.
30087

30088

[snb]

Never use merged cells.

[hrzagi]

Never use merged cells.

Ok, can you do it without those merged cells, they are irrelevant, they don't have to be merged. Just use three rows instead of three pairs of merged cells.

[p45cal]

Test the attached

[hrzagi]

Test the attached
It works great and as I can see in the code it was much more complex than what I was trying. Thank you very much, you saved me. :thumb

[hrzagi]

Is there a reason why you abandoned the code for Option buttons and continued working with ActiveX radio buttons? Because I tried the code you left for the Option buttons and it seems to me that it could also work quite well, at least on the first try. Only the line
Set iii = ActiveSheet.GroupBoxes gave me problems, and when I removed it, everything works fine. I'm asking out of curiosity, is there a reason I shouldn't use that code?

[p45cal]

I shouldn't have left any code modules in that file. Oops. That code shouldn't ever be called anyway. The only relevant code is in the sheet's code-module and in the ThisWorkbook code-module.
I found that the forms control option buttons depended heavily on their being in a forms control group box so that they worked properly together in groups, but because the group boxes and option buttons moved around with the hiding of rows (regardless of the settings in each control's format cntrol dialogue box) the option buttons swapped their allegiances among the group boxes.
ActiveX option buttons don't need a group box to keep them in their group, they have a Group property which you can assign and that doesn't change.
ActiveX controls on a sheet often have their own problems, but if these occur you can overcome them in the code.

So both standard code-modules Module1 and Module2 should be removed.

I have updated the attachment in msg#24 (A small correction was needed in the remaining code).

[snb]

You are making a logical error in your concept.
Don't use an algorithm that recurs back to the algorithm itself.
In this case: do not use row-dependent elements to show/hide rows.

[hrzagi]

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/office/vba/api/Excel.Worksheet.Protect

I have tried several solutions from the internet but none of them work for me. I need the example file to be protected, but to still be able to hide and show rows. This code should work but it doesn't.


Private Sub Workbook_Open()
Dim wSheetName As Worksheet
For Each wSheetName In Worksheets
wSheetName.Protect Password:="Secret", UserInterFaceOnly:=True
Next wSheetname
End Sub

Do you have any ideas how to solve this?

[p45cal]

I've added
Private Sub Workbook_Open()
Dim wSheetName As Worksheet
For Each wSheetName In Worksheets
wSheetName.Protect Password:="Secret", UserInterFaceOnly:=True
Next wSheetName
For Each ctrl In Array(List1.OptionButton1, List1.OptionButton2)
ctrl.Value = False
Next ctrl
End Sub
to the attached (added only the first 3 lines). It seems to work as expected; option buttons working, but the user can't manually do much. What's going wrong?