VBA Form TextBox needs formatting

SemperFi

New Member
Joined
Aug 21, 2014
Messages
22
Hi, I've read almost all the posts concerning formatting the VBA TextBox in a Form, however, none of the solutions worked for me.
I'm building a form with 6 fields for a cattle database, only having issues with the date field "TextBox5", which displays a number, not the date when I "recall" the entry to make an update. I've tried all sorts of formatting that I can think of, I'm new in VBA, but honestly I cannot figure this out, perhaps, you can help me solve this riddle.

The date displays correctly in the table once entered and saved and it is recalled correctly by the Form but does not display correctly inside the TextBox5 Field when I double click on any entry to make an update. Please see image below:

TextBox5 doesn't show date format when I double click on any entry in the database to make an update.
1692733756323.png


Note: Below image the date displays correctly when I type it and it pastes correctly into the table
1692734062910.png
1692734341485.png


This is the code thus far, I'm half way through coding, not done yet:

Private Sub CommandButton1_Click()
Me.ComboBox1.Value = ""
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""
End Sub

Private Sub CommandButton2_Click()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Datos")
Dim last_Row As Long
last_Row = Application.WorksheetFunction.CountA(sh.Range("A:A"))
'Validations---------------------------------------------------------------
If Me.ComboBox1.Value = "" Then
MsgBox "Seleccionar Sexo", vbCritical
Exit Sub
End If
'-----------
If Me.TextBox1.Value = "" Then
MsgBox "Poner Lomo-lote o NA", vbCritical
Exit Sub
End If
'-----------
If Me.TextBox2.Value = "" Then
MsgBox "Poner No. Reporte o NA", vbCritical
Exit Sub
End If
'-----------
If Me.TextBox3.Value = "" Then
MsgBox "Poner Raza o NA", vbCritical
Exit Sub
End If
'-----------
If Me.TextBox4.Value = "" Then
MsgBox "Poner Color o NA", vbCritical
Exit Sub
End If
'-----------
If Me.TextBox1.Value = "" Then
MsgBox "Poner Fecha Nacimiento o NA", vbCritical
Exit Sub
End If
'End Validation------------------------
'Añade nuevos datos a la base de datos -------------
sh.Range("A" & last_Row + 1).Value = "=Row()-1"
sh.Range("E" & last_Row + 1).Value = Me.ComboBox1.Value
sh.Range("B" & last_Row + 1).Value = Me.TextBox1.Value
sh.Range("F" & last_Row + 1).Value = Me.TextBox2.Value
sh.Range("D" & last_Row + 1).Value = Me.TextBox3.Value
sh.Range("C" & last_Row + 1).Value = Me.TextBox4.Value
sh.Range("G" & last_Row + 1).Value = Me.TextBox5.Value (note for MrExcel: This is the TextBox where the date is displaying wrong.
sh.Range("H" & last_Row + 1).Value = Now
'------------------------------
Me.ComboBox1.Value = ""
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""
'-----------------------------
Call Refresh_Data
End Sub

Private Sub CommandButton5_Click()
ThisWorkbook.Save
MsgBox "Los Datos se Guardaron"
End Sub
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'note: the numbers at the end is the column for each entry, remember the first column is 0, the second is 1 and so on---------
Me.ComboBox1.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 4)
Me.TextBox1.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 1)
Me.TextBox2.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 5)
Me.TextBox3.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 3)
Me.TextBox4.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 2)
Me.TextBox5.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 6)
End Sub

Private Sub UserForm_Activate()
With Me.ComboBox1
.Clear
.AddItem ""
.AddItem "Macho"
.AddItem "Hembra"
End With
Call Refresh_Data
End Sub

Sub Refresh_Data()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Datos")
Dim last_Row As Long
last_Row = Application.WorksheetFunction.CountA(sh.Range("A:A"))
With Me.ListBox1
.ColumnHeads = True
.ColumnCount = 8
.ColumnWidths = "40,80,80,80,80,90,90,90"
If last_Row = 1 Then
.RowSource = "Datos!A2:H"
Else
.RowSource = "Datos!A2:H" & last_Row
End If
End With
End Sub


Thank you really appreciate your help.


Pedro M
 

Attachments

  • 1692734318155.png
    1692734318155.png
    19.6 KB · Views: 8

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Please disregard this question, leaving the post up in case someone comes up with the same question. I found the answer within this forum:

 
Upvote 0
Solution

Forum statistics

Threads
1,224,809
Messages
6,181,075
Members
453,020
Latest member
mattg2448

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top