I have a cell A1 that has a user entered date in it. When that cell value is changed (by the user) I want to trigger running a subroutine. How would I setup the equivalant of a ON_CHANGE trigger for a cell? Thanks
The below code gives me a 'compile error, sub or function not defined'. I know the sub name is different than your example but whe I got the error using the function name you used, I tried to use the default one that came up for the CHANGE.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address <> "$A$1" Then Exit Sub
' initializing variables
Dim d_Current_Date As Date
Dim s_Filename1 As String
Dim s_Filename2 As String
Dim s_Filename3 As String
Rem *setting date variable
d_Current_Date = Workbooks("BuffaloWeeklyView.xls").Names("ReportDate").RefersToRange.Value
Rem *current sales month
s_Filename1 = "\Sales\Sales_" + Format(d_Current_Date, "mmmm") + "_" + Format(d_Current_Date, "yyyy") + ".xls"
Rem *testing to see if current sales month is open
If Not WorkbookOpen(s_Filename1) Then
Workbooks.Open Filename:=ThisWorkbook.Path & "\" & s_Filename1
I am not an expert in de-bugging code and I am not that familiar with all the processes you are trying to use in your code. However, these questions may shed some light:
1. What line of the code is causing the message: 'compile error, sub or function not defined'?
2. Did your code work correctly when triggered manually? That is before you included it in a Worksheet_Change or Worksheet_SelectionChange event?