Selected Answer
You are headed for a serious management problem. I urge you to invest time to give useful names to your forms, controls and variables so that you can concentrate on programming. For example 'CmdCancel' will tell you more than 'Commandbutton1'.
With that said, you need to understand the sequence of actions and then take control of them. You can't have control if you want VBA to do everything automatic, and you can't let VBA do everything automatic in a project as complex as the one you are building. Please understand the sequence.
- You create a Form (as an object)
- Now the form exists. You can access it, but you can't see it.
- The Initialise event occurs. You still can't see it, but it exists.
- You can write to the form or read from it.
- The Show command makes it visible. Now the controls on the form control everything. The code that called the form is waiting.
- The Hide command makes it invisible.You can still access it. (read/write)
- Unload deletes the form from memory.
- Set Form variable = Nothing clears the memory of the variable which used to hold the form which is already unloaded.
So, first of all you need to have a variable to hold your form. Create this in your click event, for example (please excuse the horrible naming - not my doing :-))
Private Sub CommandButton2_Click()
Dim Form1 As UserForm1
Set Form1 = New UserForm1 ' initialise event occurs
With Form1
.TextBox1.Text = Sheet4.Cells(5, "A").Value
.Show
' the remainder of the code runs when you Hide the form:
Sheet4.Cells(5, "B").Value = .TextBox2.Text
End With
Unload Form1
Set Form1 = Nothing
End Sub
Observe that the above code writes a value to TextBox1 after initialisation, meaning the Change event for TextBox1 will occur (you can more easily turn off events during initialisation). Instead of writing at this point you might do the same thing in the Initialise procedure or some event provedure or skip the procedures and do everything at this point.
The code gives control to the form on 'Show" and resumes when the form is hidden. The line of code reading from TextBox2 and writing to the sheet will run after the 'Hide' command. Your code uses 'Unload Me' instead of 'Hide' thereby killing this option. You meed to change that. Use 'Me.Hide' instead. But that line of code will run regardless of whether you pressed OK or Cancel. I usually set a flag in one of the Tag properties to indicate which button was pressed.
I took the liberty to modernise your initialse event. I hope this will give you some ideas. If this post does help you, please be sure to select it as a useful answer.
Private Sub UserForm_Initialize()
' all the form's event procedures are Private
Const StartDay As Integer = -4 ' first day is Today - StartDay
Dim CbxList() As String
Dim i As Integer
ReDim CbxList(14) ' reflecting -4 + 10 (+0)
For i = 0 To UBound(CbxList)
CbxList(i) = Format(Date + i + StartDay, "dd/mm/yyyy")
Next i
With ComboBox7
.List = CbxList
.ListIndex = 4 ' today's date (?)
End With
With ComboBox2
.List = Split("Walk in,Email,Telephone,Patrol", ",")
.MatchEntry = fmMatchEntryNone
.Value = "Select"
.MatchEntry = fmMatchEntryFirstLetter
End With
End Sub