excel16: vba, how choose date dd/mm/yyyy from combo box and update cells

Question

Pls help... In excel V16 forms, I've added combo boxes for date of entry & date of last payment set as DD / MM / YYYY defaulted with values: DD (01 to 31)/ MM (01 to 12)/ YYYY (2000 to 2020)

User can input a studentID and click on search its retrieve the data from sheet1 and fill in the form with the required values.

i'm Getting below issues: 1. after the search for existing record the combo boxes DD/ MM/YYYY are not being loaded with the data stored in cell Q date of entry & cell X date of last payment. 2. when updating the date (DD/MM/YYYY) data, it's not replicating in the cells Q & X.

Pls help Macros in https://drive.google.com/drive/folders/1k8UKQQOS8tWa7I-wucEgIA5ypN6MFPOq?usp=sharing

Codes for search & update btn:

Private Sub txt_date_Change()
txt_date.Text = cbo_dd.Value & "/" & cbo_mm.Value & "/" & cbo_yy.Value
End Sub

Private Sub cmd_search_Click()
Dim lastrow
Dim ref As String
Sheets("sheet1").Activate
lastrow = Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row
ref = txt_ref.Text
For currentrow = 2 To lastrow
If Cells(currentrow, 1).Text = ref Then

'txt_date.Text = Cells(currentrow, 17).Text
txt_datein.Text = Cells(currentrow, 17).Text   'date of entry 
txt_dateout.Text = Cells(currentrow, 24).Value 'date last payment
Exit For
End If
Next currentrow
txt_datein.Enabled = False
txt_date.Enabled = False
End Sub

Private Sub cmd_update_Click()
Dim ref As String, cir As String
Sheets("sheet1").Activate

datein = txt_datein.Text
Cells(currentrow, 17).Value = datein

dateout = txt_dateout.Text
Cells(currentrow, 24).Value = dateout

With Me.cbo_mm
.Clear
.AddItem "01"
.AddItem "02"
.AddItem "03"
.AddItem "04"
.AddItem "05"
.AddItem "06"
.AddItem "07"
.AddItem "08"
.AddItem "09"
.AddItem "10"
.AddItem "11"
.AddItem "12"
End With

With Me.cbo_yy
.Clear
.AddItem "2018"
.AddItem "2019"
.AddItem "2020"
End With

Call clear_all
End Sub

please insert your code into the actual question and remove the link. no one wants to (or probably will) click your link and open your file. — Scott Holtzman, Aug 15 '18 at 16:06

score 0 · Answer 1 · answered Aug 15 '18 at 16:42

0

Remove this from your UserForm1 code:

Private Sub txt_date_Change()

txt_date.Text = cbo_dd.Value & "/" & cbo_mm.Value & "/" & cbo_yy.Value

End Sub

answered Aug 15 '18 at 16:42

Kauê Delmonte

38
7

but this piece of code i've added as i hv created a new text box to be hidden in the form. It concatenate the combo box values selected. is there another way to do it selected values from DD / MM/YYYY combo updates the required cell? – Akika.YS Aug 15 '18 at 16:47
and when click on search btn, it should re-populate the combo box wz ze values from cells.. Any idea? – Akika.YS Aug 15 '18 at 17:01

excel16: vba, how choose date dd/mm/yyyy from combo box and update cells

1 Answers1