IF statement but in code

[Jock]

Hi,
I have code which will add a comment box two cells to the right whenever a
date is placed in column "N".
There is text within this comment box which is added by the code and takes
the date from "N" and adds 14 (days).
On occasion, I will need to overide the 14 days with a number from column
"M" which is manually input into that cell.

So, in eccence, I need someting like:
If (M5="",N5+14,N5+M5) but in the following code:

Dim strTemp As Date
'adds a comment box to cells in column P when date is entered in N
On Error Resume Next
If Not Intersect(Target, Me.Range("N4:N9999")) Is Nothing Then
With Target
If .Value <> "" Then
Application.EnableEvents = False
strTemp = Target.Offset(0, 3).Text
Target.Offset(0, 2).AddComment UserName() & " - AoS due by: " &
Format(Target.Value + 14, "dd mmm")
Target.Offset(0, 2).Comment.Shape.TextFrame.AutoSize = True
'On Error GoTo 0
Application.EnableEvents = True
End If
End With
End If


Any ideas???

 

[Jacob Skaria]

Try the below

If Trim(Target.Offset(0,-1)) = "" Then
Target.Offset(0, 2).AddComment UserName() & " - AoS due by: " & _
Format(Target.Value + 14, "dd mmm")
Else
Target.Offset(0, 2).AddComment UserName() & " - AoS due by: " & _
Format(Target.Value + Target.Offset(0,-1).Value , "dd mmm")
End If

If this post helps click Yes

 

[OssieMac]

Hello again Jock,

I assumed that it is a worksheet change event. Hope that is correct.

Not sure if Username is meant to be a variable that you created or the
Username of the workstation. I made it the username of the workstation.

Also it is preferrable to put the Application.EnableEvents = False at the
start of your code and re-enable at the end of the code. Plus it is advisable
to have the following code somewhere handy in the module so that if your code
fails at any time during testing before it gets to the code to re-enable.
Once events is turned off they stay off until restart Excel or enable with
code. Following sub can be run from within VBA editor by placing cursor
anywhere in the sub and press F5.

Sub Re_EnableEvents()
Application.EnableEvents = True
End Sub

Dim strTemp As Date does not look right considering where it is used in the
code. Is it a date or a string?

Anyway the following code should work but you might need to adjust the
Username if you are setting Username somewhere else in your code. However, if
you are setting it then change the variable to something other then Username
because it is a reserved word.

Note that a space and underscore at the end of a line is a line break in an
otherwise single line of code.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim strTemp As Date
'adds a comment box to cells in column P when date is entered in N
Application.EnableEvents = False
If IsDate(Target) Then 'Test if valid date.
MsgBox "IsDate"
On Error Resume Next
If Not Intersect(Target, Me.Range("N4:N9999")) Is Nothing Then
With Target
If .Value <> "" Then

strTemp = Target.Offset(0, 3).Text
Target.Offset(0, 2).AddComment Application.UserName _
& " - AoS due by: " & _
Format(Target.Value + 14, "dd mmm")

Target.Offset(0, 2).Comment.Shape. _
TextFrame.AutoSize = True

End If
End With
End If
End If
Application.EnableEvents = True
End Sub

 

[OssieMac]

Jock,

Drop the line MsgBox "IsDate"
I used it during testing.