1

I've got some code that was developed in a tool I'm working on that performs some basic calculations for adding dates, etc. based on the change of another cell. It works fine in the sheet it was created for, however, when I copy it into another sheet module for a similar task (two extra columns added) it doesn't appear to be working. I've changed the references to account for the two extra columns, and I don't receive any error messages, it just doesn't do the calculations it's supposed to. I am a VBA novice and just can't seem to figure it out. Thanks in advance for the help!

Public Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo SelectError:
Oval = Target.Value
SelectError:
End Sub

Private Sub Worksheet_Change2(ByVal Target As Range)


If Not Intersect(Target, Range("G11:G202")) Is Nothing Then
On Error GoTo Eerror:

Application.ScreenUpdating = False
ActiveWorkbook.Unprotect Password:="Password"

        'Dim TarAddress As String


            Range(Target.Address).Select  ''''DO NOT DELETE, PREVENTS LOOP

If Range(Target.Address).Value <> 0 Then
Range(Target.Address).Offset(0, 1).Value = Date

'Update Budget

If Range(Target.Address).Offset(0, 5).Value = 0 Then

Range(Target.Address).Offset(0, 5).Value = Range(Target.Address).Value

End If

If Range(Target.Address).Value <> 0 Then
Range(Target.Address).Offset(0, 6).Value = Range(Target.Address).Value

End If

'Set Task Status to Not Started in Cancelled

If Range(Target.Address).Offset(0, 2).Value = "Cancelled" Then

Range(Target.Address).Offset(0, 2).Value = "Not Started"

End If


End If
Eerror:
ActiveWorkbook.Protect Password:="Password"
End If


If Not Intersect(Target, Range("I11:I202")) Is Nothing Then
On Error GoTo GError:
Application.ScreenUpdating = False
ActiveWorkbook.Unprotect Password:="Password"

        'Dim TarAddress As String


             Range(Target.Address).Select  ''''DO NOT DELETE, PREVENTS LOOP

'Mark Complete Date
If Range(Target.Address).Value = "Complete" Or Range(Target.Address).Value = "Complete w/ Issues" Then
Range(Target.Address).Offset(0, 2).Value = Date
Range(Target.Address).Offset(0, -2).Value = 0
End If
'Mark Start Date w/ Complete Date if Blank
If (Range(Target.Address).Value = "Complete" Or Range(Target.Address).Value = "Complete w/ Issues") And Range(Target.Address).Offset(0, 1).Value = "" Then
Range(Target.Address).Offset(0, 1).Value = Date
End If

'Mark Start Date
If Range(Target.Address).Value = "In Progress" And Range(Target.Address).Offset(0, 1).Value = "" Then
Range(Target.Address).Offset(0, 1).Value = Date
End If

'Mark Cancel Date
If Range(Target.Address).Value = "Cancelled" And Range(Target.Address).Offset(0, 8).Value = 1 Then
Range(Target.Address).Offset(0, 13).Value = Date
End If
GError:
ActiveWorkbook.Protect Password:="Password"
End If

End Sub

ETA: I don't know if it's because it's in a sheet vs a module, but it won't let me step through the code to try to track it. An example of what I'm expecting, is that if I put in a value greater than 0 in a cell in column G, it will update the next cell over with today's date, copy and paste that value into cells 5 and 6 columns over, and a few other things. It doesn't make any changes like this when I do put in a value, nor does it give an error message. The sheet just remains unchanged.

M. R.
  • 11
  • 4
  • What is not working? Have you tried adding break point in all subs and F8 through the code. Simple debugging should be done and included in the question. – Andreas Dec 31 '18 at 14:32
  • `Target` is a Range object; `Target.Address` is a String indicating the range object's address; `Range(Target.Address)` is the Range object that has that same address that the `Target` object has... which makes it equivalent to `Target' object we've started with. --- A very peculiar and entirely pointless circular transformation. No-no, this does not solve your problem, whatever it is, just noticing... –  Dec 31 '18 at 14:37
  • Hi Andreas, None of the calculations in the code are updating with the expected results. For instance, in the section that references column G. If I change the value here from 0 to another number, I'd expect it to populate today's date in the next cell over. Additionally it should populate the same number in two other cells offset from that one (5 over, and 6). It isn't doing any of these things. I've tried to toggle breakpoints and step through the code, but it doesn't seem to be letting me. – M. R. Dec 31 '18 at 14:38
  • 2
    Is that a typo or why is the name `Worksheet_Change2` instead of `Worksheet_Change`? – Storax Dec 31 '18 at 15:33
  • Storax, you just solved my problem. I had changed that originally thinking the issue was that it was named the same as another module. I changed it back and it works now...... Nothing else changed so that's weird but I'll take it! Thank you! – M. R. Dec 31 '18 at 15:40
  • Take the error handling out of the `Worksheet_SelectionChange` event and try it. Your code there is functionally equivalent to `On Error Resume Next`. – Comintern Dec 31 '18 at 15:46
  • @M. R.: Why should this be weird? Please have a look at the [Worksheet object events](https://learn.microsoft.com/en-us/office/vba/excel/concepts/events-worksheetfunctions-shapes/worksheet-object-events) – Storax Dec 31 '18 at 16:00
  • I originally didn't have the "2" there and it wouldn't work. So I added it. Then when you mentioned it and I removed the "2" it worked. The weirdness was it not working originally. Thanks! – M. R. Dec 31 '18 at 16:46

0 Answers0