VBA Code Help - List

%3CLINGO-SUB%20id%3D%22lingo-sub-2594582%22%20slang%3D%22en-US%22%3EVBA%20Code%20Help%20-%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2594582%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%2C%20I%20have%20created%20a%20script%20that%20if%20you%20select%20from%20a%20drop-down%20list%2C%20it%20will%20then%20hide%2Fshow%20specific%20rows%20as%20seen%20on%20the%20code%20below.%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20it%20has%20a%20conflict%20with%20another%20script%20that%20hides%2Fshow%20rows.%20How%20can%20I%20avoid%20this%20thing%20to%20happen%3F%20Attached%20were%20the%20sample%20data%20file.%20Thanks%20in%20advance.%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22rbalza_0-1627514075891.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F299120i5C3F95E873013376%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22rbalza_0-1627514075891.png%22%20alt%3D%22rbalza_0-1627514075891.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22rbalza_1-1627514156683.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F299121i29D18CB39B1B453E%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22rbalza_1-1627514156683.png%22%20alt%3D%22rbalza_1-1627514156683.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%0A%20%20%20%20If%20Target.Column%20%3D%2016%20And%20Target.Row%20%3D%203%20Then%0A%20%20%20%20%20%20%20%20If%20Target.Value%20%3D%20%22Option%201%22%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20Worksheets(%22Annual%22).Range(%2266%3A66%2C75%3A75%2C134%3A134%2C171%3A178%2C244%3A244%2C246%3A246%2C256%3A259%2C263%3A263%22).EntireRow.Hidden%20%3D%20True%0A%20%20%20%20%20%20%20%20%20%20%20%20Worksheets(%22Annual%22).Range(%2265%3A65%2C168%3A170%2C197%3A197%2C216%3A235%2C239%3A242%2C247%3A247%2C264%3A264%2C275%3A275%22).EntireRow.Hidden%20%3D%20False%0A%20%20%20%20%20%20%20%20ElseIf%20Target.Value%20%3D%20%22Option%202%22%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20Worksheets(%22Annual%22).Range(%2266%3A66%2C75%3A75%2C134%3A134%2C171%3A178%2C244%3A244%2C246%3A246%2C256%3A259%2C263%3A263%22).EntireRow.Hidden%20%3D%20False%0A%20%20%20%20%20%20%20%20%20%20%20%20Worksheets(%22Annual%22).Range(%2265%3A65%2C168%3A170%2C197%3A197%2C216%3A235%2C239%3A242%2C247%3A247%2C264%3A264%2C275%3A275%22).EntireRow.Hidden%20%3D%20True%0A%20%20%20%20%20%20%20%20ElseIf%20Target.Value%20%3D%20%22All%22%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20Worksheets(%22Annual%22).Range(%2266%3A66%2C75%3A75%2C134%3A134%2C171%3A178%2C244%3A244%2C246%3A246%2C256%3A259%2C263%3A263%2C65%3A65%2C168%3A170%2C197%3A197%2C216%3A235%2C239%3A242%2C247%3A247%2C264%3A264%2C275%3A275%22).EntireRow.Hidden%20%3D%20True%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20End%20If%0A%20%20%20%20%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2594582%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2594992%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20Code%20Help%20-%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2594992%22%20slang%3D%22en-US%22%3Eagreed.%20thanks%20heap%20for%20your%20time%20mate%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%20appreciated!%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2594910%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20Code%20Help%20-%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2594910%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1098170%22%20target%3D%22_blank%22%3E%40rbalza%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20is%20becoming%20too%20complicated%2C%20sorry.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2594755%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20Code%20Help%20-%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2594755%22%20slang%3D%22en-US%22%3EWhen%20I%20tick%20the%20show%2Fhide%20(%2B)%20on%20the%20Task%20it%20will%20show%20both%20rows%20option%201%20%26amp%3B%20option%202%20while%20option%201%20is%20being%20selected.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2594750%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20Code%20Help%20-%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2594750%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1098170%22%20target%3D%22_blank%22%3E%40rbalza%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhat%20exactly%20is%20the%20conflict%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

Hi everyone, I have created a script that if you select from a drop-down list, it will then hide/show specific rows as seen on the code below. 

However, it has a conflict with another script that hides/show rows. How can I avoid this thing to happen? Attached were the sample data file. Thanks in advance.

rbalza_0-1627514075891.png

rbalza_1-1627514156683.png

 

 

 

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 16 And Target.Row = 3 Then
        If Target.Value = "Option 1" Then
            Worksheets("Annual").Range("66:66,75:75,134:134,171:178,244:244,246:246,256:259,263:263").EntireRow.Hidden = True
            Worksheets("Annual").Range("65:65,168:170,197:197,216:235,239:242,247:247,264:264,275:275").EntireRow.Hidden = False
        ElseIf Target.Value = "Option 2" Then
            Worksheets("Annual").Range("66:66,75:75,134:134,171:178,244:244,246:246,256:259,263:263").EntireRow.Hidden = False
            Worksheets("Annual").Range("65:65,168:170,197:197,216:235,239:242,247:247,264:264,275:275").EntireRow.Hidden = True
        ElseIf Target.Value = "All" Then
            Worksheets("Annual").Range("66:66,75:75,134:134,171:178,244:244,246:246,256:259,263:263,65:65,168:170,197:197,216:235,239:242,247:247,264:264,275:275").EntireRow.Hidden = True
        End If
    End If
    
End Sub

 

 

4 Replies

@rbalza 

What exactly is the conflict?

When I tick the show/hide (+) on the Task it will show both rows option 1 & option 2 while option 1 is being selected.

@rbalza 

This is becoming too complicated, sorry.

agreed. thanks heap for your time mate appreciated!