Need help with making fields required before save/print/close

**tnsvolley** · 01-06-2016, 12:45 PM

I have a form that has two tabs, Authorization and Final Reconciliation. I want to make several fields (b7:b12,h4,j7,m7,j8,m8,h9,h10) on the Authorization tab required before the user can save, print or close the form. Is there a way to do this??

I have attached the spreadsheet for a better visual.

Out of Area Travel Form Effective 2016-01-01.xlsm

Thanks in Advance for any help

**Mumps1** · 01-06-2016, 01:33 PM

Place the following macros in the code module for ThisWorkbook. Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the left hand pane, doubleclick 'ThisWorkbook'. An empty code window will open up. Copy and paste all three macros into the open window. Close the window to return to your sheet. When you attempt to save, close or print the form, you will be prompted to enter any missing information.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.ScreenUpdating = False
    Dim rng As Range
    Dim response As String
    For Each rng In Range("b7:b12,h4,j7,m7,j8,m8,h9,h10")
        If rng = "" Then
            Do
                response = InputBox("Please enter the missing information for cell " & rng.Address(0, 0))
                If response = "" Then
                    MsgBox ("You must enter the missing information for cell " & rng.Address(0, 0))
                Else
                    rng = response
                    Exit Do
                End If
            Loop
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Application.ScreenUpdating = False
    Dim rng As Range
    Dim response As String
    For Each rng In Range("b7:b12,h4,j7,m7,j8,m8,h9,h10")
        If rng = "" Then
            Do
                response = InputBox("Please enter the missing information for cell " & rng.Address(0, 0))
                If response = "" Then
                    MsgBox ("You must enter the missing information for cell " & rng.Address(0, 0))
                Else
                    rng = response
                    Exit Do
                End If
            Loop
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Application.ScreenUpdating = False
    Dim rng As Range
    Dim response As String
    For Each rng In Range("b7:b12,h4,j7,m7,j8,m8,h9,h10")
        If rng = "" Then
            Do
                response = InputBox("Please enter the missing information for cell " & rng.Address(0, 0))
                If response = "" Then
                    MsgBox ("You must enter the missing information for cell " & rng.Address(0, 0))
                Else
                    rng = response
                    Exit Do
                End If
            Loop
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

**LJMetzger** · 01-06-2016, 01:51 PM

Hi tnsvolley,

See the attached sample copy of your file. You need the following code in the 'ThisWorkbook' code module of your file.

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  
  Dim bHavePrerequisites As Boolean
  
  bHavePrerequisites = VerifyPrerequisites()
  If bHavePrerequisites = False Then
    MsgBox "The Form can not be closed until the prerequisites on" & vbCrLf & _
           "the 'Authorization' Tab have been filled in." & vbCrLf & vbCrLf & _
           "Prerequisites are: b7:b12,h4,j7,m7,j8,m8,h9,h10"
    Cancel = True
    Exit Sub
  End If

End Sub

Private Sub Workbook_BeforePrint(Cancel As Boolean)
  
  Dim bHavePrerequisites As Boolean
  
  bHavePrerequisites = VerifyPrerequisites()
  If bHavePrerequisites = False Then
    MsgBox "The Form can not be printed until the prerequisites on" & vbCrLf & _
           "the 'Authorization' Tab have been filled in." & vbCrLf & vbCrLf & _
           "Prerequisites are: b7:b12,h4,j7,m7,j8,m8,h9,h10"
    Cancel = True
    Exit Sub
  End If

End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  
  Dim bHavePrerequisites As Boolean
  
  bHavePrerequisites = VerifyPrerequisites()
  If bHavePrerequisites = False Then
    MsgBox "The Form can not be saved until the prerequisites on" & vbCrLf & _
           "the 'Authorization' Tab have been filled in." & vbCrLf & vbCrLf & _
           "Prerequisites are: b7:b12,h4,j7,m7,j8,m8,h9,h10"
    Cancel = True
    Exit Sub
  End If

End Sub

