Worksheet Change Event

[shawleigh17]

Is there a way to write a Worksheet_SelectionChange(ByVal Target As Range) event in module after creating a sheet in VBA? I constantly delete a sheet, then repopulate it with a new one that is empty, but I need to add some code that happens if they should change a particular cell. It worked when I ran it on a worksheet without refreshing, but as soon as I cleared and repopulated the sheet, it was gone. Is there a way to preserve this?

 

[stanleydgromjr]

shawleigh17,

This should get you started in the right direction:
Programming The VBA Editor - Created by Chip Pearson at Pearson Software Consulting LLC
This page describes how to write code that modifies or reads other VBA code.
http://www.cpearson.com/Excel/vbe.aspx


See nirvana's reply here:
http://www.mrexcel.com/forum/showthread.php?t=374183


Have a great day,
Stan

 

[shawleigh17]

Great, thanks!

-Shawna

 

[Colin Legg]

Much simpler would be just to have a hidden, empty worksheet template with the event handler code already written in it's class code module.

Rather than adding a new sheet, just copy the template and make the copy visible. Copying the template will copy the event handler code.

 

[RoryA]

Or use the Workbook_SheetChange event and check if it's the sheet you are interested in.

 

[Colin Legg]

Rory's implicitly picked up on something there -

You say that you are using the Worksheet_SelectionChange event handler to monitor if a particular cell is changed. You should be using the Worksheet_Change event handler...

 

[shawleigh17]

I tried doing that last week, but I couldn't figure out how to do it. I posted on here, but nobody saw it/ offered suggestions, so I was trying to find a way around it.

Here's the prior post:

I am trying to write code in a sheet whenever that sheet is changed, but I only want this sheet to be affected, and no others. This sheet is deleted/recreated with each run of the larger program, so I can't really have standing code in the sheet file. Is there a way to do this? I want the sheet to essentially do this: When I change a value in the "From Location" column (B), I want column A to update with the vlookup value.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If (Sh = Workbooks("WAT_File_Existence_Test.xls").Sheets("Print_Sheet")) Then
        Workbooks.Open ("D:\Personal\O&S_Report.xls")
        Sheets("SKU_Change_Plan").Visible = True
        Sheets("SKU_Change_Plan").Unprotect ("fritoIPM")
        ActiveCell.Offset(0, -1).FormulaR1C1 = "l;l,"
        Dim O_S_Value As Boolean
        O_S_Value = ThisWorkbook.Sheets("Velocity").Range("A9")
        
        If O_S_Value = "True" Then
            Descrp = Application.WorksheetFunction.VLookup(ActiveCell.Value, Workbooks("O&S_Report.xls").Sheets("SKU_Change_Plan").Range("All_Data"), 4, False)
        Else
            Descrp = Application.WorksheetFunction.VLookup(ActiveCell.Value, Workbooks("O&S_Report.xls").Sheets("SKU_Change_Plan").Range("All_Data"), 4, False)
        End If
        
        Application.DisplayAlerts = False
        Workbooks("O&S_Report.xls").Activate
        Sheets("SKU_Change_Plan").Protect ("fritoIPM")
        ActiveWorkbook.SaveAs ("D:\Personal\O&S_Report.xls")
        ActiveWorkbook.Close
        Application.DisplayAlerts = True
    
    End If

End Sub

 

[shawleigh17]

Oh, nevermind, it looks like those are two different things. I'll look into the Worksheet_Change event handler.

 

[Colin Legg]

I think I have unintentionally confused you.

My point was that you said you were using
Worksheet_SelectionChange at the sheet level which would equate to Workbook_SheetSelectionChange at the workbook level.


Whereas, as Rory said, you should be using
Worksheet_Change at the sheet level which would equate to Workbook_SheetChange at the workbook level.


Hope that clarifies...

 

[shawleigh17]

Okay, how do I use the Workbook_SheetChange Event and check that I am on the right sheet?

I have tried, but I don't think that I am doing it right...