Selected Answer
Hello Leopard,
You only need to use the Initialize or Activate event to set the TextBox because you didn't create the form properly. It's better to create the form when you click the button and set the text box before you show it.
Private Sub CommandButton1_Click()
' 161
Const NumFmt As String = """Trade/ """ & "0000"
Const BaseNum As Integer = 1000
Const Target As String = "E2"
Dim Form As UserForm1 ' better to name the form
Dim InvNum As Long
InvNum = Val(ActiveSheet.Range(Target).Value) + 1
If InvNum < BaseNum Then InvNum = InvNum + BaseNum
Set Form = New UserForm1 ' Initialize event fires now
With Form
.TextBox1.Value = Format(InvNum, NumFmt)
.Show ' Activate event fires now
' code continues here when the form is closed:-
If Val(.Tag) = True Then ' check if 'OK' was pressed
With ActiveSheet.Range(Target)
.Value = InvNum
.NumberFormat = NumFmt
End With
End If
End With
Unload Form
Set Form = Nothing
End Sub
It's important to understand that the code gives control to the form with the Show command and until the form is hidden. After that it continues to run with the command after Show. At that time all of the form is still in memory. Only the display is hidden. So, you can take the value of the invoice number and write it to the sheet at that time.
The advantage of this is that you can avoid writing the number to the sheet if you cancelled the invoice. So, you need some message to send back to the calling procedure to tell it if it's OK to write the number to the sheet or not. To send this message you create a command button on the form. I called it CmdOK. When this button is clicked the code below runs. Note that it must be installed in Userform1's code module.
Private Sub CmdOK_Click()
' 161
With Me
.Tag = -1
.Hide
End With
End Sub
As you see, the code does only two things. It assigns a value of -1 to the form's Tag property, and then hides the form. Of course, once the form is hidden the code resumes below the Show command. There the Tag property's value is checked. If it's True (meaning -1) the new invoice number is written to the form.
Observe that the cell never gets the value of "Trade /1001". Its value is just 1001 and the word "Trade/ " is added in the number format, which is the same for the cell and the text box.
I attach a workbook in which all of the above is implemented.
To stop the code from failing when exited irregularly the following procedure must be added to the form's code module.
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
' 161
If CloseMode = vbFormControlMenu Then
Cancel = -1
With Me
.Tag = vbNullString
.Hide
End With
End If
End Sub