I have several Userforms on which I have a Cancel/Close button. My intent is to allow a user to close a userform when ever they wish but to query them before execution to avert an error.
Each control on the userform has an exit event to validate data entered. In the event the user wishes to close the user form the on exit events prevent the closure from happening.
I have attempted to use a Public EnableEvents as Boolean declaration (recommended by Chip Pearson) to interrupt the on exit events but either misunderstood what Chip's solution addressed or missed something in the steps. Any help you provided would be appreciated.
thank you.
My Code Follows
Option Explicit
Dim rng As Range, QuestionToMessageBox As String, YesOrNoAnswertoMessageBox As String
Public EnableEvents As Boolean, BranchNum As Integer, iRow As Integer, WS As Worksheet, BranchCount As Integer
Private Sub UserForm_Initialize()
'*******************************************************************************
'The UserForm allows for the entry of new data or the editing of existing data.
'Step one is to test to see if any data already exists on the sheet
'Future plans may allow for a user to enter multiple Branch (office Locations)
'but currently only a single location is allowed.
'*******************************************************************************
On Error Resume Next
Me.EnableEvents = True
With Worksheets("Schedule A")
CBState3.List = .Range("p12" & .Range("p" & .Rows.Count).End(xlUp).Row).value
End With
If WorksheetFunction.CountA(Sheets("BranchInfo").Range("A2:I2")) > 0 Then
MsgBox "Some Branch Information has Already been entered." & vbCr & "Click OK to Validate or Edit as needed."
Sheets("BranchInfo").Visible = True
Worksheets("Branchinfo").Select
'UFBranchInfo.PreviousCB.Visible = False
'UFBranchInfo.NextCB.Visible = False
'UFBranchInfo.AddBranchCB.Visible = False
With Sheets("BranchInfo")
UFBranchInfo.TBBranchNo1.value = .Cells(2, 1)
UFBranchInfo.TBContact = .Cells(2, 2)
UFBranchInfo.TBEquipLocAdd1 = .Cells(2, 3)
UFBranchInfo.TBEquipAdd2 = .Cells(2, 4)
UFBranchInfo.TBEquipCity = .Cells(2, 5)
UFBranchInfo.TBEquipCounty = .Cells(2, 6)
UFBranchInfo.CBState3 = .Cells(2, 7)
UFBranchInfo.TBEquipLocZipcode.value = .Cells(2, 8)
UFBranchInfo.TBHeadCount.value = .Cells(2, 9)
End With
Else
Sheets("BranchInfo").Visible = True
Sheets("Branchinfo").Select
'UFBranchInfo.PreviousCB.Visible = False
'UFBranchInfo.NextCB.Visible = False
'UFBranchInfo.AddBranchCB.Visible = False
End If
End Sub
Private Sub FinishedCB_Click()
'------------------------------------------------------------------------------
'Allow user to enter only a single branch
'------------------------------------------------------------------------------
On Error Resume Next
Set WS = Worksheets("BranchInfo")
Call DataEntry
Unload Me
Sheets("BranchInfo").Visible = False
UFPayeeInfo.Show
Exit Sub
End Sub
Private Sub CancelCB_Click()
'*********************************************************************************
'Cancel gives the user a choice to lose data entered or to continue Data Entry.
'********************************************************************************
EnableEvents = False
On Error Resume Next
Application.ScreenUpdating = False
QuestionToMessageBox = "Closing this form will cause any Data entered to be lost!" _
& vbCrLf & "Yes to Proceed --- No to Cancel"
YesOrNoAnswertoMessageBox = MsgBox(QuestionToMessageBox, vbYesNo, "Close Form or Not")
If YesOrNoAnswertoMessageBox = vbNo Then
Cancel = True
TBBranchNo1.SetFocus
Else
Unload Me
Application.ScreenUpdating = True
Sheets("BranchInfo").Visible = False
End If
Me.EnableEvents = True
End Sub
Private Sub TBBranchNo1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error Resume Next
If EnableEvents =False Then Exit Sub
If Not IsNumeric(TBBranchNo1.value) Or TBBranchNo1.value < 1008 Or TBBranchNo1 > 7999 Then
TBBranchNo1.value = ""
MsgBox "Please enter a Four (4)digit Branch Number"
Cancel = True
Me.TBBranchNo1.SetFocus
End If
'Me.EnableEvents = True
End Sub
Private Sub TBHeadCount_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error Resume Next
If EnableEvents=False Then Exit Sub
If Not IsNumeric(TBHeadCount.value) Then
TBHeadCount.value = ""
MsgBox "Please enter the Number of Active Employees in your Branch"
Cancel = True
Me.TBHeadCount.SetFocus
End If
'Me.EnableEvents = True
End Sub
Private Sub TBContact_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error Resume Next
If EnableEvents = False Then Exit Sub
If (Me.TBContact.value) = "" Then
MsgBox "Please enter Contact Name"
Cancel = True
Me.TBContact.SetFocus
End If
'Me.EnableEvents = True
End Sub
Private Sub TBEquipLocAdd1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error Resume Next
If EnableEvents=False Then Exit Sub
If (Me.TBEquipLocAdd1.value) = "" Then
MsgBox "Please enter Address information"
Cancel = True
Me.TBEquipLocAdd1.SetFocus
End If
'Me.EnableEvents = True
End Sub
Private Sub TBEquipCity_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error Resume Next
If EnableEvents = False Then Exit Sub
If (Me.TBEquipCity.value) = "" Then
MsgBox "Please enter the Name of the City"
Cancel = True
Me.TBEquipCity.SetFocus
End If
'Me.EnableEvents = True
End Sub
Private Sub TBEquipCounty_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error Resume Next
If EnableEvents =False Then Exit Sub
If (Me.TBEquipCounty.value) = "" Then
MsgBox "Please Enter the County where Equipment is Located"
Cancel = True
Me.TBEquipCounty.SetFocus
End If
' Me.EnableEvents = True
End Sub
Private Sub CBState3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error Resume Next
If EnableEvents = False Then Exit Sub
If (Me.CBState3.value) = "" Then
MsgBox "Please Select the State"
Cancel = True
Me.CBState3.SetFocus
End If
'Me.EnableEvents = True
End Sub
Private Sub TBEquipLocZipcode_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error Resume Next
If EnableEvents = False Then Exit Sub
If (Me.TBEquipLocZipcode.value) = "" Then
MsgBox "Please enter the Zip code"
Cancel = True
Me.TBEquipLocZipcode.SetFocus
Exit Sub
End If
If Not IsNumeric(TBEquipLocZipcode.value) Or TBEquipLocZipcode.value < 1 Or TBBranchNo1 > 99999 Then
TBEquipLocZipcode.value = ""
MsgBox "Please enter a Five (5)digit Zip Code"
Cancel = True
Me.TBEquipLocZipcode.SetFocus
End If
'Me.EnableEvents = True
End Sub
Each control on the userform has an exit event to validate data entered. In the event the user wishes to close the user form the on exit events prevent the closure from happening.
I have attempted to use a Public EnableEvents as Boolean declaration (recommended by Chip Pearson) to interrupt the on exit events but either misunderstood what Chip's solution addressed or missed something in the steps. Any help you provided would be appreciated.
thank you.
My Code Follows
Option Explicit
Dim rng As Range, QuestionToMessageBox As String, YesOrNoAnswertoMessageBox As String
Public EnableEvents As Boolean, BranchNum As Integer, iRow As Integer, WS As Worksheet, BranchCount As Integer
Private Sub UserForm_Initialize()
'*******************************************************************************
'The UserForm allows for the entry of new data or the editing of existing data.
'Step one is to test to see if any data already exists on the sheet
'Future plans may allow for a user to enter multiple Branch (office Locations)
'but currently only a single location is allowed.
'*******************************************************************************
On Error Resume Next
Me.EnableEvents = True
With Worksheets("Schedule A")
CBState3.List = .Range("p12" & .Range("p" & .Rows.Count).End(xlUp).Row).value
End With
If WorksheetFunction.CountA(Sheets("BranchInfo").Range("A2:I2")) > 0 Then
MsgBox "Some Branch Information has Already been entered." & vbCr & "Click OK to Validate or Edit as needed."
Sheets("BranchInfo").Visible = True
Worksheets("Branchinfo").Select
'UFBranchInfo.PreviousCB.Visible = False
'UFBranchInfo.NextCB.Visible = False
'UFBranchInfo.AddBranchCB.Visible = False
With Sheets("BranchInfo")
UFBranchInfo.TBBranchNo1.value = .Cells(2, 1)
UFBranchInfo.TBContact = .Cells(2, 2)
UFBranchInfo.TBEquipLocAdd1 = .Cells(2, 3)
UFBranchInfo.TBEquipAdd2 = .Cells(2, 4)
UFBranchInfo.TBEquipCity = .Cells(2, 5)
UFBranchInfo.TBEquipCounty = .Cells(2, 6)
UFBranchInfo.CBState3 = .Cells(2, 7)
UFBranchInfo.TBEquipLocZipcode.value = .Cells(2, 8)
UFBranchInfo.TBHeadCount.value = .Cells(2, 9)
End With
Else
Sheets("BranchInfo").Visible = True
Sheets("Branchinfo").Select
'UFBranchInfo.PreviousCB.Visible = False
'UFBranchInfo.NextCB.Visible = False
'UFBranchInfo.AddBranchCB.Visible = False
End If
End Sub
Private Sub FinishedCB_Click()
'------------------------------------------------------------------------------
'Allow user to enter only a single branch
'------------------------------------------------------------------------------
On Error Resume Next
Set WS = Worksheets("BranchInfo")
Call DataEntry
Unload Me
Sheets("BranchInfo").Visible = False
UFPayeeInfo.Show
Exit Sub
End Sub
Private Sub CancelCB_Click()
'*********************************************************************************
'Cancel gives the user a choice to lose data entered or to continue Data Entry.
'********************************************************************************
EnableEvents = False
On Error Resume Next
Application.ScreenUpdating = False
QuestionToMessageBox = "Closing this form will cause any Data entered to be lost!" _
& vbCrLf & "Yes to Proceed --- No to Cancel"
YesOrNoAnswertoMessageBox = MsgBox(QuestionToMessageBox, vbYesNo, "Close Form or Not")
If YesOrNoAnswertoMessageBox = vbNo Then
Cancel = True
TBBranchNo1.SetFocus
Else
Unload Me
Application.ScreenUpdating = True
Sheets("BranchInfo").Visible = False
End If
Me.EnableEvents = True
End Sub
Private Sub TBBranchNo1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error Resume Next
If EnableEvents =False Then Exit Sub
If Not IsNumeric(TBBranchNo1.value) Or TBBranchNo1.value < 1008 Or TBBranchNo1 > 7999 Then
TBBranchNo1.value = ""
MsgBox "Please enter a Four (4)digit Branch Number"
Cancel = True
Me.TBBranchNo1.SetFocus
End If
'Me.EnableEvents = True
End Sub
Private Sub TBHeadCount_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error Resume Next
If EnableEvents=False Then Exit Sub
If Not IsNumeric(TBHeadCount.value) Then
TBHeadCount.value = ""
MsgBox "Please enter the Number of Active Employees in your Branch"
Cancel = True
Me.TBHeadCount.SetFocus
End If
'Me.EnableEvents = True
End Sub
Private Sub TBContact_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error Resume Next
If EnableEvents = False Then Exit Sub
If (Me.TBContact.value) = "" Then
MsgBox "Please enter Contact Name"
Cancel = True
Me.TBContact.SetFocus
End If
'Me.EnableEvents = True
End Sub
Private Sub TBEquipLocAdd1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error Resume Next
If EnableEvents=False Then Exit Sub
If (Me.TBEquipLocAdd1.value) = "" Then
MsgBox "Please enter Address information"
Cancel = True
Me.TBEquipLocAdd1.SetFocus
End If
'Me.EnableEvents = True
End Sub
Private Sub TBEquipCity_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error Resume Next
If EnableEvents = False Then Exit Sub
If (Me.TBEquipCity.value) = "" Then
MsgBox "Please enter the Name of the City"
Cancel = True
Me.TBEquipCity.SetFocus
End If
'Me.EnableEvents = True
End Sub
Private Sub TBEquipCounty_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error Resume Next
If EnableEvents =False Then Exit Sub
If (Me.TBEquipCounty.value) = "" Then
MsgBox "Please Enter the County where Equipment is Located"
Cancel = True
Me.TBEquipCounty.SetFocus
End If
' Me.EnableEvents = True
End Sub
Private Sub CBState3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error Resume Next
If EnableEvents = False Then Exit Sub
If (Me.CBState3.value) = "" Then
MsgBox "Please Select the State"
Cancel = True
Me.CBState3.SetFocus
End If
'Me.EnableEvents = True
End Sub
Private Sub TBEquipLocZipcode_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error Resume Next
If EnableEvents = False Then Exit Sub
If (Me.TBEquipLocZipcode.value) = "" Then
MsgBox "Please enter the Zip code"
Cancel = True
Me.TBEquipLocZipcode.SetFocus
Exit Sub
End If
If Not IsNumeric(TBEquipLocZipcode.value) Or TBEquipLocZipcode.value < 1 Or TBBranchNo1 > 99999 Then
TBEquipLocZipcode.value = ""
MsgBox "Please enter a Five (5)digit Zip Code"
Cancel = True
Me.TBEquipLocZipcode.SetFocus
End If
'Me.EnableEvents = True
End Sub