Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Mar 2012
    Posts
    78
    Thanks
    20
    Thanked 0 Times in 0 Posts

    Excel WB that has required cell input!

    Hi

    I have an Excel WB that is set-up with several worksheets where I require input on various sheets before closing the WB. The WB is distributed to many users. I need the required cells to be blank when my users download the WB from a web site. Then I need the code to catch where my users forgot to enter data before closing it. The code as is works but I cannot close the WB until I fill-in the required cells! I thought maybe some code on open to delete the data in the required cells might work but the WB is emailed to me and when I open it I do not want it to delete the data from the required cells--EGADS!! Jean

    Here is the code in "This Workbook":

    Code:
    ' Create a global variable to store the default save file format.
    Dim MyCurrentSaveFormat As String
     
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.DisplayAlerts = False
    If ActiveWorkbook.Saved = False Then
    ActiveWorkbook.Save
    End If
    With Sheets("Invoice").Range("g3")
    If Len(Trim(.Value)) = 0 Then
    UsrName = Application.InputBox("Please Enter Date", "Date Required")
    If UsrName = "False" Or Len(Trim(UsrName)) = 0 Then
    Cancel = True
    Else
    .Value = UsrName
    End If
    End If
    End With
    With Sheets("Holiday").Range("b31")
    If Len(Trim(.Value)) = 0 Then
    UsrName = Application.InputBox("Please Enter Name", "Name Required")
    If UsrName = "False" Or Len(Trim(UsrName)) = 0 Then
    Cancel = True
    Else
    .Value = UsrName
    End If
    End If
    End With
    End Sub
    
    
    
    Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
     
    ' This event occurs before the user attempts to save the workbook.
    ' Save the current default save file format in the global variable.
    ' Change the default save file format to the .xslm.
     
    MyCurrentSaveFormat = Application.DefaultSaveFormat
    Application.DefaultSaveFormat = xlOpenXMLWorkbookMacroEnabled
     
    End Sub
     
    Sub Workbook_AfterSave(ByVal Success As Boolean)
     
    ' This event occurs after the user saves the workbook.
    ' Restore the default save file format to the user's original setting.
     
    Application.DefaultSaveFormat = MyCurrentSaveFormat
     
    End Sub

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    How about a backdoor approach? If you enter a "password" on the first input box, the workbook will just close by bypassing the validation of the inputs. (Note: Not tested)

    Code:
    ' Create a global variable to store the default save file format.
    Dim MyCurrentSaveFormat As String
     
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.DisplayAlerts = False
    If ActiveWorkbook.Saved = False Then
    ActiveWorkbook.Save
    End If
    With Sheets("Invoice").Range("g3")
    If Len(Trim(.Value)) = 0 Then
    UsrName = Application.InputBox("Please Enter Date", "Date Required")
    If UsrName = "PASSWORD" Then Exit Sub 'CHANGE PASSWORD
    If UsrName = "False" Or Len(Trim(UsrName)) = 0 Then
    Cancel = True
    Else
    .Value = UsrName
    End If
    End If
    End With
    With Sheets("Holiday").Range("b31")
    If Len(Trim(.Value)) = 0 Then
    UsrName = Application.InputBox("Please Enter Name", "Name Required")
    If UsrName = "False" Or Len(Trim(UsrName)) = 0 Then
    Cancel = True
    Else
    .Value = UsrName
    End If
    End If
    End With
    End Sub

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    JeanM (2014-09-01)

  4. #3
    Star Lounger
    Join Date
    Mar 2012
    Posts
    78
    Thanks
    20
    Thanked 0 Times in 0 Posts
    Hi Maudibe

    This works great. Before I heard from you I did come up with code that worked when opening the WB, but it took to long to open the WB. This works so much better. Thank you.

    Jean

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Jean,

    You could also use:
    Code:
    Dim  zCurrUser as string
    
    zCurrUser = Environ("UserName")
    
    If zCurUser <> "[Your User Name Here]" Then
      'Code to clear cells
    End if
    Place above in your Auto_Open() routine. You could also use it in the Exit routine to allow you to get out w/o completing the data while forcing others to do so.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    RG,
    Nice! Your concept makes much more sense than my approach when used in the Workbook_BeforeClose

    Code:
    ' Create a global variable to store the default save file format.
    Dim MyCurrentSaveFormat As String
     
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Environ("UserName")="[Your User Name Here]" then Exit Sub
    Application.DisplayAlerts = False
    If ActiveWorkbook.Saved = False Then
    ActiveWorkbook.Save
    End If
    With Sheets("Invoice").Range("g3")
    If Len(Trim(.Value)) = 0 Then
    UsrName = Application.InputBox("Please Enter Date", "Date Required")
    If UsrName = "False" Or Len(Trim(UsrName)) = 0 Then
    Cancel = True

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •