Vba inputbox excel

I have created this investor mail list for work purpose, and need to add some more functions:

I need to "lock" the inputbox, meaning that you have to fill out all the options otherwise an msgbox will occur "Please fill in all the options".

I also need to lock the entire spreadsheet - so it's only possible to add an investor to the mail list through the inputbox. It should only be possible to add an investor without the inputbox through the administrator

I have search all over the internet, and cannot find such functions

Help is much appreciated!

Private Sub OKButton_Click()
Dim emptyrow As Long

'Make sheet1 active
Ark1.Activate

'determine emptyrow
emptyrow = WorksheetFunction.CountA(Range("A:A")) + 1

'Transfer information
Cells(emptyrow, 1).Value = Email.Value
Cells(emptyrow, 2).Value = Bank.Value
Cells(emptyrow, 3).Value = FirstName.Value
Cells(emptyrow, 4).Value = Surname.Value
Cells(emptyrow, 5).Value = AddIn.Value
Cells(emptyrow, 6).Value = TypeComboBox.Value



If CheckBox1.Value = True Then Cells(emptyrow, 7).Value = CheckBox1.Caption

If CheckBox2.Value = True Then Cells(emptyrow, 7).Value = Cells(emptyrow, 7).Value & " " & CheckBox2.Caption

If CheckBox3.Value = True Then Cells(emptyrow, 7).Value = Cells(emptyrow, 7).Value & " " & CheckBox3.Caption

If CheckBox4.Value = True Then Cells(emptyrow, 7).Value = Cells(emptyrow, 7).Value & " " & CheckBox4.Caption

If CheckBox5.Value = True Then Cells(emptyrow, 7).Value = Cells(emptyrow, 7).Value & " " & CheckBox5.Caption

Unload Me

MsgBox "Investor successfully added"


End Sub

Private Sub UserForm_Click()

End Sub

Private Sub UserForm_Initialize()

'empty all textboxes

Email.Value = ""
Bank.Value = ""
FirstName.Value = ""
Surname.Value = ""
AddIn.Value = ""

TypeComboBox.Clear

'Fill dinnercombobox
With TypeComboBox
.AddItem "Bank"
.AddItem "Corporate"
.AddItem "DCM"
.AddItem "Fund Manager"
.AddItem "FSA"
.AddItem "Investor"
.AddItem "Insurance"
.AddItem "Magazine"
.AddItem "Other"
.AddItem "Pension Fund"
.AddItem "Rating agency"



End With

'uncheck wishbox
CheckBox1.Value = False
CheckBox2.Value = False
CheckBox3.Value = False
CheckBox4.Value = False

'set focus on email box
Email.SetFocus

End Sub

Solution

Here's how you'll force to fill in all the fields before they can press the OK button:

Private Sub Email_Change()

If Me.Email.Value <> "" And Me.Bank.Value <> "" And Me.FirstName.Value <> "" And _
    Me.Surname.Value <> "" And Me.AddIn.Value <> "" And Me.TypeComboBox.Value <> "" Then

    Me.OKButton.Enabled = True
    MsgBox "Some Fields are missing!"
Else
    Me.OKButton.Enabled = False
End If

End Sub

You put this code on Email Textbox Change event.
And you will need to put above code in all your Textboxes Change event.
Meaning, everytime Textboxes value changes, it will check if other Texboxes are empty or not.
The test I did is for empty, you can modify it if you have certain conditions that you want to meet.
Hope this gets you started.