Assistance needed in Excel 2010

[Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

Would it be possible for someone to help me with a vba problem in Excel 2010? In cell B35 I'm using the =TODAY() function and in C35 there is a manually entered date that I change each month. What I need to happen is for a previously-created macro (Macro3) to run automatically if B35>=C35.
I would appreciate any help you can give me on this.
Thanking you in advance.

2 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
your question leaves few questions in mind, about is it required to prevent macro from firing multiple times etc However what ever the answer is, the solution would is two possible places.

put your when to fire logic in this method at workbook level
Private Sub Workbook_Open()

End Sub


or in this method for sheet level
Private Sub Worksheet_Change(ByVal Target As Range)

End Sub
Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
486
Hi Pianoman,

Try the following:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C35")) Is Nothing Then Exit Sub
If Range("B35").Value >= Range("C35").Value Then
'YOUR CODE HERE
End If
End Sub

Best regards,
Trowa
Thanks for trying to help but I still can't get the macro to fire off. Basically it's a security device; in other words if I don't personally update the target date and therefore the TODAY() function passes the target date then the first two columns of data will delete automatically. The data that will be affected is in Sheet1 so maybe I'm trying to put the code in the wrong place. Following your suggestion my macro code now looks like this:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C1943")) Is Nothing Then Exit Sub
If Range("B1943").Value >= Range("C1943").Value Then
Range("B4").Select
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 1090
ActiveWindow.ScrollRow = 1443
ActiveWindow.ScrollRow = 1437
ActiveWindow.ScrollRow = 1425
ActiveWindow.ScrollRow = 1398
ActiveWindow.ScrollRow = 1347
ActiveWindow.ScrollRow = 1294
ActiveWindow.ScrollRow = 1143
ActiveWindow.ScrollRow = 1151
ActiveWindow.ScrollRow = 1186
ActiveWindow.ScrollRow = 1188
ActiveWindow.ScrollRow = 1198
ActiveWindow.ScrollRow = 1227
ActiveWindow.ScrollRow = 1247
ActiveWindow.ScrollRow = 1255
Range("Final[[ Pip Code]:[Description]]").Select
Selection.ClearContents
End If

End Sub
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Here is the issue that I am not able to read clearly from your comments
1. Do you wish to delete data when you enter some value in C35 that is less than today
2. Do you wish to delete data when the date found in c35 becomes less than today.

I am guessing you are going for #2. If that is case, you cannot use Worksheet_Change event. The reason is that if a formula value changes, that will not trigger this event. The event is triggered if a value is changed, either manually or via code.

I think you are looking for Workbook_Open event. This event is triggered when you open the workbook and macros are allowed to run. At this point, you can call a routine that will do what you want to do.

Other possible places are sheet events Worksheet_Activate and Worksheet_Deactivate which are triggered when sheets is activated or deactivated respectively

Also a word of caution. Date is a bit tricky thing in excel. What may look like date may not be a date.

This is how i would suggest.
Insert a new module and paste the code (correct it to suit your requirements
Public Sub cleanStuff(Target As Range)

   'if range is not desired range then exit
   If Intersect(Target, Range("C1943")) Is Nothing Then Exit Sub
   
   'if value in the range is blank then exit
   If (Target = vbNullString) Then Exit Sub
   
   'if value in the reference range is blank then exit
   If (Range("B1943") = vbNullString) Then Exit Sub
   
   'check by formatting the dates as YYYYMDD
   If (Format(CDate(Range("B1943")), "YYYYMMDD") >= Format(CDate(Range("C1943").Value), "YYYYMMDD")) _
   Then
      'disable events
      Application.EnableEvents = False
      
      'not about your range, but intend is to clear out the range you are using
      Range("Final[[ Pip Code]:[Description]]").ClearContents
      
      'enable events
      Application.EnableEvents = True
   End If
End Sub



Now you can all this macro, from
Workbook_Open
event. This event needs to be coded in excel objects "ThisWorkbook"
as
Private Sub Workbook_Open()
   Sheets("Sheet1").Activate
   Call cleanStuff(Range("C1943"))
End Sub


or even like this in "Sheet1"
Private Sub Worksheet_Activate()
   Call cleanStuff(Range("C1943"))
End Sub