attaching a VB routine to a cell value change

dcanham

Active Member
Joined
Jun 7, 2006
Messages
306
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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,407
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Jun 7, 2006
Messages
306
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.


Code:
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

Active Member
Joined
Jun 7, 2006
Messages
306
BTW: other than trying to change the name, I followed your suggestion. I'm not sure why there is a compile error.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,407
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
Jun 7, 2006
Messages
306
It highlights the sub header in yellow.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Try changing:

Code:
    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:

Code:
    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

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Jun 7, 2006
Messages
306
yes I do :oops: That's what I get for copy and paste from another module. That did the trick. :rolleyes:
 

Forum statistics

Threads
1,136,269
Messages
5,674,741
Members
419,525
Latest member
helensesc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top