I should have said, F4 is a dependent validation list from cell F5. When I select F4 and make a different selection, I want cell F5 to clear. Also, there is a bit of code in the sheet that selects cell A1 when you navigate to the tab.

Hi Paul

I have modified Philip's code for you.

I have unmerged the cells F4 and F5 as merged cells can cause unexpected problems.

I would suggest you name the cells F4 and F5 and refer these cells via their name in the macro to prevent issues when these cells move to another location.

Since this macro uses an event, it may cause the event checking to be disabled should the macro crash before the event could be enabled again. If this happens then you will need to enable the event manually by typing the Application.EnableEvents=True command via the immediate window.

Hope this helps.

Sunny

Thanks for this. It works great in the file you sent. I unmerged the cells in my source workbook, but when I copied your code to the sheet in my full workbook, and then changed the cell references in the code to correspond, it didn't work. It didn't crash either, it just didn't do anything. I don't know why this would be so.

Paul

Hi Paul

Without seeing your file, I can only make a guess. Some of the possibilities are:

1) You did not change the codes correctly.

2) You copied the codes to the wrong Worksheet module.

3) The code may have crashed and the Application.EnableEvents was not reset back to TRUE.

4) No idea if saving your file as a XLSB format can cause this problem as I always save as a XLSM format.

Hope this helps.

Sunny

Hi Sunny,

So if the code crashed then Application.EnableEvents would be false? I didn't try resetting Application.EnableEvents=True. What line would this be inserted? And should it be left there?

I'm using xlsb (binary) format because it accepts VBA and reduces file size by about half. Even in binary format, my file is at 24MB! I haven't had any trouble running code with binary format, and it never crashes.

Paul

Hi Paul

If you can attach your codes it would help.

Sunny

They're the same codes you've already seen, the one in which, upon selecting the tab, A1 is selected.

I just ran a little test. I copied the Settings sheet from the main file to a new workbook, and broke the links between that sheet and the others by copying and pasting the values over them. Then I copied your code into the module, made the two cell reference changes highlighted in red:

Private Sub Worksheet_Activate()
    Range("A1").Select
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

    ' Only execute this code if changed cell is F9
    If Target.Address <> "$F$9" Then
        Exit Sub
    End If
    
    Dim NewValue As Variant
    
    ' Turn off events and screen updating
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    
    NewValue = Target.Value
    Application.Undo
    
    ' If the old value is different to the new value
    If Target.Value <> NewValue Then
        ' Assign the Newvalue to the changed cell
        Target.Value = NewValue
        
        'Clear the required range
        Range("F10").ClearContents
    End If
    
    ' Re-enable events and screen updating
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
    
End Sub

And the code worked. I went back to the main file, and in the Settings sheet, I copied in the same code as above. But the code didn't work.

It seems to me that something in the main workbook is preventing the Settings sheet code from executing. Is this possible? Do I need to insert Application.EnableEvents=True in the code somewhere?

Paul

Hi Paul

I don't see anything wrong and have no idea why it is not working unless you copied the codes to the wrong worksheet module.

You can add the line of code in red after the below codes to test if the change had been detected.

If Target.Address <> "$F$9" Then
     Exit Sub
End If
MsgBox "Hello"

It will display a message box with the word Hello if the change in cell F9 have been detected.

There is no need to reset the event unless the code crashes. If that is the case then go to the VBA Immediate window and reset by entering Application.EnableEvents=True and then press ENTER.

It is also possible that there could be codes in your file that is preventing the changes from being detected. I can only assume that you have enabled your macros in Excel. Maybe you can also check if there are other worksheet change events in other sheets.

Can't think of any other reason as your other test files are OK.

Sunny

I added MsgBox "Hello" but nothing displayed when F9 was changed. However, none of the other code throughout the workbook now works, either. I guess it must have crashed. Now I need to reset with Application.EnableEvents=True. Where do I find the VBA Immediate window?

Paul

1) Press ALT+F11.

2) Click View - Immediate Window. It should appear at the bottom right of your screen.

3) Just type Application.EnableEvents=True and then press ENTER

Hi Paul

I suggest if possible to attach a small portion of your file. I don't think it crashed as it didn't detect the change at all.

Since you mentioned "However, none of the other code throughout the workbook now works, either", I assume you have other macros as well in the workbook.

This may be the cause of the problem.

Sunny,

I found the problem. I had entered the code and adjusted the cell references first, and then I unmerged cells F9 and F10. That caused the code to execute on the merged cells before I could unmerge them, and the code ceased working. So, I tried the reverse order: unmerge the cells first, and then put in the code. Success!

Thanks for your expert help and perseverance. I couldn't do it on my own.

Paul

Hi Paul

Glad to know you have managed to resolve the problem.

Cheers

Sunny