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

Sarahd1

New Member
Joined
Mar 15, 2022
Messages
14
Office Version
  1. 365
Platform
  1. Windows
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!!
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
74,437
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
Which line of code gives the error?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,898
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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

New Member
Joined
Mar 15, 2022
Messages
14
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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
 

Attachments

  • Code in worksheet (sheet 1).PNG
    Code in worksheet (sheet 1).PNG
    198.4 KB · Views: 8
  • Code in worksheet but added module.PNG
    Code in worksheet but added module.PNG
    160.9 KB · Views: 9

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,898
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Mar 15, 2022
Messages
14
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,898
Office Version
  1. 365
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,255
Messages
5,852,917
Members
431,531
Latest member
jgottlieb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top