1

Very Basic I am sure but I can't figure it out for the life of me.

I have a set of radio buttons that changes the value of cell("L37") between 1 and 2.

I have tried to write the VBA code several different ways without luck. Please advise.

Sub hide_sheet()
    If Worksheets("Feedback").Range("L37").Value = 1 Then
      Rows("63:93").EntireRow.Hidden = True
    Else Worksheets("Feedback").Range("L37").Value = 2 Then
      Worksheets("Feedback").Rows("63:93").EntireRow.Hidden = False
    End If
End Sub

I had it working perfectly fine via a Macro tied to the radio buttons; however, due to me needing to protect the sheet I needed to change it.

I am using a protect/unprotect VBA code and would like to include it into the code so it won't set off the macro/sheet protected warning.

Here is my protect/unprotect code I am using for everything.

Sub unprotect()

    Worksheets("Feedback").unprotect
    
End Sub

Sub protect()

    Worksheets("Feedback").protect , _
        AllowFormattingCells:=True, _
        AllowFormattingRows:=True
        
    
End Sub

Any advice would be greatly appreciated. I Thank You in Advance of your assistance.

Methodikal
  • 13
  • 3

2 Answers2

3

Show/Hide Rows

Issues

  • The Feedback worksheet is not qualified so if the wrong workbook is active, it will fail. To reference the workbook containing this code, you can use ThisWorkbook:

    ThisWorkbook.Worksheets("Feedback")...

  • You are using Rows("63:93") instead of Worksheets("Feedback").Rows("63:93") in the If clause. If the wrong worksheet is active (selected), it will fail.

  • You are using Else instead of ElseIf.

  • You can use the With statement to reduce typing as illustrated in the following code.

  • If you convert the cell value to a string, then if the cell accidentally contains an error value, the code will not fail.

The Code

Sub ShowHideRowsFix()
    With ThisWorkbook.Worksheets("Feedback")
        .Unprotect
        Select Case CStr(.Range("L37").Value)
        Case "1"
            .Rows("63:93").Hidden = True
        Case "2"
            .Rows("63:93").Hidden = False
        Case Else
        End Select
        .Protect AllowFormattingCells:=True, AllowFormattingRows:=True
    End With
End Sub

An Improvement

  • To automate this operation (no need for buttons), in the sheet module of the Feedback worksheet identified in the VBE Project explorer window by e.g. Sheet1(FeedBack) (double-click to open), you could use the following code.
Private Sub Worksheet_Change(ByVal Target As Range)
    
    Const CellAddress As String = "L37"
    
    Dim Cell As Range: Set Cell = Me.Range(CellAddress)
    If Intersect(Cell, Target) Is Nothing Then Exit Sub
    
    ShowHideRows Cell

End Sub

Sub ShowHideRows(ByVal Cell As Range)
    With Cell.Worksheet
        .Unprotect
        Select Case CStr(Cell.Value)
        Case "1"
            .Rows("63:93").Hidden = True
        Case "2"
            .Rows("63:93").Hidden = False
        Case Else
        End Select
        .Protect AllowFormattingCells:=True, AllowFormattingRows:=True
    End With
End Sub
VBasic2008
  • 44,888
  • 5
  • 17
  • 28
0

I prefer the simplest approach where possible. This is the approach I use to hide columns re-written for your needs.

The following code may exist on the UserForm or in a standard Code Module: 
Sub rwControl(ByRef hType As String)
  Dim rwVis As Boolean
  If hType = "hRW" Then rwVis = True
  If hType = "uRW" Then rwVis = False
    With ThisWorkbook.Worksheets("Feedback")
      .Unprotect
      .Rows("63:93").Hidden = rwVis
      .Protect
    End With
End Sub

You may call this code directly from you RadioButton_Change() Event vy including the following code:

IF RadioButton1.Value = True then 'Assuming the value is 1
   rwControl "hRW"   'This hides the Rows
 ELSE
   rwControl "uRW"   'This unhides the Rows
End If

OR, to keep it really simple:

in your RadioButton_Change() event simply add: 
Private Sub RadioButton1 Change()
With ThisWorkBook.Worksheets("Feedback")
    .Unprotect
        If RadioButton1.Value = True Then
            .Rows("63:93").Hidden = True
          Else
            .Rows("63:93").Hidden = False
        End If
      .Protect
End With
End Sub

Using this approach negates the need for Worksheet Module Coding and Case Coding and allows you to keep the RadioButtons if you deem them important to the functionality.

hermannjt
  • 37
  • 8