SheetChange script throws Run Time error when clearing any cell

[supercharger]

I'm novice by all definitions with VBA, but I can usually piece things together that I need to do. this one is stumping me.

I have a cell, C62, that I have a data validation list with five options. Two options out of that, I want it to make additional rows, 65-68, visible.

The code works perfectly; if I clear any cell in the entire worksheet, I get a Run Time Error 13 dialog.

Any help is greatly appreciated!

Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
 
        If Target.Address = ("$C$62") And Target.Value2 = "Minimal with External/Contract Change Resource - Advisory and material review" Then
       Sheets("Change Needs Assessment").Rows("65:68").EntireRow.Hidden = False
       ElseIf Target.Address = ("$C$62") And Target.Value2 = "Moderate + - External/Contract Change Lead requested for change strategy and deliverable " Then
       Sheets("Change Needs Assessment").Rows("65:68").EntireRow.Hidden = False
        ElseIf Target.Address = ("$C$62") And Target.Value2 = "" Then
        Sheets("Change Needs Assessment").Rows("65:68").EntireRow.Hidden = True
        ElseIf Target.Address = ("$C$62") And Target.Value2 = "Minimal with Internal Strategic Change Resource- Advisory and material review" Then
        Sheets("Change Needs Assessment").Rows("65:68").EntireRow.Hidden = True
        ElseIf Target.Address = ("$C$62") And Target.Value2 = "Moderate + - Internal Change Lead requested for change strategy and deliverable " Then
        Sheets("Change Needs Assessment").Rows("65:68").EntireRow.Hidden = True
        ElseIf Target.Address = ("$C$62") And Target.Value2 = "None - The project does not believe Change Management is required" Then
        Sheets("Change Needs Assessment").Rows("65:68").EntireRow.Hidden = True
        
        End If
       
       
        
End Sub

 

[gallen]

Hello and welcome.

I can't really see how this occurs:

if I clear any cell in the entire worksheet, I get a Run Time Error 13 dialog.

The event triggers whenever any cell of any sheet changes however your code checks if the address for the changed cell is "C62" and if it isn't then nothing will happen so I can only assume some other code is being triggered elsewhere.

When you get the error, what line is highlighted when you click 'debug'?

 

[Joe4]

Welcome to the Board!

Try putting this code in the Worksheet_Change event procedure of the sheet that the drop-down box appears in.

Also, I am not a big fan of multiple ElseIF statements. I find them a bit cumbersome to read, and repetitive in this case. Use CASE statements is much cleaner, i.e.

Private Sub Worksheet_Change(ByVal Target As Range)

        If Target.Address = ("$C$62") Then
            Select Case Target.Value
                Case "Minimal with External/Contract Change Resource - Advisory and material review"
                    Sheets("Change Needs Assessment").Rows("65:68").EntireRow.Hidden = False
                Case "Moderate + - External/Contract Change Lead requested for change strategy and deliverable "
                    Sheets("Change Needs Assessment").Rows("65:68").EntireRow.Hidden = False
                Case ""
                    Sheets("Change Needs Assessment").Rows("65:68").EntireRow.Hidden = True
                Case "Minimal with Internal Strategic Change Resource- Advisory and material review"
                    Sheets("Change Needs Assessment").Rows("65:68").EntireRow.Hidden = True
                Case "Moderate + - Internal Change Lead requested for change strategy and deliverable "
                    Sheets("Change Needs Assessment").Rows("65:68").EntireRow.Hidden = True
                Case "None - The project does not believe Change Management is required"
                    Sheets("Change Needs Assessment").Rows("65:68").EntireRow.Hidden = True
            End Select
        End If

End Sub

Since you are either hiding or unhiding the same group of rows, there is really only two possible outcomes, so you could, in theory reduce it down to just two checks, but since your descriptions are so long, that might be a bit jumbled and harder to read.

See here for more on Case statements: MS Excel: How to use the CASE Statement (VBA)

 

[steve the fish]

Hi. Are you clearing more than one cell at once? Target.Value2 will be an array of Values so your Target.Value2 wont exist. As for why that happens with SheetChange and you are clearing cells im not sure. Using your macro you can do something like:

