attaching a VB routine to a cell value change

[dcanham]

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

 

[Peter_SSs]

dcanham

Here is one way:
1. Right click the sheet tab and choose 'View Code'.
2. Paste the code below into the main right pane of the VBA window.
3. Enter your code where indicated.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Target.Address <> "$A$1" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    
    <SPAN style="color:#007F00">'*** Enter the rest of your code here ***</SPAN>
    
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[dcanham]

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
    End If

End Sub

 

[dcanham]

BTW: other than trying to change the name, I followed your suggestion. I'm not sure why there is a compile error.

 

[Peter_SSs]

dcanham

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?

 

[dcanham]

It highlights the sub header in yellow.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

 

[Jon von der Heyden]

Try changing:

    Rem *testing to see if current sales month is open 
    If Not WorkbookOpen(s_Filename1) Then 
       Workbooks.Open Filename:=ThisWorkbook.Path & "\" & s_Filename1 
    End If

To:

    Rem *testing to see if current sales month is open
    If Not Workbooks.Open(s_Filename1) = True Then
       Workbooks.Open Filename:=ThisWorkbook.Path & "\" & s_Filename1
    End If

 

[Norie]

dcanham

Do you actually have a sub/function called WorkbookOpen?

If you do is it meant to determine whether or not a workbook is open?

 

[dcanham]

yes I do That's what I get for copy and paste from another module. That did the trick.