Code for blanking cell when drop down list is changed in first cell

[Sarahd1]

Hi everyone. I keep getting an error (1004 - application-defined or object-defined error) that I can't fix. I tried moving the code I have from the worksheet to a module, that didn't work.
Situation; I have two groups of drop lists. B and C, and then D, E, F and G.
column B and C are drop down lists. C is dependent on the selection in column B.
Columns D, E, F and G are drop down lists. E is dependent on D, F dependent on E, G dependent on F.
When a selection is changed in column B, I need column C to go blank.
When the selection in D is changed, I need column E to go blank, and so on with the other dependent columns (F and G).
I will paste the code I am using below, but this is only for the second group of columns (D, E, F, G).

CODE FOR D, E, F and G.
Private Sub Worksheet_Change(ByVal Target As Range)
'Update by Extendoffice 2018/06/04

Application.EnableEvents = False
If Target.Column = 4 And Target.Validation.Type = 3 Then
Target.Offset(0, 1).Value = ""
End If
Application.EnableEvents = True

Application.EnableEvents = False
If Target.Column = 5 And Target.Validation.Type = 3 Then
Target.Offset(0, 1).Value = ""
End If
Application.EnableEvents = True

Application.EnableEvents = False
If Target.Column = 6 And Target.Validation.Type = 3 Then
Target.Offset(0, 1).Value = ""
End If
Application.EnableEvents = True
End Sub


When I add the text into the code for column B and C, I end up later getting the error I stated above. This is what the code looked like when I added in text for B and C

Private Sub Worksheet_Change(ByVal Target As Range)
'Update by Extendoffice 2018/06/04

Application.EnableEvents = False
If Target.Column = 2 And Target.Validation.Type = 3 Then
Target.Offset(0, 1).Value = ""
End If
Application.EnableEvents = True
Application.EnableEvents = False
If Target.Column = 4 And Target.Validation.Type = 3 Then
Target.Offset(0, 1).Value = ""
End If
Application.EnableEvents = True

Application.EnableEvents = False
If Target.Column = 5 And Target.Validation.Type = 3 Then
Target.Offset(0, 1).Value = ""
End If
Application.EnableEvents = True

Application.EnableEvents = False
If Target.Column = 6 And Target.Validation.Type = 3 Then
Target.Offset(0, 1).Value = ""
End If
Application.EnableEvents = True
End Sub


Can anyone help me with a new code please that wont give me this error?
Thank you!!

 

[Fluff]

Hi & welcome to MrExcel.
Which line of code gives the error?

 

[Joe4]

Welcome to the Board!

First, a few things:
1. Worksheet_Change event procedures (which are VBA code procedures that run automatically whenever a cell changes) MUST be placed in the appropriate Worksheet module. If you move them out to another "General" module, they will not fire automatically. Procedures in General modules are only run manually or when explicitly called by a button or some other procedure.
2. You can only have one procedure named "Worksheet_Change" per sheet (so if you have more to do, you always have to add it to the current procedure).
3. Rather than have all those "Application.EnableEvents" interspersed in your code, simply have one that shuts it off at the beginning, and one to turn it back on at the end.
4. When posting code, please use Code tags like I have. It makes your code much easier for us to read (see: How to Post Your VBA Code)

So, I think you just need to update your code like this:

Private Sub Worksheet_Change(ByVal Target As Range)
'Update by Extendoffice 2018/06/04

    Application.EnableEvents = False

    If Target.Column = 2 And Target.Validation.Type = 3 Then
        Target.Offset(0, 1).Value = ""
    End If

    If Target.Column = 4 And Target.Validation.Type = 3 Then
        Target.Offset(0, 1).Value = ""
    End If

    If Target.Column = 5 And Target.Validation.Type = 3 Then
        Target.Offset(0, 1).Value = ""
    End If

    If Target.Column = 6 And Target.Validation.Type = 3 Then
        Target.Offset(0, 1).Value = ""
    End If

    Application.EnableEvents = True
    
