I have a file in which I have 22 tables. 10 tables you can modify and 12 you cant. I would like when a user has pressed a option button to show:

1. All tables
2. Tables that can be modified (Green)
3. Tables that cant be modified (Red)

Here is the file:

CodePudding user response：

Show/Hide Entire Rows

• Copy the code into a standard module, e.g. Module1. Right-click each of the option buttons and assign it the correct procedure.

Option Explicit

' Module Level Constants

Private Const YesRows As String _
        = "7:23,24:49,41:57,58:74,143:159," _
        & "177:193,211:227,245:261,279:295,313:329"
Private Const NoRows As String _
        = "75:91,92:108,109:125,126:142,160:176," _
        & "194:210,228:244,262:278,296:312,330:346," _
        & "347:364,365:381"
Private Const wbName As String = "Example.xlsx"
Private Const wsName As String = "Sheet2"

' Module Level Procedures (cannot be seen in the 'Assign Macro' dialog)

Private Function RefWorksheet() As Worksheet
    ' If you put the code in another macro-enabled workbook, 
    ' the workbook needs to be open, before you can use:
    Dim wb As Workbook: Set wb = Workbooks(wbName)
    ' If the code is in the same workbook, you will have to save it
    ' as a macro-enabled workbook, before you can instead use:
    'Dim wb As Workbook: Set wb = ThisWorkbook ' (recommended)
    Dim ws As Worksheet: Set ws = wb.Worksheets(wsName)
    Set RefWorksheet = ws
End Function

Private Sub ShowRows(ByVal ws As Worksheet, ByVal RowsAddress As String)
    ws.Range(RowsAddress).EntireRow.Hidden = False
End Sub

Private Sub HideRows(ByVal ws As Worksheet, ByVal RowsAddress As String)
    ws.Range(RowsAddress).EntireRow.Hidden = True
End Sub

' Public Procedures (can be seen in the 'Assign Macro' dialog)
' (Click each option button and assign it the correct procedure.)

Sub ShowAll()
    Dim ws As Worksheet: Set ws = RefWorksheet
    ShowRows ws, YesRows
    ShowRows ws, NoRows
End Sub

Sub ShowYes()
    Dim ws As Worksheet: Set ws = RefWorksheet
    ShowRows ws, YesRows
    HideRows ws, NoRows
End Sub

Sub ShowNo()
    Dim ws As Worksheet: Set ws = RefWorksheet
    HideRows ws, YesRows
    ShowRows ws, NoRows
End Sub