VBA Code to generate basic forms depending on one cell value

jsrussell12

New Member
Joined
Mar 24, 2023
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Perhaps something like this
VBA Code:
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.)

 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top