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"'........
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

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,399
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,399
Office Version
2013
Platform
Windows
how many of these textboxes do you have ?
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,480
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,480
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,089,470
Messages
5,408,439
Members
403,205
Latest member
Kennet3h

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top