Creating a 'Reset' button for a text form, plus additional buttons

[WEB78]

Hello,

I am decent with excel but brand new to the VBA side of things; decided that I need specific help because I'm using excel in a unique way (not financial) and can't seem to find precisely what I need. My job is to determine what legislation or other internal processes are triggered by the nature of the work that my company is proposing to undertake. To do this, I have created a series of yes/no questions which are tied together with in-cell =IFS formulas. I have added some VBA code for hiding any rows deemed 'N/A' and a colleague helped with a =VRLookUp formula for adding a drop-down list which pulls from a reference sheet within the same workbook. All of these elements are working how I want them to.

Now I want to add some user features such as a 'Reset' button for restoring the form to its original position but I am running into an error in my row-hiding sub. For the =VRLookUp section, I want to add a 'DONE Adding Mitigations' button which will hide/delete all rows up to the last entry.

Lastly I want to create a userform which will prompt the user to add additional information depending on how they have responded to select questions in the screening form. Something like: "You answered "YES" to [question from screening form]. Provided a detailed explanation below". The userform will be launched via a button at the end of screening form. This is a lot so anyone who is up for the challenge I greatly appreciate the help!!

My row hiding code looks like this:

Private Sub worksheet_change(ByVal Target As Range)

If Range("I28").Value = "NO" Then
Rows("32:45").EntireRow.Hidden = True
ElseIf Range("I28").Value = "YES" Then
Rows("32:45").EntireRow.Hidden = False
End If


If Range("I28").Value = "NO" Then
Rows("51:56").EntireRow.Hidden = True
ElseIf Range("I28").Value = "YES" Then
Rows("51:56").EntireRow.Hidden = False
End If...

This is my first post so thank you everyone!

 

[Logit]

As a start you can change your posted macro to :

Private Sub worksheet_change(ByVal Target As Range)

If Range("I28").Value = "NO" Then
     Rows("32:45").EntireRow.Hidden = True
     Rows("51:56").EntireRow.Hidden = True

ElseIf Range("I28").Value = "YES" Then
     Rows("32:45").EntireRow.Hidden = False
     Rows("51:56").EntireRow.Hidden = False

End If

end sub

How are you accomplishing the following in your workbook ?

Now I want to add some user features such as a 'Reset' button for restoring the form to its original position but I am running into an error in my row-hiding sub. For the =VRLookUp section, I want to add a 'DONE Adding Mitigations' button which will hide/delete all rows up to the last entry.

 

[WEB78]

Here is a snip of the end of the code for the RESET button. It's long and repetitive and essentially just replaces each cell that the user may change with either "" (for blank), "yes", or "no" and then unhides any cells that have been hidden by the user. Please don't judge my lack of elegance:

...
Range("I74").Value = "no"
Range("I76").Value = "no"
Range("I78").Value = "no"
Range("I80").Value = "no"

Rows.EntireRow.Hidden = False
End Sub

I have decided to add a SUBMIT button after each section which is where I will take the code you re-wrote for me (thank you by the way) for hiding the N/A rows based on user responses. The reason for going with a button is that things were getting laggy and I think it might be because I was using a 'change window' so it was always running in the background, I could be wrong though. Either way, using a SUBMIT button also makes the entry more affirmative and can trigger some other necessary things: the form may trigger a need for the user to add an explanation for certain choices so I have a second sheet to copy any of these triggering responses with a cell below for an explanation and adding hyperlinks to outside documents.

Thank you again for your assistance!