How to Hide certain userform fields during initialization?

Question

So I have a userform that consist of Multipagesand one of the pages contain togglebuttons which hide and unhide fields on the Userform as well as on the excel worksheet. The picture below shows the togglebutton page.

The code for HAZOP/ SIL & LOPA is the same except which fields it hides is different. Below is the code for HAZOP togglebutton.

Private Sub togbHAZOP_Click()

    If togbHAZOP = True Then

        Sheets("Updated Hours EST").Rows("6:27").EntireRow.Hidden = False
        Sheets("SCOPE").Rows("31:37").EntireRow.Hidden = False
        Sheets("SUMMARY").Rows("5:8").EntireRow.Hidden = False
        Frame5.Enabled = True
        Frame5.Visible = True
        Frame6.Enabled = True
        Frame6.Visible = True
        Frame7.Enabled = True
        Frame7.Visible = True
        HazOp.Enabled = True
        HazOp.Visible = True

    Else
        Sheets("Updated Hours EST").Rows("6:27").EntireRow.Hidden = True
        Sheets("SCOPE").Rows("31:37").EntireRow.Hidden = True
        Sheets("SUMMARY").Rows("5:8").EntireRow.Hidden = True
        Frame5.Enabled = False
        Frame5.Visible = False
        Frame6.Enabled = False
        Frame6.Visible = False
        Frame7.Enabled = False
        Frame7.Visible = False
        HazOp.Enabled = False
        HazOp.Visible = False

    End If

End Sub

Code for Initialization at the moment but it doesn't work, gives an error saying "Run-time error 438: Object doesn't support this property or method"

Private Sub UserForm_Initialize()

    WizardProp.MultiPage1.Value = 0
    Me.MultiPage1.Style = fmTabStyleNone
    togbHAZOP.Frame5.Enabled = False
    togbHAZOP.Frame5.Visible = False
    togbHAZOP.Frame6.Enabled = False
    togbHAZOP.Frame6.Visible = False
    togbHAZOP.Frame7.Enabled = False
    togbHAZOP.Frame7.Visible = False
    togbHAZOP.HazOp.Enabled = False
    togbHAZOP.HazOp.Visible = False

End Sub

The issue I am having is how do I hide certain fields within the userform at the beginning of the code and based upon the user's selection using the togglebuttonswill hide/unhide because at the moment I have to click on the togglebuttons then unclick to get it where the linked fields are hidden and doing that everytime during initialization can be a nuisance.

When you debug it do you know which line you get that error on? — Hasib_Ibradzic
– Hasib_Ibradzic, Commented Mar 20, 2019 at 15:43

Pᴇʜ · Accepted Answer · 2019-03-20 15:48:04Z

Note that you can reduce the code like below. Replace True with togbHAZOP.Value and False with Not togbHAZOP.Value in the If togbHAZOP = True part. So you don't need to repeat the whole code.

Private Sub togbHAZOP_Click()
    Sheets("Updated Hours EST").Rows("6:27").EntireRow.Hidden = Not togbHAZOP.Value
    Sheets("SCOPE").Rows("31:37").EntireRow.Hidden = Not togbHAZOP.Value
    Sheets("SUMMARY").Rows("5:8").EntireRow.Hidden = Not togbHAZOP.Value
    Frame5.Enabled = togbHAZOP.Value
    Frame5.Visible = togbHAZOP.Value
    Frame6.Enabled = togbHAZOP.Value
    Frame6.Visible = togbHAZOP.Value
    Frame7.Enabled = togbHAZOP.Value
    Frame7.Visible = togbHAZOP.Value
    HazOp.Enabled = togbHAZOP.Value
    HazOp.Visible = togbHAZOP.Value    
End Sub

Since togbHAZOP is a toggle it does not have a frame togbHAZOP.Frame5 that's probably the issue here. It should probably be something like:

Private Sub UserForm_Initialize()
    WizardProp.MultiPage1.Value = 0
    Me.MultiPage1.Style = fmTabStyleNone
    Me.Frame5.Enabled = False
    Me.Frame5.Visible = False
    Me.Frame6.Enabled = False
    Me.Frame6.Visible = False
    Me.Frame7.Enabled = False
    Me.Frame7.Visible = False
    Me.HazOp.Enabled = False
    Me.HazOp.Visible = False
End Sub

Collectives™ on Stack Overflow

How to Hide certain userform fields during initialization?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related