Private Function VerifyPrerequisites() As Boolean
  'This returns True if all of the following cells are NOT BLANK:
  'b7:b12,h4,j7,m7,j8,m8,h9,h10
  
  Dim myRange As Range
  Dim r As Range
  Dim sRange As String
  Dim sValue As String
  
  sRange = "b7:b12,h4,j7,m7,j8,m8,h9,h10"
  
  Dim ws As Worksheet
  Set ws = ActiveWorkbook.Sheets("Authorization")
  
  Set myRange = ws.Range(sRange)
  Debug.Print myRange.Address
  
  'Initialize the return value to Verify
  VerifyPrerequisites = True
  
  'Process each cell in the range
  'Get the Value (remove leading/trailing spaces)
  'NOT VERIFY (and exit) if any cell is BLANK (length = 0)
  For Each r In myRange
    sValue = Trim(r)
    If Len(sValue) = 0 Then
      VerifyPrerequisites = False
      Exit For
    End If
  Next r

  Set ws = Nothing
  
End Function

It is a best practice to declare all variables. If you misspell a variable in your code, VBA will silently assume it is a Variant variable and go on executing with no clue to you that you have a bug. Go to the VBA development window, click Tools, Options, and check "Require Variable Declaration." This will insert the following line at the top of all new modules:

Option Explicit

This option requires all variables to be declared and will give a compiler error for undeclared variables.

If you need help with Macros and/or VBA the following may help:
To enable Macros and to Run Macros see the following:
http://office.microsoft.com/en-us/ex...010031071.aspx
http://office.microsoft.com/en-us/ex...010014113.aspx
If help is still needed do a google search for 'youtube excel enable macro' and/or 'youtube excel run macro'.