Set c = Intersect(Target, Range("C62"))
If Not c Is Nothing Then
    If c.Value2 = "Minimal with External/Contract Change Resource - Advisory and material review" Then
        Sheets("Change Needs Assessment").Rows("65:68").EntireRow.Hidden = False
    ElseIf c.Value2 = "Moderate + - External/Contract Change Lead requested for change strategy and deliverable " Then
        Sheets("Change Needs Assessment").Rows("65:68").EntireRow.Hidden = False
    ElseIf c.Value2 = "" Then
        Sheets("Change Needs Assessment").Rows("65:68").EntireRow.Hidden = True
    ElseIf c.Value2 = "Minimal with Internal Strategic Change Resource- Advisory and material review" Then
        Sheets("Change Needs Assessment").Rows("65:68").EntireRow.Hidden = True
    ElseIf c.Value2 = "Moderate + - Internal Change Lead requested for change strategy and deliverable " Then
        Sheets("Change Needs Assessment").Rows("65:68").EntireRow.Hidden = True
    ElseIf c.Value2 = "None - The project does not believe Change Management is required" Then
        Sheets("Change Needs Assessment").Rows("65:68").EntireRow.Hidden = True
    End If
End If

 

[Fluff]

Hia & welcome to the board
Untested but try adding this to the top of your code

If Not Target.Address = ("$C$62") Then Exit Sub

 

[supercharger]

Welcome to the Board!

Try putting this code in the Worksheet_Change event procedure of the sheet that the drop-down box appears in.

Also, I am not a big fan of multiple ElseIF statements. I find them a bit cumbersome to read, and repetitive in this case. Use CASE statements is much cleaner, i.e.

Private Sub Worksheet_Change(ByVal Target As Range)

        If Target.Address = ("$C$62") Then
            Select Case Target.Value
                Case "Minimal with External/Contract Change Resource - Advisory and material review"
                    Sheets("Change Needs Assessment").Rows("65:68").EntireRow.Hidden = False
                Case "Moderate + - External/Contract Change Lead requested for change strategy and deliverable "
                    Sheets("Change Needs Assessment").Rows("65:68").EntireRow.Hidden = False
                Case ""
                    Sheets("Change Needs Assessment").Rows("65:68").EntireRow.Hidden = True
                Case "Minimal with Internal Strategic Change Resource- Advisory and material review"
                    Sheets("Change Needs Assessment").Rows("65:68").EntireRow.Hidden = True
                Case "Moderate + - Internal Change Lead requested for change strategy and deliverable "
                    Sheets("Change Needs Assessment").Rows("65:68").EntireRow.Hidden = True
                Case "None - The project does not believe Change Management is required"
                    Sheets("Change Needs Assessment").Rows("65:68").EntireRow.Hidden = True
            End Select
        End If

End Sub

Since you are either hiding or unhiding the same group of rows, there is really only two possible outcomes, so you could, in theory reduce it down to just two checks, but since your descriptions are so long, that might be a bit jumbled and harder to read.

See here for more on Case statements: MS Excel: How to use the CASE Statement (VBA)

This worked. As I said, I'm pretty noobish at VBA and Java. I only write what I have to and rarely step outside of what I know, so it's often not the most efficient, but usually gets the job done.

In response to the other questions, stepping in to the debug, the error throws at the first line (If Target.Address...). I wasn't clearing multiple fields, either. If I simply deleted content in, say A2, it would give me the Run Time error. Don't really understand why.

Thanks everyone for the quick responses. I'd love to understand why this happened, but have a lot more work on this sheet, so I have to move on. Don't worry...I'm sure my next script will be botched, too...stay tuned for the next thread!

 

[supercharger]

Hia & welcome to the board
Untested but try adding this to the top of your code

If Not Target.Address = ("$C$62") Then Exit Sub

I'll try this on one of my drafts once I get past this project. Curious if that would've solved it, as it's a simple solution to my pragmatic approach to coding this!

 

[supercharger]

Sorry, I'm missing a case for if it's a null value. In the current state, if I select an option where Hidden=False and then go back and delete the option, the rows remain visible. I tried just writing an additional command to @Joe4 code: Case "" with Hidden=True, but it didn't seem to work.

 

[Joe4]

If there are no other drop-down options other than the ones you listed, then make it your last one and use "Case Else".
Then everything else will fall into that bucket.

 

[supercharger]

If there are no other drop-down options other than the ones you listed, then make it your last one and use "Case Else".
Then everything else will fall into that bucket.

Ah, duh. I should've thought of that! Thanks, again!