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

[patsfan1]

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.

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]

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]

Have you tried using the reverse approach ?

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

 

[patsfan1]

Have you tried using the reverse approach ?

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]

how many of these textboxes do you have ?

 

[dmt32]

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

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

 

[patsfan1]

Have you tried using the reverse approach ?

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]

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

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]

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

   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