To access Visual Basic (VBA) see:
http://www.ablebits.com/office-addin...a-macro-excel/
a. Click on any cell in the Excel Spreadsheet (may not be needed).
b. ALT-F11 to get to VBA.
c. CTRL-R to get project explorer (if it isn't already showing).
d. Double Click on a 'Module Name' in 'Project Explorer' to see code for that module.

Lewis

**tnsvolley** · 01-07-2016, 02:53 PM

Originally Posted by LJMetzger

Hi tnsvolley,

See the attached sample copy of your file. You need the following code in the 'ThisWorkbook' code module of your file.

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  
  Dim bHavePrerequisites As Boolean
  
  bHavePrerequisites = VerifyPrerequisites()
  If bHavePrerequisites = False Then
    MsgBox "The Form can not be closed until the prerequisites on" & vbCrLf & _
           "the 'Authorization' Tab have been filled in." & vbCrLf & vbCrLf & _
           "Prerequisites are: b7:b12,h4,j7,m7,j8,m8,h9,h10"
    Cancel = True
    Exit Sub
  End If

End Sub

Private Sub Workbook_BeforePrint(Cancel As Boolean)
  
  Dim bHavePrerequisites As Boolean
  
  bHavePrerequisites = VerifyPrerequisites()
  If bHavePrerequisites = False Then
    MsgBox "The Form can not be printed until the prerequisites on" & vbCrLf & _
           "the 'Authorization' Tab have been filled in." & vbCrLf & vbCrLf & _
           "Prerequisites are: b7:b12,h4,j7,m7,j8,m8,h9,h10"
    Cancel = True
    Exit Sub
  End If

End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  
  Dim bHavePrerequisites As Boolean
  
  bHavePrerequisites = VerifyPrerequisites()
  If bHavePrerequisites = False Then
    MsgBox "The Form can not be saved until the prerequisites on" & vbCrLf & _
           "the 'Authorization' Tab have been filled in." & vbCrLf & vbCrLf & _
           "Prerequisites are: b7:b12,h4,j7,m7,j8,m8,h9,h10"
    Cancel = True
    Exit Sub
  End If

End Sub

Private Function VerifyPrerequisites() As Boolean
  'This returns True if all of the following cells are NOT BLANK:
  'b7:b12,h4,j7,m7,j8,m8,h9,h10
  
  Dim myRange As Range
  Dim r As Range
  Dim sRange As String
  Dim sValue As String
  
  sRange = "b7:b12,h4,j7,m7,j8,m8,h9,h10"
  
  Dim ws As Worksheet
  Set ws = ActiveWorkbook.Sheets("Authorization")
  
  Set myRange = ws.Range(sRange)
  Debug.Print myRange.Address
  
  'Initialize the return value to Verify
  VerifyPrerequisites = True
  
  'Process each cell in the range
  'Get the Value (remove leading/trailing spaces)
  'NOT VERIFY (and exit) if any cell is BLANK (length = 0)
  For Each r In myRange
    sValue = Trim(r)
    If Len(sValue) = 0 Then
      VerifyPrerequisites = False
      Exit For
    End If
  Next r

  Set ws = Nothing
  
End Function

It is a best practice to declare all variables. If you misspell a variable in your code, VBA will silently assume it is a Variant variable and go on executing with no clue to you that you have a bug. Go to the VBA development window, click Tools, Options, and check "Require Variable Declaration." This will insert the following line at the top of all new modules:

Option Explicit

This option requires all variables to be declared and will give a compiler error for undeclared variables.

If you need help with Macros and/or VBA the following may help:
To enable Macros and to Run Macros see the following:
http://office.microsoft.com/en-us/ex...010031071.aspx
http://office.microsoft.com/en-us/ex...010014113.aspx
If help is still needed do a google search for 'youtube excel enable macro' and/or 'youtube excel run macro'.

To access Visual Basic (VBA) see:
http://www.ablebits.com/office-addin...a-macro-excel/
a. Click on any cell in the Excel Spreadsheet (may not be needed).
b. ALT-F11 to get to VBA.
c. CTRL-R to get project explorer (if it isn't already showing).
d. Double Click on a 'Module Name' in 'Project Explorer' to see code for that module.

Lewis

Thank you, that worked perfectly!
Eileen

**tnsvolley** · 01-07-2016, 05:22 PM

Originally Posted by LJMetzger

Hi tnsvolley,

See the attached sample copy of your file. You need the following code in the 'ThisWorkbook' code module of your file.

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  
  Dim bHavePrerequisites As Boolean
  
  bHavePrerequisites = VerifyPrerequisites()
  If bHavePrerequisites = False Then
    MsgBox "The Form can not be closed until the prerequisites on" & vbCrLf & _
           "the 'Authorization' Tab have been filled in." & vbCrLf & vbCrLf & _
           "Prerequisites are: b7:b12,h4,j7,m7,j8,m8,h9,h10"
    Cancel = True
    Exit Sub
  End If

End Sub

Private Sub Workbook_BeforePrint(Cancel As Boolean)
  
  Dim bHavePrerequisites As Boolean
  
  bHavePrerequisites = VerifyPrerequisites()
  If bHavePrerequisites = False Then
    MsgBox "The Form can not be printed until the prerequisites on" & vbCrLf & _
           "the 'Authorization' Tab have been filled in." & vbCrLf & vbCrLf & _
           "Prerequisites are: b7:b12,h4,j7,m7,j8,m8,h9,h10"
    Cancel = True
    Exit Sub
  End If

End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  
  Dim bHavePrerequisites As Boolean
  
  bHavePrerequisites = VerifyPrerequisites()
  If bHavePrerequisites = False Then
    MsgBox "The Form can not be saved until the prerequisites on" & vbCrLf & _
           "the 'Authorization' Tab have been filled in." & vbCrLf & vbCrLf & _
           "Prerequisites are: b7:b12,h4,j7,m7,j8,m8,h9,h10"
    Cancel = True
    Exit Sub
  End If

End Sub

Private Function VerifyPrerequisites() As Boolean
  'This returns True if all of the following cells are NOT BLANK:
  'b7:b12,h4,j7,m7,j8,m8,h9,h10
  
  Dim myRange As Range
  Dim r As Range
  Dim sRange As String
  Dim sValue As String
  
  sRange = "b7:b12,h4,j7,m7,j8,m8,h9,h10"
  
  Dim ws As Worksheet
  Set ws = ActiveWorkbook.Sheets("Authorization")
  
  Set myRange = ws.Range(sRange)
  Debug.Print myRange.Address
  
  'Initialize the return value to Verify
  VerifyPrerequisites = True
  
  'Process each cell in the range
  'Get the Value (remove leading/trailing spaces)
  'NOT VERIFY (and exit) if any cell is BLANK (length = 0)
  For Each r In myRange
    sValue = Trim(r)
    If Len(sValue) = 0 Then
      VerifyPrerequisites = False
      Exit For
    End If
  Next r

  Set ws = Nothing
  
End Function

It is a best practice to declare all variables. If you misspell a variable in your code, VBA will silently assume it is a Variant variable and go on executing with no clue to you that you have a bug. Go to the VBA development window, click Tools, Options, and check "Require Variable Declaration." This will insert the following line at the top of all new modules:

Option Explicit

This option requires all variables to be declared and will give a compiler error for undeclared variables.

If you need help with Macros and/or VBA the following may help:
To enable Macros and to Run Macros see the following:
http://office.microsoft.com/en-us/ex...010031071.aspx
http://office.microsoft.com/en-us/ex...010014113.aspx
If help is still needed do a google search for 'youtube excel enable macro' and/or 'youtube excel run macro'.

To access Visual Basic (VBA) see:
http://www.ablebits.com/office-addin...a-macro-excel/
a. Click on any cell in the Excel Spreadsheet (may not be needed).
b. ALT-F11 to get to VBA.
c. CTRL-R to get project explorer (if it isn't already showing).
d. Double Click on a 'Module Name' in 'Project Explorer' to see code for that module.

Lewis

That worked how I wanted, however, i did not think my plan through as now I can not save it as a blank form for others to fill in because the fields have to be filled in. Any ideas??

**LJMetzger** · 01-07-2016, 06:16 PM

Hi,

You don't have to quote entire previous posts when replying.

See the attached file that contains the following code. New Code and Modified Code are in red.

To implement the code, Double Click cell 'A1' (to save), 'B1' (to print), 'C1' (to close) on sheet 'Authorization' and enter password abc (Case Insensitive). This will allow the 'Administrator' to save, print, or close the file at any time.

Option Explicit

Private Const sAdministratorPASSWORD = "abc"
Private bAdministratorSavePrintClose As Boolean

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

  Dim sPassword As String

  'Process a 'Double Click' on Sheet 'Authorization'
  ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  'A1 = Save
  ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  If Not Intersect(Target, Range("A1")) Is Nothing Then
    If Sh.Name = "Authorization" Then
    
      sPassword = InputBox( _
        Title:="Administrator Password Data Entry (File Save)", _
        Prompt:="Enter the Administrator Password (CASE INSENSITIVE) to Save the File.")
    
    
      If UCase(sPassword) = UCase(sAdministratorPASSWORD) Then
        'Enable Administrator Save
        'Save the file
        'Disable Administrator Save
        bAdministratorSavePrintClose = True
        ThisWorkbook.Save
        bAdministratorSavePrintClose = False
        MsgBox "File SAVED by Administrator."
      Else
        MsgBox "File NOT SAVED, Incorrect or NO Administrator PASSWORD Entered."
      End If
      
      'Return Normal Focus to Excel
      Cancel = True
    
    End If
  End If
  
  ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  'B1 = Print
  ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  If Not Intersect(Target, Range("B1")) Is Nothing Then
    If Sh.Name = "Authorization" Then
    
      sPassword = InputBox( _
        Title:="Administrator Password Data Entry (File Print)", _
        Prompt:="Enter the Administrator Password (CASE INSENSITIVE) to Print the File.")
    
      If UCase(sPassword) = UCase(sAdministratorPASSWORD) Then
        'Enable Administrator Save
        'Save the file
        'Disable Administrator Save
        bAdministratorSavePrintClose = True
        Sh.PrintPreview
        bAdministratorSavePrintClose = False
        MsgBox "File PRINTED by Administrator."
      Else
        MsgBox "File NOT PRINTED, Incorrect or NO Administrator PASSWORD Entered."
      End If
      
      'Return Normal Focus to Excel
      Cancel = True
    
    End If
  End If
  
  ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  'C1 = Close
  ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  If Not Intersect(Target, Range("C1")) Is Nothing Then
    If Sh.Name = "Authorization" Then
    
      sPassword = InputBox( _
        Title:="Administrator Password Data Entry (File Close)", _
        Prompt:="Enter the Administrator Password (CASE INSENSITIVE) to Close the File.")
    
    
      If UCase(sPassword) = UCase(sAdministratorPASSWORD) Then
        'Enable Administrator Close
        'Close the file
        'Disable Administrator Close
        bAdministratorSavePrintClose = True
        ThisWorkbook.Close
        bAdministratorSavePrintClose = False
        MsgBox "File CLOSED by Administrator."
      Else
        MsgBox "File NOT CLOSED, Incorrect or NO Administrator PASSWORD Entered."
      End If
      
      'Return Normal Focus to Excel
      Cancel = True
    
    End If
  End If
  

End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
  
  Dim bHavePrerequisites As Boolean
  
  If bAdministratorSavePrintClose = False Then
    bHavePrerequisites = VerifyPrerequisites()
    If bHavePrerequisites = False Then
      MsgBox "The Form can not be closed until the prerequisites on" & vbCrLf & _
             "the 'Authorization' Tab have been filled in." & vbCrLf & vbCrLf & _
             "Prerequisites are: b7:b12,h4,j7,m7,j8,m8,h9,h10"
      Cancel = True
     Exit Sub
    End If
  End If

End Sub

Private Sub Workbook_BeforePrint(Cancel As Boolean)
  
  Dim bHavePrerequisites As Boolean
  
  If bAdministratorSavePrintClose = False Then
    bHavePrerequisites = VerifyPrerequisites()
    If bHavePrerequisites = False Then
      MsgBox "The Form can not be printed until the prerequisites on" & vbCrLf & _
             "the 'Authorization' Tab have been filled in." & vbCrLf & vbCrLf & _
             "Prerequisites are: b7:b12,h4,j7,m7,j8,m8,h9,h10"
      Cancel = True
      Exit Sub
    End If
  End If

End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  
  Dim bHavePrerequisites As Boolean
  
  If bAdministratorSavePrintClose = False Then
    bHavePrerequisites = VerifyPrerequisites()
    If bHavePrerequisites = False Then
      MsgBox "The Form can not be saved until the prerequisites on" & vbCrLf & _
             "the 'Authorization' Tab have been filled in." & vbCrLf & vbCrLf & _
             "Prerequisites are: b7:b12,h4,j7,m7,j8,m8,h9,h10"
      Cancel = True
      Exit Sub
    End If
  End If
  
End Sub

Private Function VerifyPrerequisites() As Boolean
  'This returns True if all of the following cells are NOT BLANK:
  'b7:b12,h4,j7,m7,j8,m8,h9,h10
  
  Dim myRange As Range
  Dim r As Range
  Dim sRange As String
  Dim sValue As String
  
  sRange = "b7:b12,h4,j7,m7,j8,m8,h9,h10"
  
  Dim ws As Worksheet
  Set ws = ActiveWorkbook.Sheets("Authorization")
  
  Set myRange = ws.Range(sRange)
  Debug.Print myRange.Address
  
  'Initialize the return value to Verify
  VerifyPrerequisites = True
  
  'Process each cell in the range
  'Get the Value (remove leading/trailing spaces)
  'NOT VERIFY (and exit) if any cell is BLANK (length = 0)
  For Each r In myRange
    sValue = Trim(r)
    If Len(sValue) = 0 Then
      VerifyPrerequisites = False
      Exit For
    End If
  Next r

  Set ws = Nothing
  
End Function

**tnsvolley** · 01-07-2016, 07:01 PM

Wow...that is awesome...thanks so much!!!

Need help with making fields required before save/print/close

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Need help with making fields required before save/print/close

Re: Need help with making fields required before save/print/close

Re: Need help with making fields required before save/print/close

Re: Need help with making fields required before save/print/close

Re: Need help with making fields required before save/print/close

Re: Need help with making fields required before save/print/close

Re: Need help with making fields required before save/print/close

Thread Information

Users Browsing this Thread

Similar Threads

Make multiple fields required in form before save

Create watermark, save as pdf, print pdf, close file**

[SOLVED] Combine DocumentBeforeSave and Print (Required fields)

Macro-Verify required fields before proceding with routine (save)

Won't Save if Required Fields are not Filled out

Copy,Save,Print then Close

want to save, print and close wb

[SOLVED] I like to open a folder,auto print,save then close

Bookmarks

Bookmarks

Posting Permissions