Worksheet Change not doing anything

Hi,

I'm sorry in advance because other people have asked similar questions and I've been through them and tried to apply the help given to my situation without success.

I am putting together a mini-database of information and I have a sheet that I want to update automatically every time a change is made, therefore I've set up a few lines telling the sheet what I want it to do as a Worksheet_Change sub (you'll see below):

Private Sub Worksheet_Change(ByVal Target As Range)
Call EnableEvents




Dim i As Integer




For i = 15 To 39
    If Sheets("Email Check").Range("C12").Value = "" Then
    
    ElseIf Sheets("Email Check").Range("D" & i).Value = "" Then
    Else
        Sheets("Email Check").Range("F" & i).Value = Sheets("Email Check Workings").Range("F" & i).Value
        With Sheets("Email Check").Range("C" & i)
            .Value = Sheets("Email Check Workings").Range("C" & i).Value
            .HorizontalAlignment = xlRight
        End With
        Sheets("Email Check").Range("H9").Value = i
    End If
Next i




End Sub

I don't think I've disabled the events, but as you can see the first thing I'm doing is enabling the events - just as a precaution.
There is data in cell C12 and D15 so the Else part of the if procedure should run. The Email Check Workings sheet is "veryhidden" but the cell F15 on that sheet fills automatically dependent on what is cells C12 and D15 of the Email Check sheet.

I've even put a check in there telling it to put the value of i into cell H9 - just to see if the whole procedure is running - and that is not happenning.

I've been through step by step several times and I just can't see it.

If anyone can help I would be most grateful.

Many thanks,

Red Smurf

Re: Worksheet Change not doing anything

It's a little tricky because it's part of a much larger project with hundreds of client details (about 3.5MB). I've tried to cut the bit out with the problem together with the other sheets which affect the results and I can't get the size down below the 205KB max and include all the sheets, so I've attached only the problem page and the page it feeds from directly There's a few sheets which work in conjunction with the one I'm having the problem with. A Menu sheet, Email Check sheet (this is the problem one), Email Check Workings - no macros here just excel formulas which are working perfectly, Classic Members which is just data, and City members which is just dataThere is a button on the Menu sheet to take you to the Email Check sheet. Here's the code for the button:

Sub Email_Check_Page()Application.ScreenUpdating = FalseSheets("Email Check").Visible = xlSheetVisibleFor Each ws In ActiveWorkbook.Worksheets     ''''''''''to hide everything except the sheet I want to go to    If ws.Name = "Email Check" Then    Else    ws.Visible = xlSheetVeryHidden    End IfNext wsSheets("Email Check").Range("D15:D39").Value = ""    '''''''''to clear the cells on the newly opened sheet Sheets("Email Check").Range("C12").Value = ""          '''''''''every time the sheet is openedSheets("Email Check").Range("F15:F39").Value = ""Call EnableEvents                                                  ''''''''''''small macro to ensure events are enabled  Application.ScreenUpdating = TrueEnd Sub

Then the problem page opens and you use a dropdown menu to select with City or Classic in cell C12 enter numbers in the cells in column D. If there's a number in column D then cells F on the same line should fill automatically - but this doesn't work Any help would be great - many thanks,

Re: Worksheet Change not doing anything

Many thanks Holger, but neither method has worked. I used the intersect as follows:

Private Sub Worksheet_Change(ByVal Target As Range)




Dim i As Integer




For i = 15 To 39
    If Sheets("Email Check").Range("C12").Value = "" Then
    
    ElseIf Not Intersect(Target, Range("D15:D39")) Is Nothing Then
        Application .EnableEvents = False
        Sheets("Email Check").Range("F" & i).Value = Sheets("Email Check Workings").Range("F" & i).Value
        With Sheets("Email Check").Range("C" & i)
            .Value = Sheets("Email Check Workings").Range("C" & i).Value
            .HorizontalAlignment = xlRight
        End With
        Sheets("Email Check").Range("H9").Value = i
        Application .EnableEvents = True
    End If
Next i




End Sub

I've tried taking out the reference to cell C12 as well and that doesn't work either - my check line re putting the value of i in H9 is not being actioned either which makes me suspect that the whole procedure isn't starting at all. Do you have any other ideas?

Many thanks,

Red Smurf

Re: Worksheet Change not doing anything

Hi,

No luck with either unfortunately.

In my file the sheet that's feeding my problem sheet is veryhidden - would that make a difference?

Many thanks,

Re: Worksheet Change not doing anything

Hi,
Just tried - it's doesn't make any difference at all.
Thanks for the thought though
Red Smurf

Re: Worksheet Change not doing anything

Quote from KjBox;720198

Try running the code with the "Email Check" sheet visible, If that works then you are going to have to include code to unhide the sheet, then make it Very Hidden again after it has been updated.

Another possible cause could be the sheet name. Make sure there is not a leading or trailing space in the sheet name. If there is then Sheets("Email Check") would not find the sheet. This is a good reason for using Sheet CodeName in code rather than Sheet name or sheet index.

Many thanks - just double checked, and copy pasted the sheet names from the sheet tabs to the code - unfortunately no success

Re: Worksheet Change not doing anything

Hi,
I've just been doing something else on the file on a different, unrelated sheet and I've noticed that the Worksheet_Change on that sheet isn't working either - this would suggest to me that the problem is something to do with the events not being enabled- even though I've said specifically that they should be. Does that help at all?

I've just put a few checks in place to write "check" in specific cells at various points of the code - hoping to isolate where the problem is in the code. What I've found is the Worksheet_Change code is not running at all. Is this to do with the EnableEvents?

Many thanks
Red Smurf