IsBlank function run on form will not allow initial save of bank value to send to users

I have the following code to prevent users from being able to save the form blank to send. However I need to be able to send the user a blank form innitially I can not figure out how to do this, Please advise.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If IsEmpty(ThisWorkbook.Sheets(1).Range("B6")) Then
        MsgBox ("Must enter Supplier Name")
        Cancel = True
    Else
    If IsEmpty(ThisWorkbook.Sheets(1).Range("C6")) Then
       MsgBox ("Must enter Supplier Location")
       Cancel = True
    Else
    If IsEmpty(ThisWorkbook.Sheets(1).Range("D6")) Then
       MsgBox ("Please enter your 5 digit supplier code. If you need assistance with the supplier code please contact SSG at 1(800)782-8099")
       Cancel = True
    Else
    If IsEmpty(ThisWorkbook.Sheets(1).Range("B10")) Then
       MsgBox ("Please enter expected users First name")
       Cancel = True
    Else
    If IsEmpty(ThisWorkbook.Sheets(1).Range("C10")) Then
       MsgBox ("Please enter expected users Last name")
       Cancel = True
    Else
    If IsEmpty(ThisWorkbook.Sheets(1).Range("D10")) Then
       MsgBox ("Please Create an original SSO # for this user")
       Cancel = True
    Else
    If IsEmpty(ThisWorkbook.Sheets(1).Range("F10")) Then
       MsgBox ("Please enter expected users email address.      Note: This must be a complete and existing email address")
       Cancel = True
    Else
    If IsEmpty(ThisWorkbook.Sheets(1).Range("G10")) Then
       MsgBox ("Please enter expected users full phone number with area code")
       Cancel = True

End If
End If
End If
End If
End If
End If
End If
End If

End Sub

Disable macros before opening this file. Then fix your code, and save.

Hi lengwer,

To iniitialize your data try the following in an ordinary code module such as 'Module1':

Sub InitializeFormCells()

    With ThisWorkbook.Sheets(1)
        Range("B6") = ""
        Range("C6") = ""
        Range("D6") = ""
        Range("B10") = ""
        Range("C10") = ""
        Range("D10") = ""
        Range("F10") = ""
        Range("G10") = ""
    End With

End Sub

Your routine checks for empty cells, but does not check for cells that contain blanks and no other characters. I added 'MyIsEmpty()' to replace 'IsEmpty()'.

MyIsEmpty():
a. Checks for empty cell
b. Checks for cell that contains only spaces (BLANKS)
c. Removes leading and trailing spaces from non-BLANK cells

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    If MyIsEmpty(ThisWorkbook.Sheets(1).Range("B6")) Then
        MsgBox ("Must enter Supplier Name")
        Cancel = True
        Exit Sub
    End If
    If MyIsEmpty(ThisWorkbook.Sheets(1).Range("C6")) Then
       MsgBox ("Must enter Supplier Location")
       Cancel = True
       Exit Sub
    End If
    If MyIsEmpty(ThisWorkbook.Sheets(1).Range("D6")) Then
       MsgBox ("Please enter your 5 digit supplier code. If you need assistance with the supplier code please contact SSG at 1(800)782-8099")
       Cancel = True
       Exit Sub
    End If
    If MyIsEmpty(ThisWorkbook.Sheets(1).Range("B10")) Then
       MsgBox ("Please enter expected users First name")
       Cancel = True
       Exit Sub
    End If
    If MyIsEmpty(ThisWorkbook.Sheets(1).Range("C10")) Then
       MsgBox ("Please enter expected users Last name")
       Cancel = True
       Exit Sub
    End If
    If MyIsEmpty(ThisWorkbook.Sheets(1).Range("D10")) Then
       MsgBox ("Please Create an original SSO # for this user")
       Cancel = True
       Exit Sub
    End If
    If MyIsEmpty(ThisWorkbook.Sheets(1).Range("F10")) Then
       MsgBox ("Please enter expected users email address.      Note: This must be a complete and existing email address")
       Cancel = True
       Exit Sub
    End If
    If MyIsEmpty(ThisWorkbook.Sheets(1).Range("G10")) Then
       MsgBox ("Please enter expected users full phone number with area code")
       Cancel = True
       Exit Sub
    End If
End Sub

Private Function MyIsEmpty(ByRef r As Range) As Boolean
  'This returns true if a cell is EMPTY or if the cell contains all blanks
  'This also removes leading and trailing blanks from the cell

  Dim s As String
  s = Trim(r.Text)
  
  If IsEmpty(r) Or Len(s) = 0 Then
    MyIsEmpty = True
  Else
    'Remove leading and trailing blanks from the value
    r.Value = Trim(r.Text)
  End If
End Function

Lewis

Thank you all for the information (Sorry for spelling errors in advance). I did figure out the issue myself. I also concatenated the msgbox to contain all of the errors at once. I created the following code to cover three scenarios:

1. Will save if all fields are null
2. Will not save if one or more of the required fields are null
3. Will save if all fields required are completed.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If IsEmpty(ThisWorkbook.Sheets(1).Range("B6")) Then
        MsgBox ("Must enter Supplier Name")
        Cancel = True
    Else
    If IsEmpty(ThisWorkbook.Sheets(1).Range("C6")) Then
       MsgBox ("Must enter Supplier Location")
       Cancel = True
    Else
    If IsEmpty(ThisWorkbook.Sheets(1).Range("D6")) Then
       MsgBox ("Please enter your 5 digit supplier code. If you need assistance with the supplier code please contact SSG at 1(800)782-8099")
       Cancel = True
    Else
    If IsEmpty(ThisWorkbook.Sheets(1).Range("B10")) Then
       MsgBox ("Please enter expected users First name")
       Cancel = True
    Else
    If IsEmpty(ThisWorkbook.Sheets(1).Range("C10")) Then
       MsgBox ("Please enter expected users Last name")
       Cancel = True
    Else
    If IsEmpty(ThisWorkbook.Sheets(1).Range("D10")) Then
       MsgBox ("Please Create an original SSO # for this user")
       Cancel = True
    Else
    If IsEmpty(ThisWorkbook.Sheets(1).Range("F10")) Then
       MsgBox ("Please enter expected users email address.      Note: This must be a complete and existing email address")
       Cancel = True
    Else
    If IsEmpty(ThisWorkbook.Sheets(1).Range("G10")) Then
       MsgBox ("Please enter expected users full phone number with area code")
       Cancel = True

End If
End If
End If
End If
End If
End If
End If
End If

End Sub

I will definitly change the code to reflect the MyIsEmpty() rather than the IsEmpty(). I can see where this would benifit. Thank you again for the information and the quick responses.