If Textbox.value ="", Then Go to next line of code, if not do this

patsfan1

New Member
Joined
Jul 30, 2019
Messages
11
Hello,
I am attempting to run code in a user form that determines the difference between two dates (in two text boxes) and place that number in a third textbox. I am doing this for a large number of textboxes on the form. I want the code to check if either of the two date text boxes is blank, and if so, just move to the next line of code. I have "Then Exit Sub" in their now, which is not what I need as it stops the code and exits once it reaches an empty date textbox. I am just starting out with VBA so I'm not sure how to handle this. Any help is appreciated. Also, if there is an easy way to do this as a range of textboxes and shorten the code that would be amazing, but I'll take any help.

Code:
If TextBox512.Value = "" Or TextBox497.Value = "" Then Exit Sub
TextBox573.Value = DateDiff("m", TextBox497, TextBox512) & " Mo"
 
If TextBox513.Value = "" Or TextBox498.Value = "" Then Exit Sub
TextBox574.Value = DateDiff("m", TextBox498, TextBox513) & " Mo"
 
If TextBox514.Value = "" Or TextBox499.Value = "" Then Exit Sub
TextBox575.Value = DateDiff("m", TextBox499, TextBox514) & " Mo"
 
If TextBox515.Value = "" Or TextBox500.Value = "" Then Exit Sub
TextBox576.Value = DateDiff("m", TextBox500, TextBox515) & " Mo"'........
 

patsfan1

New Member
Joined
Jul 30, 2019
Messages
11
I want the code to check if either of the two date text boxes is blank, and if so, just move to the next line of code.
Actually, if either date textbox is blank, I want the date diff textbox to be blank as well.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,314
Office Version
2013
Platform
Windows
Have you tried using the reverse approach ?

Code:
If TextBox512.Value <> "" And TextBox497.Value <> "" Then 
TextBox573.Value = DateDiff("m", TextBox497, TextBox512) & " Mo"
 

patsfan1

New Member
Joined
Jul 30, 2019
Messages
11
Have you tried using the reverse approach ?

Code:
If TextBox512.Value <> "" And TextBox497.Value <> "" Then 
TextBox573.Value = DateDiff("m", TextBox497, TextBox512) & " Mo"
Yes, I have tried that. It still stops doing the date diff calc once it comes to a date textbox that is empty. Do I next to add some sort of "Next" or "Else" statement to get it to move to the next line of code?

For context, I am putting this code in the user form initialize and activate sub to have the datediff values reflected when the userform is opened. The actual textboxes with dates are populated via control source property linked to a sheet.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,314
Office Version
2013
Platform
Windows
how many of these textboxes do you have ?
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,356
Office Version
2013
Platform
Windows
Hi,
If you have many textboxes then rather than write many lines of code you could create looping code to do what you want

Untested but try following


Code:
Dim i As Integer
For i = 512 To 515
    If IsDate(Me.Controls("TextBox" & i).Value) And _
    IsDate(Me.Controls("TextBox" & i - 15).Value) Then
    
        Me.Controls("TextBox" & i + 61).Value = _
        DateDiff("m", DateValue(Me.Controls("TextBox" & i - 15).Value), _
        DateValue(Me.Controls("TextBox" & i).Value)) & " Mo"
    
    End If
Next i

suggestion is based on the textbox naming conventions you published & assumes rest of your textboxes are named in same manner.

Dave
 
Last edited:

patsfan1

New Member
Joined
Jul 30, 2019
Messages
11
Have you tried using the reverse approach ?

Code:
If TextBox512.Value <> "" And TextBox497.Value <> "" Then 
TextBox573.Value = DateDiff("m", TextBox497, TextBox512) & " Mo"

So I was able to get this to work using this approach in the initialize user form sub. For my purposes, I also wanted it to display the date after the date textboxes were updated in the user form, so I ended up adding an AfterUpdate() code for each date textbox: "IF Textbox.value = "" Then Exit, textbox.value = date diff"

Thank you for your help!
 

patsfan1

New Member
Joined
Jul 30, 2019
Messages
11
Hi,
If you have many textboxes then rather than write many lines of code you could create looping code to do what you want

Untested but try following


Code:
Dim i As Integer
For i = 512 To 515
    If IsDate(Me.Controls("TextBox" & i).Value) And _
    IsDate(Me.Controls("TextBox" & i - 15).Value) Then
    
        Me.Controls("TextBox" & i + 61).Value = _
        DateDiff("m", DateValue(Me.Controls("TextBox" & i - 15).Value), _
        DateValue(Me.Controls("TextBox" & i).Value)) & " Mo"
    
    End If
Next i

suggestion is based on the textbox naming conventions you published & assumes rest of your textboxes are named in same manner.

Dave
Thank you! I will try this, definitely more efficient than my current approach!
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,356
Office Version
2013
Platform
Windows
Thank you! I will try this, definitely more efficient than my current approach!
Most welcome hope suggestion helps but will need to adapt to meet specific project need

I posted idea off top of my head but could be tidied a little

Code:
   Dim i As Integer
    Dim Date1 As Variant, Date2 As Variant
    
    For i = 497 To 500
    
        Date1 = Me.Controls("TextBox" & i)
        Date2 = Me.Controls("TextBox" & i + 15).Value
    
         With Me.Controls("TextBox" & i + 76)
            If IsDate(Date1) And IsDate(Date2) Then
                .Value = DateDiff("m", DateValue(Date1), DateValue(Date2)) & " Mo"
            Else
                .Value = ""
            End If
        End With
    Next i
maybe....

Dave
 

Forum statistics

Threads
1,085,714
Messages
5,385,409
Members
401,943
Latest member
xvpnkr

Some videos you may like

This Week's Hot Topics

Top