Crashing UserForm

[Malteser_xl]

Hello!

I'm trying to make a form to reduce data inconsistency in the files.

So far the form mostly does what i want but it takes a long time for the form come back to initial settings, and it even crashes. I put Call UserForm1_Initialize at the end of the submit and to the clear button, whenever i have records in the form and press clear it becomes unresponsive and closes excel or takes a very long time to be cleared.

some of the other questions:

1. How can I make a search for the data as user types in the comboboxes as user types, not the whole found word when the first letter is typed e.g. company name field

2. How can I make company number to be chosen automatically when company name is chosen and vice versa? I tried to make it in a multiple column combobox but it doesn't work for me in this case. (because now the ID and Name can be inputted incorrectly as they are not linked and chosen separately)

3. I'm thinking to create another form and make it launch at the same time but when I create a multiple page form and insert this form on the page 1, all of the names are changed, is there a way to keep them? and when i manually change them to the original names the code still doesn't work, should I somehow indicate that its in the UserForm1 AND page 1 now?

4. How can I make Year calculate automatically? I simply wanted to insert a formula for the whole column like : =2016-G2 and fill all column, but then I won't be able to insert record with the userform as it searches for the emptyrow, is there a way to calculate it when the record is inserted from the userform?

I might be asking a lot but i couldn't find a solution for these things and i really want it to work properly.
Any help with the code improvement, suggestions or even referral to some websites are welcome as I'm new to this.

Here is the copy of the code, couldn't upload the file as it's too large (?):

Public Sub UserForm1_Initialize()
CompanyRef = ""
CompanyRef.Clear
CompanyRef.List = Sheet2.Range("A:A").value
ContactingOfficerInput.Clear
CompanyNameInput.value = ""
CompanyNameInput.Clear
' company name input is from the sheet 2 preset values
CompanyNameInput.List = Sheet2.Range("B:B").value
UnitNumber.value = ""
AgeInput = ""
SpinButton1 = "0"
Model = ""
AgeDisplay.value = SpinButton1.value
Comments.value = ""
ACType.Clear
' adding items to the AC type drop-menu
    With ACType
        .AddItem "Centralised AC Units"
        .AddItem "Reversible AC Split Units"
        .AddItem "Non-Reversible AC Split Units"
    End With

ContactingOfficerInput.Clear
' adding items to the value drop-menu
    With ContactingOfficerInput
    
        .AddItem "A.M. Grech"
        .AddItem "A. Asciac"
        .AddItem "R. Sacco"
        .AddItem "J. Zahra"
    
    End With

acSize.Clear
' adding items to the value drop-menu
    With acSize

        .AddItem "9,000"
        .AddItem "12,000"
        .AddItem "18,000"
        .AddItem "24,000"
        .AddItem "30,000"
        .AddItem "50,000"
    
    End With

End Sub

Private Sub AgeInput_Change()

OnlyNumbers

End Sub


' for the age of the AC
Private Sub SpinButton1_Change()
' shows maximum number
SpinButton1.Max = 50
' shows minimum number
SpinButton1.Min = 0
' shows the selected value from the spinbutton in the field next to it
AgeDisplay.value = SpinButton1.value
End Sub

Private Sub Submit_Click()

' Entry validation, mandatory field input.

If ContactingOfficerInput.value = "" Then
    MsgBox "You must complete the Contacting Officer field", vbCritical
    Exit Sub
End If

If ACType.value = "" Then
    MsgBox "You must complete the AC Type field", vbCritical
    Exit Sub
End If

If CompanyRef.value = "" Then
    MsgBox "You must complete the Company Reference field", vbCritical
    Exit Sub
End If

' initiate emptyRow
Dim emptyRow As Long

Sheet1.Activate
' finds a next empty row
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
' displays entered values into the next row
Cells(emptyRow, 2).value = CompanyRef.value
Cells(emptyRow, 3).value = CompanyNameInput.value
Cells(emptyRow, 4).value = ACType.value
Cells(emptyRow, 5).value = acSize.value


Cells(emptyRow, 9).value = UnitNumber.value
Cells(emptyRow, 8).value = SpinButton1.value
Cells(emptyRow, 17).value = Model.value
Cells(emptyRow, 1).value = ContactingOfficerInput.value
Cells(emptyRow, 21).value = Comments.value

' radio buttons
If KW.value = True Then
Cells(emptyRow, 6).value = "KW"
End If

If BTU.value = True Then
Cells(emptyRow, 6).value = "BTU"
End If

If HP.value = True Then
Cells(emptyRow, 6).value = "HP"
End If

Call UserForm1_Initialize

MsgBox "Your entry has been added"


End Sub

' number of units
Private Sub UnitNumber_Change()
OnlyNumbers
End Sub

'allows only numbers
Private Sub OnlyNumbers()


    If TypeName(Me.ActiveControl) = "TextBox" Then

        With Me.ActiveControl

            If Not IsNumeric(.value) And .value <> vbNullString Then

                MsgBox "You must input a number"

                .value = vbNullString

            End If

        End With

    End If



End Sub

Private Sub Clear_Click()

Call UserForm1_Initialize

End Sub

Private Sub Cancel_Click()

Unload Me

End Sub

______________________________________________________________

Mod edit : thread moved to appropriate forum !​