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

populate date in textbox based on adjacent textbox

0

Hello

I try  populating date  automatically  in  column DATE  based  on  entering  adjacent  textbox in  column CODE  . currently  code  populate  for  all  of  textboxes  in column DATE  even  if  the  adjacent  textboxes for  the  rest of  rows  in  column code are  empty .

so  what  I  want to  populate  date  in  texbox  into  DATE   column based on  filling of  adjacent textbox in  CODE column for  each row separately   on userform   . if  the  others  rows contain empty  adjacent textboxes  , then  should  not  populate  date  in  DATE column  for  the  same  row based on adjacent  textbox is  empty then  .

Sub pop_date()
 Dim i, S As Long
    For i = 67 To 130 Step 7
        'first set of TextBoxes
        If Me.Controls("TextBox" & i) <> "" Then

        For S = 66 To 129 Step 7
        With Me.Controls("TextBox" & S)
            .Value = Format(Date, "dd/mm/yyyy")
        End With
        'second set of TExtBoxes
        Next S
       End If
    Next i

End Sub

Private Sub TextBox67_AfterUpdate()
Call pop_date
End Sub

thanks

excel
MS Office Version: 2019
posted by: Mussa (rep: 48) on Tue Feb 7, 2023 at 10:35 am
Answer
Discuss

Answers

0
Selected Answer

Hi Mussa

Given your Date textbox controls are numbered one less than the Code box (so TextBox66 is the Date for TextBox67) and 7 between Codes, just use one loop but when the Code box if not blank, set the date in the Textbox, as follows (changes in bold):

Sub pop_date()
 Dim i As Long
    For i = 67 To 130 Step 7
        'loop through first set of TextBoxes
        If Me.Controls("TextBox" & i) <> "" Then
            'if not blank, add date to second set of TextBoxes
            Me.Controls("TextBox" & i - 1).Value = Format(Date, "yyyy/mm/dd")
        End If
    Next i

End Sub


Private Sub TextBox67_AfterUpdate()
Call pop_date
End Sub

Once TB67 is updated, any (and only) filled Code boxes will have today's date against them. This code is in the revised file attached.

Hope this is what you wanted.

posted by: John_Ru (rep: 6142) on Wed Feb 8, 2023 at 3:04 am
Discuss

Discussion

BTW, do you realise that your original declaration:
Dim i, S As Long

only sets the datatype for S (Long)? i becomes the Variant (even though you want it to be Long). Better to write:
Dim i as Long, S As Long

even though your code doesn't really need to be memory-efficient.
John_Ru (rep: 6142) Feb 8, '23 at 3:10 am
Hi John,

I  thought  to  should  specify  two  loops , but you  use  one  loop .
BTW: I  think change   event  is  better than  afterupdate event and  when  clear  textbox67  should  also  clear date for  adjacent textbox like  this 
Else
            Me.Controls("TextBox" & i - 1).Value = ""
        End If

just  for  others  members  when  read  this thread.
thanks  very  much  for  your  guiding .
Mussa (rep: 48) Feb 8, '23 at 3:56 am
Thanks for selecting my Answer (again), Mussa. I felt that since one textbox depends on the value of another, only one loop is needed (plus an If test).
John_Ru (rep: 6142) Feb 8, '23 at 4:00 am
Add to Discussion


Answer the Question

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