End Sub

 

[Sarahd1]

Welcome to the Board!

First, a few things:
1. Worksheet_Change event procedures (which are VBA code procedures that run automatically whenever a cell changes) MUST be placed in the appropriate Worksheet module. If you move them out to another "General" module, they will not fire automatically. Procedures in General modules are only run manually or when explicitly called by a button or some other procedure.
2. You can only have one procedure named "Worksheet_Change" per sheet (so if you have more to do, you always have to add it to the current procedure).
3. Rather than have all those "Application.EnableEvents" interspersed in your code, simply have one that shuts it off at the beginning, and one to turn it back on at the end.
4. When posting code, please use Code tags like I have. It makes your code much easier for us to read (see: How to Post Your VBA Code)

So, I think you just need to update your code like this:

Private Sub Worksheet_Change(ByVal Target As Range)
'Update by Extendoffice 2018/06/04

    Application.EnableEvents = False

    If Target.Column = 2 And Target.Validation.Type = 3 Then
        Target.Offset(0, 1).Value = ""
    End If

    If Target.Column = 4 And Target.Validation.Type = 3 Then
        Target.Offset(0, 1).Value = ""
    End If

    If Target.Column = 5 And Target.Validation.Type = 3 Then
        Target.Offset(0, 1).Value = ""
    End If

    If Target.Column = 6 And Target.Validation.Type = 3 Then
        Target.Offset(0, 1).Value = ""
    End If

    Application.EnableEvents = True
   
End Sub

Thank you! I have just applied this code and so far not getting any errors. And will do re code posting in the future, thanks
I did have my code pasted to the worksheet (sheet 1) rather than general, I think? I will post an image below of where I have it. I attempted to put it into a 'module' rather than the sheet but that wouldn't work for me before. I have added two images, one shows where I have the code (sheet 1) and another where I right clicked on sheet 1 and added a module, which appears below. Can you please advise if I should delete the module I just added, and keep the code where I have it (sheet 1) - or if I should move it to the module? Thanks again

 

[Joe4]

I refer back to point number 1 in my previous post.
This code NEEDS to be put in the sheet module of the sheet you want to apply it to in order for it to run automatically.
You do NOT want to put it in a General module.

 

[Sarahd1]

I refer back to point number 1 in my previous post.
This code NEEDS to be put in the sheet module of the sheet you want to apply it to in order for it to run automatically.
You do NOT want to put it in a General module.

Hi Joe, thanks for your reply.
I do have it posted in the worksheet (I think). I think the naming may be confusing me. You have said to place the code in the specific worksheet module, but what I am seeing on my screen isn't called a module (unless I right click on the name of the worksheet and press insert >module, which I believe creates a general module, which is not what I need, as you have said). If you see the screenshot above, I have the code placed in 'sheet 1 (operations_plan)', under 'Microsoft excel objects'. So this is what I believe is the specific worksheet. Are these terms interchangeable with 'worksheet module' Or is there a way to create a specific module for the worksheet, that I am missing?
Just want to confirm I have it in the right place to avoid that error again. I am not currently getting the error with it placed the location I specified. I am new to VBA codes.

 

[Joe4]

Every workbook has preset modules that you cannot create, delete, or rename. It will have one for the wokbook (called "ThisWorkbook"), and one for each sheet.
If you add new worksheets, new worksheet modules for these worksheets are automatically added.

You are correct, if you manually insert a module, this create a General module. Any code that you place it here will NOT run automatically, as they are not linked to any particular worksheet.

One easy way to make sure you are in the correct worksheet module is to go to that sheet in Excel, then right-click on the sheet tab name at the bottom of the screen, and select "View Code".
This will bring you into the Worksheet module for that worksheet.

Alternatively, you could just double-click on that sheet name in the VB Explorer under the "Microsoft Excel Objects" listing for that workbook.