Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

problem using function isdate & not isdate by vba

0

Hello experts

I  try  adding  word done  in column E . if  I  add  is not date  in column G  and  will pops  up  message box  I should  add date , if  I  add  isdate  in column G   should add DONE  into cells for  column E for  each  date into cells  for  column  G and  will pops  up  message box  , but  it  seems   to writing way  of  function  isdate  is  wrong .

Private Sub Worksheet_change(ByVal Target As Range)
If Target.Cells.CountLarge > 1 Then Exit Sub
If Not Intersect(Not IsDate(Target, Range("g:g"))) Is Nothing Then
       MsgBox ("Please enter valid date in column  : g" & RowCount & ". Example: m/dd/yyyy")
       End If
If Not Intersect(IsDate(Target, Range("g:g"))) Is Nothing Then
Target.Offset(, -2) = "done"
MsgBox "the date has been changed in cell" & " " & Target.Offset(, 2).Address & VBA.vbCrLf & " the  date is " & Target.Offset(, 2).Value, vbExclamation
End If
End Sub
Answer
Discuss

Answers

0
Selected Answer

Hi Speed

Try this (and the attached file). I've commented the code so you see what I've changed and why. Western users should note that the sheet uses right-to-left direction (which affects offset addressing):: 

Private Sub Worksheet_change(ByVal Target As Range)

' quit unless a single cell in changed in G
If Target.Cells.CountLarge > 1 Or Intersect(Target, Range("g:g")) Is Nothing Then Exit Sub

'set earliest date you'll accept (as a String)
Earliest = "01/01/2000"

' check if a date after that was added
If Not IsDate(Target.Value) Or Target.Value < DateValue(Earliest) Then
    MsgBox ("Please enter a valid date (after " & Earliest & ") in " & Target.Address(0, 0) & ". Example: m/dd/yyyy")

    Else
    ' if so
    Target.Offset(0, -2) = "done" '### offset is -2 since sheet uses right-to-left driection, not left-to-right
    MsgBox "The date has been changed in cell " & Target.Address(0, 0) & vbCrLf & "The date is " & Target.Value, vbExclamation

End If

End Sub

Note that I've used Target.Address(0, 0) which sets both row and column absolute addressing ($) to 0 (=False). I also set an Earliest date (which you can change) to prevent errors, since you could type 67 in column G and it would be displayed/ seen by VBA as a date (17 March 1990).

Hope this is what you want.

Discuss

Discussion

Great john !
I  wanted clearing   the  cells  when  writting  is not date   after pops  up  message 
I  added  after  message  but  the  message  continues  showing , any  idea ,please?
MsgBox ("Please enter a valid date (after " & Earliest & ") in " & Target.Address(0, 0) & ". Example: m/dd/yyyy")
    Target.Value = ""
speed (rep: 32) Jun 18, '22 at 8:48 am
Speed

You can't do that (without re-triggering the macro and getting the message).
Try turning events off before you do it (then on again after), like this:
If Not IsDate(Target.Value) Or Target.Value < DateValue(Earliest) Then
    MsgBox ("Please enter a valid date (after " & Earliest & ") in " & Target.Address(0, 0) & ". Example: m/dd/yyyy")
    Application.EnableEvents = False
    'set to nothing and return to cell
    Target.Value = ""
    Target.Select
    Application.EnableEvents = True
    Else
John_Ru (rep: 3992) Jun 18, '22 at 9:28 am
thanks for explanation & everything !
speed (rep: 32) Jun 18, '22 at 9:44 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login