VBA Code Order help

Noz2k

Well-known Member
Joined
Mar 15, 2011
Messages
688
What I need to do is add this code

Code:
If CDate(txtDateOfAccident.Value) > Format(Date, "dd/mm/yyyy") Then
    imgCross2.visible = False
End If

In order to check that the date entered is not in the future, to this code which has various other arguments within it.

Code:
Private Sub txtDateOfAccident_Change()
 If Not txtDateOfAccident.Value Like "##[/]##[/]####" Then
 
        imgCross2.Visible = True
 
'Specifies the format of the textbox
 
    Else:
 
    If Mid(txtDateOfAccident.Value, 4, 2) < 13 And Mid(txtDateOfAccident.Value, 4, 2) > 0 Then
 
            If Mid(txtDateOfAccident.Value, 4, 2) = "04" Or Mid(txtDateOfAccident.Value, 4, 2) = "06" Or Mid(txtDateOfAccident.Value, 4, 2) = "09" Or Mid(txtDateOfAccident.Value, 4, 2) = "11" Then
 
                If Left(txtDateOfAccident.Value, 2) > 30 Or Left(txtDateOfAccident.Value, 2) < 1 Then
                    imgCross2.Visible = True
 
                    Else: imgCross2.Visible = False
 
                End If
 
 'Checks if the month of the date is a 30 day month, and if valid removes image
 
        Else:
 
            If Mid(txtDateOfAccident.Value, 4, 2) = "02" Then
 
                If ((Mid(txtDateOfAccident.Value, 7, 4) \ 4) * 4) = ((Mid(txtDateOfAccident.Value, 7, 4) / 4) * 4) Then
 
                    If Left(txtDateOfAccident.Value, 2) > 29 Or Left(txtDateOfAccident.Value, 2) < 1 Then
                        imgCross2.Visible = True
 
                        Else: imgCross2.Visible = False
 
                    End If
 
  'Checks if the month of the date is Febuary and what to do if it is a leap year, and if valid removes image
 
                Else:
 
                       If Left(txtDateOfAccident.Value, 2) > 28 Or Left(txtDateOfAccident.Value, 2) < 1 Then
                        imgCross2.Visible = True
 
                    Else: imgCross2.Visible = False
                    End If
 
 
                End If
 
  'Specifies what to do if it's not a leap year, and if valid removes image
 
             Else:
 
 
 
                If Left(txtDateOfAccident.Value, 2) <= 31 And Left(txtDateOfAccident.Value, 2) > 0 Then
                imgCross2.Visible = False
 
             Else:  imgCross2.Visible = True
 
   'Specifies what to do if the month is a 31 day month, and if valid removes image
 
        End If
        End If
        End If
 
    Else:   imgCross2.Visible = True
 
  'Specifies that the month value must be between 1 and 12
 
    End If
    End If
End Sub

I can't figure out where to put it in, or if indeed it's needed multiple times. Wherever I seem to try it I seem to either break the code or negate one of the other arguments.

Any help would be greatly appreciated
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,463
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I really wouldn't use a Change event for this since it fires with every keystroke, which is unnecessary, but you could simplify to:
Code:
Private Sub txtDateOfAccident_Change()
If IsDate(txtDateOfAccident.Value) then
   imgCross2.visible = (CDate(txtDateOfAccident.Value) <= Date)
Else
   imgCross2.Visible = True
End If
End Sub
 

Noz2k

Well-known Member
Joined
Mar 15, 2011
Messages
688
Thanks, I have now simplified the code to

Code:
Private Sub txtDateOfAccident_Change()
 
  If Not IsDate(txtDateOfAccident) Then
      imgCross2.Visible = True ' stop the user moving to the next control
   Else
       imgCross2.Visible = False
    
  End If
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,530
Messages
5,625,362
Members
416,096
Latest member
forevans

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