Userform - All fields Manadatory and must me Completed yet "Unload Me" circumvents process

D

Dice711

New Member
Joined
Aug 13, 2002
Messages
14
Hello

Need help on this one. I have really tried to search and figure this issue out before posting...

I have a userform called DigitalSignatureSM that is used to digitally sign a form. All fields in the userform (i.e. dropdown combo boxes) are required to be filled and will highlight in red the areas that need to be filled in and will not let the user proceed until all required fields have information (see that code below). I call the userform into subroutine using: DigitalSignatureSM.Show.

Everything works fine unless the user decides to click on the "Close" button on the userform that is actually just "Unload Me." The problem is that the original subroutine continues on and processes the code to the end, whereby giving my an un-signed form. What I really want to happen is if the user clicks on the "Close" button (or otherwise Unload Me), that all code stops meaning the userform closes (that happens now) and everything stops (that doesn't happen).

Thanks in advance for any help. I hope it is something simple...


Make all fields mandatory code:

Dim ans As Long
For Each ctrl In Me.Controls
Select Case True
Case TypeOf ctrl Is MSForms.CheckBox, TypeOf ctrl Is MSForms.OptionButton
Select Case ctrl.Value
Case True
ctrl.BackColor = vbGreen
Case Else
ctrl.BackColor = vbRed: ans = ans + 1
End Select
Case TypeOf ctrl Is MSForms.TextBox, TypeOf ctrl Is MSForms.ComboBox
Select Case ctrl.Value
Case vbNullString
ctrl.BackColor = vbRed: ans = ans + 1
Case Else
ctrl.BackColor = vbGreen
End Select
End Select
Next ctrl
If ans > 0 Then
MsgBox "Please enter data in the required fields. Controls in Red need completion", vbInformation, "Enter Data"
Exit Sub
'Else
'MsgBox "Thanks, fields have been filled correctly"
End If
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
Hi try declaring a public boolean variable to manage cancel button press & see if this helps

e.g

in standard module

Code: 
Public CancelEntry As Boolean


Updated to DigitalSignatureSM form code

Code: 
Private Sub CmdClose_Click()
    CancelEntry = True
    Unload Me
End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    CancelEntry = CBool(CloseMode = 0)
End Sub

Update to you main form

Code: 
 DigitalSignatureSM.Show
    If CancelEntry Then Exit Sub
    
    '
    'rest of code
    '
    '

Hope Helpful

Dave
 
Upvote 0
Hello Dave

Thanks so much... I really appreciate the response. It isn't working (yet)… probably something I am not doing right. The sub continues on even after clicking the CmdClose.

I placed the "Public CancelEntry As Boolean" in "This workbook" as a general declaration (hope that was right). I placed all of the other code in the other correct areas as well.

Any ideas?

Thanks again.

Paul
 
Upvote 0
Dice711 said:
Hello Dave
I placed the "Public CancelEntry As Boolean" in "This workbook" as a general declaration (hope that was right).
Click to expand...

try placing in a STANDARD module as stated in my post.

Dave
 
Upvote 0
Hello Dave

Thanks again. I placed the public comment a standard module. Still the same, the sub still continues after clicking the close button.
 
Upvote 0
hi,
sorry my error replace UserForm_QueryClose procedure in DigitalSignatureSM form with updated version below & see if solves

Code: 
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
   If CloseMode = 0 Then CancelEntry = True
End Sub



Dave
 
Last edited:
Upvote 0
Dice711 said:
Hello Dave

I replaced the Private Sub... It is still continuing on through the Sub.
Click to expand...

can you publish all forms code or better place copy of your workbook in a dropbox?

Dave
 
Last edited:
Upvote 0
Thanks Dave

It's a lot. I placed most all of it below, except for the public declaration of "Public CancelEntry As Boolean" which is at the top of the module where below exists.



This is the main sub that starts the inventory process. The Call DigitalSignatureSM_Click comes in about 40 lines down (which again if the user(s) attempts to cancel/close I want to whole process to stop).




Sub Process_Returns_From_Pits()
'Returns Cards, Dice & Tiles to Inventory, whereby closing out the Gaming Day

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheets("Main").Select
'The following will copy and paste return of cards to "Cards" Main Form
Range("ReturnCards").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Cards").Select
Range("ReturnCardsMain").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Sheets("Main").Select
'The following will copy and paste return of dice to "Dice" Main Form
Range("ReturnDice").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Dice").Select
Range("ReturnDiceMain").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Sheets("Main").Select
'The following will copy and paste return of tiles to "Tiles" Main Form
Range("ReturnTiles").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Tiles").Select
Range("ReturnTilesMain").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Sheets("Main").Select

'**********
'The following will activate the Digital Signature subroutine

Call DigitalSignatureSM_Click

'The following will copy and paste the current signatures to the closing card form

Sheets("Log").Select
Range("CurrentNameSM").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Cards").Select
Range("SigSMClosingCards").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Sheets("Log").Select
Range("CurrentLicenseSM").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Cards").Select
Range("LicSMClosingCards").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Sheets("Log").Select
Range("CurrentNameGR").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Cards").Select
Range("SigGRClosingCards").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Sheets("Log").Select
Range("CurrentLicenseGR").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Cards").Select
Range("LicGRClosingCards").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

'The following will drop the actual signatures into the closing sheet



'The following will send the final closing Card Inventory to folder
Call SendPDF_To_File_Cards_Form_Final

'********** New Gaming Day starts here **********

'Make Closing Cards amount the new day Opening Amount
Sheets("Cards").Select
Range("CardsClosing").Select
Selection.Copy
Range("CardsOpening").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


'The following will send the final closing Dice Inventory to folder
Call SendPDF_To_File_Dice_Form

'Make Closing Dice amount the new day Opening Amount
Sheets("Dice").Select
Range("DiceClosing").Select
Selection.Copy
Range("DiceOpening").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'**********

'The following will send the final closing Tiles Inventory to folder
Call SendPDF_To_File_Tiles_Form

'Make Closing Tiles amount the new day Opening Amount
Sheets("Tiles").Select
Range("TilesClosing").Select
Selection.Copy
Range("TilesOpening").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'**********


'********** Purge Main Forms Area **********

'Purge Main Cards Form
Sheets("Cards").Select
Range("IssuedCardsMain").Select
Selection.ClearContents
Range("VendorCardsMain").Select
Selection.ClearContents
Range("AdditionalCardsMain").Select
Selection.ClearContents
Range("ReturnCardsMain").Select
Selection.ClearContents

'Purge Main Dice Form
Sheets("Dice").Select
Range("IssuedDiceMain").Select
Selection.ClearContents
Range("VendorDiceMain").Select
Selection.ClearContents
Range("AdditionalDiceMain").Select
Selection.ClearContents
Range("ReturnDiceMain").Select
Selection.ClearContents

'Purge Main Tiles Form
Sheets("Tiles").Select
Range("IssuedTilesMain").Select
Selection.ClearContents
Range("VendorTilesMain").Select
Selection.ClearContents
Range("AdditionalTilesMain").Select
Selection.ClearContents
Range("ReturnSecTilesMain").Select
Selection.ClearContents
Range("ReturnTilesMain").Select
Selection.ClearContents

'********** Purge Main Sheet Areas **********

'Purge Returns Area (Main Sheet)
Sheets("Main").Select
Range("ReturnCards").Select
Selection.ClearContents
Range("ReturnDice").Select
Selection.ClearContents
Range("ReturnTiles").Select
Selection.ClearContents

'Purge Vendor Delivery Area (Main Sheet)
Range("VendorCards").Select
Selection.ClearContents
Range("VendorDice").Select
Selection.ClearContents
Range("VendorTiles").Select
Selection.ClearContents

'Purge Additional Issuance Area (Main Sheet)
Range("AddCards").Select
Selection.ClearContents
Range("AddDice").Select
Selection.ClearContents
Range("AddTiles").Select
Selection.ClearContents

'The following will send the Opening Card Inventory to folder
Call SendPDF_To_File_Cards_Form_Opening


Sheets("Main").Select
Range("A1").Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
Application.DisplayAlerts = True
MsgBox "Your Return of Cards, Dice & Tiles has been recorded.", , "Return to Inventory"
Range("A1").Select

End Sub








This is all of the code related to the DigitalSignatureSM_Click:



Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = 0 Then CancelEntry = True
End Sub
Private Sub SMclose_Click()
CancelEntry = True
Unload Me
End Sub




Private Sub SM_Dropdown_AfterUpdate()

With Me
.SMTextBox = Application.WorksheetFunction.VLookup((Me.SM_Dropdown), Sheet8.Range("lookupSM"), 2, 0)
End With

End Sub
Private Sub GR_Dropdown_AfterUpdate()

With Me
.GRTextBox = Application.WorksheetFunction.VLookup((Me.GR_Dropdown), Sheet8.Range("lookupGR"), 2, 0)
End With

End Sub





Private Sub CmdClose_Click()
CancelEntry = True
Unload Me
End Sub





Private Sub SMsend_Click()

'This code makes all fields in the Userform mandatory

Dim ans As Long
For Each ctrl In Me.Controls
Select Case True
Case TypeOf ctrl Is MSForms.CheckBox, TypeOf ctrl Is MSForms.OptionButton
Select Case ctrl.Value
Case True
ctrl.BackColor = vbGreen
Case Else
ctrl.BackColor = vbRed: ans = ans + 1
End Select
Case TypeOf ctrl Is MSForms.TextBox, TypeOf ctrl Is MSForms.ComboBox
Select Case ctrl.Value
Case vbNullString
ctrl.BackColor = vbRed: ans = ans + 1
Case Else
ctrl.BackColor = vbGreen
End Select
End Select
Next ctrl
If ans > 0 Then
MsgBox "Please enter data in the required fields. Controls in Red need completion", vbInformation, "Enter Data"
Exit Sub
'Else
'MsgBox "Thanks, fields have been filled correctly"
End If

'This code drops off the information on the "Log" sheet

Sheet10.Activate
Range("A2").Select
ActiveCell.End(xlDown).Select
LastRow = ActiveCell.Row
Cells(LastRow + 1, 1).Value = SM_Dropdown.Text
Cells(LastRow + 1, 2).Value = SMTextBox.Text
Cells(LastRow + 1, 4).Value = GR_Dropdown.Text
Cells(LastRow + 1, 5).Value = GRTextBox.Text
Cells(LastRow + 1, 7).Value = Date + Time

'This code drops off the user information in the "Current Active Signature" area

Range("CurrentNameSM").Value = SM_Dropdown.Text
Range("CurrentLicenseSM").Value = SMTextBox.Text
Range("CurrentNameGR").Value = GR_Dropdown.Text
Range("CurrentLicenseGR").Value = GRTextBox.Text

'This code clears the form and places the cursor in A2

Range("A2").Select
SM_Dropdown.Text = ""
SMTextBox.Text = ""
GR_Dropdown.Text = ""
GRTextBox.Text = ""

Unload Me

End Sub




Private Sub UserForm_Initialize()

SM_Dropdown.BackColor = vbWhite
SMTextBox.BackColor = vbWhite
GR_Dropdown.BackColor = vbWhite
GRTextBox.BackColor = vbWhite

DigitalSignatureSM.SM_Dropdown.SetFocus

End Sub
 
Upvote 0
Sorry for the long post... just learning:
Code: 
Sub Process_Returns_From_Pits()
'Returns Cards, Dice & Tiles to Inventory, whereby closing out the Gaming Day

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheets("Main").Select
'The following will copy and paste return of cards to "Cards" Main Form
Range("ReturnCards").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Cards").Select
Range("ReturnCardsMain").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Sheets("Main").Select
'The following will copy and paste return of dice to "Dice" Main Form
Range("ReturnDice").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Dice").Select
Range("ReturnDiceMain").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Sheets("Main").Select
'The following will copy and paste return of tiles to "Tiles" Main Form
Range("ReturnTiles").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Tiles").Select
Range("ReturnTilesMain").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Sheets("Main").Select

'**********
'The following will activate the Digital Signature subroutine

Call DigitalSignatureSM_Click

'The following will copy and paste the current signatures to the closing card form

Sheets("Log").Select
Range("CurrentNameSM").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Cards").Select
Range("SigSMClosingCards").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Sheets("Log").Select
Range("CurrentLicenseSM").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Cards").Select
Range("LicSMClosingCards").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Sheets("Log").Select
Range("CurrentNameGR").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Cards").Select
Range("SigGRClosingCards").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Sheets("Log").Select
Range("CurrentLicenseGR").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Cards").Select
Range("LicGRClosingCards").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

'The following will drop the actual signatures into the closing sheet



'The following will send the final closing Card Inventory to folder
Call SendPDF_To_File_Cards_Form_Final

'********** New Gaming Day starts here **********

'Make Closing Cards amount the new day Opening Amount
Sheets("Cards").Select
Range("CardsClosing").Select
Selection.Copy
Range("CardsOpening").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


'The following will send the final closing Dice Inventory to folder
Call SendPDF_To_File_Dice_Form

'Make Closing Dice amount the new day Opening Amount
Sheets("Dice").Select
Range("DiceClosing").Select
Selection.Copy
Range("DiceOpening").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'**********

'The following will send the final closing Tiles Inventory to folder
Call SendPDF_To_File_Tiles_Form

'Make Closing Tiles amount the new day Opening Amount
Sheets("Tiles").Select
Range("TilesClosing").Select
Selection.Copy
Range("TilesOpening").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'**********


'********** Purge Main Forms Area **********

'Purge Main Cards Form
Sheets("Cards").Select
Range("IssuedCardsMain").Select
Selection.ClearContents
Range("VendorCardsMain").Select
Selection.ClearContents
Range("AdditionalCardsMain").Select
Selection.ClearContents
Range("ReturnCardsMain").Select
Selection.ClearContents

'Purge Main Dice Form
Sheets("Dice").Select
Range("IssuedDiceMain").Select
Selection.ClearContents
Range("VendorDiceMain").Select
Selection.ClearContents
Range("AdditionalDiceMain").Select
Selection.ClearContents
Range("ReturnDiceMain").Select
Selection.ClearContents

'Purge Main Tiles Form
Sheets("Tiles").Select
Range("IssuedTilesMain").Select
Selection.ClearContents
Range("VendorTilesMain").Select
Selection.ClearContents
Range("AdditionalTilesMain").Select
Selection.ClearContents
Range("ReturnSecTilesMain").Select
Selection.ClearContents
Range("ReturnTilesMain").Select
Selection.ClearContents

'********** Purge Main Sheet Areas **********

'Purge Returns Area (Main Sheet)
Sheets("Main").Select
Range("ReturnCards").Select
Selection.ClearContents
Range("ReturnDice").Select
Selection.ClearContents
Range("ReturnTiles").Select
Selection.ClearContents

'Purge Vendor Delivery Area (Main Sheet)
Range("VendorCards").Select
Selection.ClearContents
Range("VendorDice").Select
Selection.ClearContents
Range("VendorTiles").Select
Selection.ClearContents

'Purge Additional Issuance Area (Main Sheet)
Range("AddCards").Select
Selection.ClearContents
Range("AddDice").Select
Selection.ClearContents
Range("AddTiles").Select
Selection.ClearContents

'The following will send the Opening Card Inventory to folder
Call SendPDF_To_File_Cards_Form_Opening


Sheets("Main").Select
Range("A1").Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
Application.DisplayAlerts = True
MsgBox "Your Return of Cards, Dice & Tiles has been recorded.", , "Return to Inventory"
Range("A1").Select

End Sub

This is the CancelEntry code related to the DigitalSignatureSM_Click:

Code: 
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = 0 Then CancelEntry = True
End Sub
Private Sub SMclose_Click()
CancelEntry = True
Unload Me
End Sub


This is the two dropdown boxes:

Code: 
Private Sub SM_Dropdown_AfterUpdate()

With Me
.SMTextBox = Application.WorksheetFunction.VLookup((Me.SM_Dropdown), Sheet8.Range("lookupSM"), 2, 0)
End With

End Sub


Private Sub GR_Dropdown_AfterUpdate()

With Me
.GRTextBox = Application.WorksheetFunction.VLookup((Me.GR_Dropdown), Sheet8.Range("lookupGR"), 2, 0)
End With

End Sub

This is the CmdClose sub:

Code: 
Private Sub CmdClose_Click()
CancelEntry = True
Unload Me
End Sub

This is the "send" code that makes all fields mandatory:

Code: 
Private Sub CmdClose_Click()
CancelEntry = True
Unload Me
End Sub


This is the initialize code:

Code: 
Private Sub UserForm_Initialize()

SM_Dropdown.BackColor = vbWhite
SMTextBox.BackColor = vbWhite
GR_Dropdown.BackColor = vbWhite
GRTextBox.BackColor = vbWhite

DigitalSignatureSM.SM_Dropdown.SetFocus

End Sub


Thanks again for any help.

Paul
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Sense checking UserFrom code
Replies
0
Views
430
AndyEd
A
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
A
  • Solved
Exception to the obligation to fill in all fields of a UserForm
Replies
2
Views
578
Aretradeser
A
A
  • Solved
Calendar issue
Replies
1
Views
2K
AndyEd
A
L
  • Question Question
VBA works on work PC but not home PC
Replies
3
Views
2K
rollis13
rollis13

Forum statistics

Threads
1,223,893
Messages
6,175,249
Members
452,623
Latest member
Techenthusiast

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top