How to auto refill userform textbox ( auto number , curent date ) after clear ?

[Daniel Tou]

Hi ,
I have a file whit a user form created , whit auto number in text box 1 and current date in text box 2 which works well when open.My problem is that when clear button is applied on the form or after "ok" button is press ( and data entered) ,and clear form is applied to ad new data ,the auto number and current date it`s not filled in text box.
Can you help me whit a code for this ?

 

[royUK]

How do you calculate the auto number? Post the code.

 

[Daniel Tou]

How do you calculate the auto number? Post the code.

Private Sub UserForm_Initialize()
Me.TextBox1 = Application.WorksheetFunction.Max(Range("A:A")) + 1
Me.TextBox2.Text = Date
End Sub

 

[royUK]

Create a procedure to load the number and data then call it when needed

Option Explicit




Private Sub cmdClear_Click()
'code to clear
Loaddata
End Sub






Private Sub cmdOK_Click()
'code
Loaddata
End Sub


Private Sub UserForm_Initialize()
Loaddata
End Sub


Sub Loaddata()
Me.TextBox1 = Application.WorksheetFunction.Max(Range("A:A")) + 1
Me.TextBox2.Text = Date
End Sub

 

[Daniel Tou]

Create a procedure to load the number and data then call it when needed

Option Explicit




Private Sub cmdClear_Click()
'code to clear
Loaddata
End Sub






Private Sub cmdOK_Click()
'code
Loaddata
End Sub


Private Sub UserForm_Initialize()
Loaddata
End Sub


Sub Loaddata()
Me.TextBox1 = Application.WorksheetFunction.Max(Range("A:A")) + 1
Me.TextBox2.Text = Date
End Sub

I entered the code you given but nothing has happened , the text box does not fill.Sorry if i missed something, i`m new to VB.

 

[royUK]

Where have you put th code? it should be in the UserForm module

 

[Daniel Tou]

Where have you put th code? it should be in the UserForm module

I put the code in userform ( general section and userform initialize ) , but then i got this :

 

[royUK]

The error is because you have two initialize events which is not allowed. You need to delete the original code for the event.

 

[Daniel Tou]

The error is because you have two initialize events which is not allowed. You need to delete the original code for the event.

It`s not working.

Below the codes for command button 1 ( enter data , clear ) and cmd button 2 ( clear form ) :

Private Sub CommandButton1_Click()
Worksheets("Sheet1").Unprotect
Dim RowCount As Long
Dim ctl As Control
' Check user input
If Me.TextBox1.Value = "" Then
MsgBox "Please enter a Nr Crt.", vbExclamation, "Staff Expenses"
Me.TextBox1.SetFocus
Exit Sub
End If
If Me.TextBox2.Value = "" Then
MsgBox "Please enter a Data.", vbExclamation, "Staff Expenses"
Me.TextBox2.SetFocus
Exit Sub
End If
If Me.ComboBox1.Value = "" Then
MsgBox "Please choose a Document.", vbExclamation, "Staff Expenses"
Me.ComboBox1.SetFocus
Exit Sub
End If
If Me.TextBox3.Value = "" Then
MsgBox "Please enter a Comerciant.", vbExclamation, "Staff Expenses"
Me.TextBox3.SetFocus
Exit Sub
End If
If Me.TextBox4.Value = "" Then
MsgBox "Please enter a Alte detalii.", vbExclamation, "Staff Expenses"
Me.TextBox4.SetFocus
Exit Sub
End If
' Write data to worksheet
RowCount = Worksheets("Sheet1").Range("A1").CurrentRegion.Rows.Count
With Worksheets("Sheet1").Range("A1")
.Offset(RowCount, 0).Value = Me.TextBox1.Value
.Offset(RowCount, 1).Value = Me.TextBox2.Value
.Offset(RowCount, 2).Value = Me.ComboBox1.Value
.Offset(RowCount, 3).Value = Me.TextBox3.Value
.Offset(RowCount, 4).Value = Me.TextBox4.Value
End With
' Clear the form
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
Next ctl
Worksheets("Sheet1").Protect
End Sub

Private Sub CommandButton2_Click()
' Clear the form
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
Next ctl
End Sub

 

[royUK]

It won't work because you haven't used my code.

Add the loaddata procedure. Then call it at the end of the button code and in the initialize event as in my example code.