Member Avatar for visuallearner

Hello- I am new to Visual Basic and have done tutorials, as well as checked VB help and online to help me with this problem, but I can't figure it out. I am trying to make a 'Supervisor field' in an excel spreadsheet mandatory. If users do not complete this field then I want a Msg box to pop up indicating, "Supervisor field is mandatory for submission." I don't want to include a drop-down box, and I am trying to make it an If..Then statement, but I don't have it quite right. After they complete the form, then need to click on a 'Submit' button to submit the form to an email box set up. Any help would be appreciated.

  • 2 Contributors
  • 3 Replies
  • 206 Views
  • 20 Hours Discussion Span
  • Latest Post Latest Post by Ramy Mahrous

Dani AI

Generated

asked for the Supervisor field to be mandatory (showing "Supervisor field is mandatory for submission.") when the user clicks a Submit button, without using a dropdown. The simplest, most robust approach is to validate the field at the start of the Submit routine. The same check works whether the value is a worksheet cell, an on-sheet control (ComboBox), or a UserForm control; 's question about a ComboBox is relevant — replace the Range(...) check with the control's .Value when appropriate. Naming the supervisor cell (Name Box or Formulas → Define Name) as Supervisor makes the code clearer.

If the Supervisor entry is a cell (named "Supervisor"), place this in a standard module and assign it to the Submit button:

Public Sub SubmitForm()
    Dim sup As String
    sup = Trim(CStr(Range("Supervisor").Value))
    If sup = "" Then
        MsgBox "Supervisor field is mandatory for submission.", vbExclamation, "Missing Supervisor"
        Range("Supervisor").Select
        Exit Sub
    End If

    ' -- existing submission/email code goes here --
    Call SendFormByEmail  ' replace with actual routine name
End Sub

If using a UserForm with a TextBox named txtSupervisor and a CommandButton cmdSubmit, put this in the UserForm code:

Private Sub cmdSubmit_Click()
    Dim sup As String
    sup = Trim(Me.txtSupervisor.Value)
    If sup = "" Then
        MsgBox "Supervisor field is mandatory for submission.", vbExclamation, "Missing Supervisor"
        Me.txtSupervisor.SetFocus
        Exit Sub
    End If

    ' -- continue with submission logic --
    Call SendFormByEmailFromUserForm
End Sub

Notes and troubleshooting: Trim() removes stray spaces; use CStr to avoid type errors. If the on-sheet control is an ActiveX ComboBox use OLEObjects("ComboBox1").Object.Value or check its linked cell. If the Submit control is a Shape or a Forms button, assign the macro from the right-click menu; ActiveX buttons go in the sheet module. If the sheet is protected, selection calls may fail — unprotect or set focus differently. Validate before calling any email/send routine so blank entries are blocked reliably.

Recommended Answers

All 3 Replies

Member Avatar for Ramy Mahrous

This ComboBox in VB.NET form or in Excel sheet?

Member Avatar for visuallearner

It is in an excel spreadsheet, but I am using visual basic to edit the form/submit the form created in excel.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.