message box if textbox is left blank but commandbutton still executes / locking cells after form is submitted

[Arthur Justice]

Hello,

Excel 2010

First issue:
- form still executes even if field is left blank
- I don't want the form to execute if certain boxes are blank
- I currently have a message box that pops up, but the other fields still execute
Private Sub submitbutton_Click()

Cells(emptyrow, 1).Value = Now()
Cells(emptyrow, 2).Value = nametxt.Value
Cells(emptyrow, 3).Value = phonetxt.Value
Cells(emptyrow, 4).Value = bldgtxt.Value
Cells(emptyrow, 5).Value = roomtxt.Value
Cells(emptyrow, 6).Value = codetxt.Value
Cells(emptyrow, 8).Value = resttxt.Value
Cells(emptyrow, 9).Value = descriptionlistbox.Value
Cells(emptyrow, 10).Value = explaintxt.Value
Cells(emptyrow, 11).Value = stafftxt.Value

If nametxt.Value = "" Then
MsgBox "Please Enter the Requestor's Name", vbCritical
Me.nametxt.SetFocus

Exit Sub
Else
' code if not empty
End If

Also, is there a way to lock the cells from editing after the form is submitted? I don't want the people using the form to be able to edit the data once it is on the worksheet.

Thank you in advance for your help.

 

[Arthur Justice]

I apologize. I was unable to edit my original post to make it more readable.

Hello,

Excel 2010 First issue:
- form still executes even if field is left blank
- I don't want the form to execute if certain boxes are blank
- I currently have a message box that pops up, but the other fields still execute
Private Sub submitbutton_Click()
Cells(emptyrow, 1).Value = Now()
Cells(emptyrow, 2).Value = nametxt.Value
Cells(emptyrow, 3).Value = phonetxt.Value
Cells(emptyrow, 4).Value = bldgtxt.Value
Cells(emptyrow, 5).Value = roomtxt.Value
Cells(emptyrow, 6).Value = codetxt.Value
Cells(emptyrow, 8).Value = resttxt.Value
Cells(emptyrow, 9).Value = descriptionlistbox.Value
Cells(emptyrow, 10).Value = explaintxt.Value
Cells(emptyrow, 11).Value = stafftxt.Value

If nametxt.Value = "" Then
MsgBox "Please Enter the Requestor's Name", vbCritical
Me.nametxt.SetFocus
Exit Sub
Else
' code if not empty
End If


Also, is there a way to lock the cells from editing after the form is submitted? I don't want the people using the form to be able to edit the data once it is on the worksheet. Thank you in advance for your help.

 

[NoSparks]

Give this a shot

Private Sub submitbutton_Click()

    Dim controlNames As Variant
    Dim i As Integer
    
'check form for blanks
controlNames = Array("nametxt", "phonetxt", "bldgtxt", "roomtxt", "codetxt", "resttxt", "explaintxt", "stafftxt")
For i = LBound(controlNames) To UBound(controlNames)
    If Len(Me.Controls(controlNames(i)).Text) = 0 Then
        MsgBox controlNames(i) & "  is blank."
        Me.Controls(controlNames(i)).SetFocus
        Exit Sub
    End If
Next i

'write the data
With Sheets("Your Sheet Name Here")
    .Unprotect
    .Cells(emptyrow, 1).Value = Now()
    .Cells(emptyrow, 2).Value = nametxt.Value
    '
    ' etc.
    '
    .Cells(emptyrow, 11).Value = stafftxt.Value
    'lock those cells
    .Range(Cells(emptyrow, 1), Cells(emptyrow, 11)).Locked = True
    .Protect
End With

End Sub

 

[Arthur Justice]

Give this a shot

Private Sub submitbutton_Click()

    Dim controlNames As Variant
    Dim i As Integer
    
'check form for blanks
controlNames = Array("nametxt", "phonetxt", "bldgtxt", "roomtxt", "codetxt", "resttxt", "explaintxt", "stafftxt")
For i = LBound(controlNames) To UBound(controlNames)
    If Len(Me.Controls(controlNames(i)).Text) = 0 Then
        MsgBox controlNames(i) & "  is blank."
        Me.Controls(controlNames(i)).SetFocus
        Exit Sub
    End If
Next i

'write the data
With Sheets("Your Sheet Name Here")
    .Unprotect
    .Cells(emptyrow, 1).Value = Now()
    .Cells(emptyrow, 2).Value = nametxt.Value
    '
    ' etc.
    '
    .Cells(emptyrow, 11).Value = stafftxt.Value
    'lock those cells
    .Range(Cells(emptyrow, 1), Cells(emptyrow, 11)).Locked = True
    .Protect
End With

End Sub

The code works awesome thank you very much.

One question I have about it is: instead of the error box saying "nametxt" and etc., is there a different way I the code needs to be wrote for putting "Requestor's Name is blank"

Thank you for your assistance.

 

[NoSparks]

This is not tested, but try...

msgbox "Requestor's  " & proper(left(controlnames(i),len(controlnames(i)-3))) & "  is blank"

 

[Arthur Justice]

Thank you for the reply. Unfortunately, the code did not work. However, instead of writing the code for each individual field, I change the message box, and now I will edit the form to show the user required fields.

'check form for blanks
controlNames = Array("nametxt", "phonetxt", "bldgtxt", "resttxt", "descriptionlistbox", "explaintxt", "stafftxt")
For i = LBound(controlNames) To UBound(controlNames)
If Len(Me.Controls(controlNames(i)).Text) = 0 Then
MsgBox "Please fill in required fields.", vbCritical
Me.Controls(controlNames(i)).SetFocus
Exit Sub
End If
Next I

Your help is greatly appreciated sir!