troubleshooting Question

VBA - multiple options affecting IF

Avatar of James C
James C asked on
Microsoft OfficeMicrosoft ExcelVBA
11 Comments1 Solution225 ViewsLast Modified:
I have various choices on a worksheet named "INSERT" that hide rows on a worksheet called "Calculation".
The worksheet "INSERT" is hidden before users can change selections in "Calculation".
Some of these changes are overriding selections in "INSERT", (i.e. some rows that were hidden).

I need to do some sort of IF AND statement but there are 6 options in "INSERT" and therefore my code may end up being very long, unless you could kindly assist me please.

A selection of an idea I have is as follows:

I should also state that within the code for the macros when clicking the buttons within "INSERT" to make selections, I have entered a 1 in a cell underneath the button to show that it was selected (e.g. E19).
 ElseIf Range("D3").Value = "L" And Sheets("INSERT").Range("E19") = "1" Then
       Sheets("Calculation").Unprotect Password:="hello01"
        Range("D9:G9").Select
        ActiveCell.FormulaR1C1 = "1"
        Range("D6:G6").Select
        ActiveCell.FormulaR1C1 = "0"
        Rows("6:6").Hidden = True
        Rows("9:10").Hidden = False
        Rows("11:11").Hidden = False
        Rows("13:13").Hidden = True
        Rows("22:22").Hidden = False
        Rows("32:34").Hidden = True
       Sheets("Calculation").Protect Password:="hello01"
ElseIf Range("D3").Value = "L" And Sheets("INSERT").Range("E19") = "0" Then
       Sheets("Calculation").Unprotect Password:="hello01"
        Range("D9:G9").Select
        ActiveCell.FormulaR1C1 = "1"
        Range("D6:G6").Select
        ActiveCell.FormulaR1C1 = "0"
        Rows("6:6").Hidden = False
        Rows("9:10").Hidden = False
        Rows("11:11").Hidden = False
        Rows("13:13").Hidden = True
        Rows("22:22").Hidden = False
        Rows("32:34").Hidden = True
       Sheets("Calculation").Protect Password:="hello01"
The above code is for only 1 of the 6 choices so you can see why it'd end up being very long without a shortcut you may know of.
Thanking you in advance!
ASKER CERTIFIED SOLUTION
GrahamSkan
Retired
Join our community to see this answer!
Unlock 1 Answer and 11 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros