VBA Code to generate basic forms depending on one cell value

[jsrussell12]

Hi,

I am trying to create a macro that allows users to click a button and depending on a single cell value, hides certain rows within a range (containing a label and below that a text box).
The macro is for a user form that depending on the single value (Cell M15) could be have no hidden rows based with most complex option and hides some rows to become more simplistic depending on the cell (M15)

For example, if cell M15 was "Simple Form", would hide rows 51-73 and 27-49. If cell M15 was "Full Form", no rows would hide, and if was "Intermediate Form", would just hide rows 51-73.

Code so far is as follows:

Sub Gen_Form()
If Target.Address = "$M$15" Then
If Target.Value = "Simple Form" Then Rows ("27:49", "51-73").EntireRow.Hidden = True
If Target.Value = "Intermediate Form" Then Rows ("51-73").EntireRow.Hidden = True
End If
End Sub

However, when I run the macro, I get an object required error and the yellow code section is highlighted in the macro editor.

Any help would be greatly appreciated!

 

[rlv01]

Perhaps something like this

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$M$15" Then
    Dim HideRange1 As Range, HideRange2 As Range
        
        Set HideRange1 = Range("27:49").EntireRow
        Set HideRange2 = Range("51:73").EntireRow

        Select Case Target.Value
            Case "Simple Form"
                Application.Union(HideRange1, HideRange2).EntireRow.Hidden = True
            Case "Intermediate Form"
                HideRange1.Hidden = False
                HideRange2.Hidden = True

            Case Else
                HideRange1.Hidden = False
                HideRange2.Hidden = False
        End Select
    End If
End Sub

(to be located in the worksheet code module).

Keep in mind that hiding rows is one thing, but you should give some thought to when to un-hide themm.

(Tip: For future posts , you should try to use code tags like I did above when posting your code. It makes it easier to read.)

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

 

[jsrussell12]

Thank you!