Need help composting a UI prompt

[mikerickson]

There is going to be a meeting of volunteers to help with an event.
I have a workbook to help me sign up volunteers for my area.

The user fills out a userform with Name, Email and Phone, checks some checkboxes and presses the SUBMIT button.

The code for this works well, the data goes where it should.

If the Email is already in the data base, a mesage box comes up with three options:

1) Write the userform's data to the spreadsheet, duplicating the entry. Unload the UF.
2) Write nothing. Unload the UF.
3) Return to the userform.

I have code that does all this.
I also have a lousy prompt in the MsgBox.

"That e-Mail address duplicates an existing entry."

"Ignore - add this duplicated e-mail entry to the list."
"Retry - don't add. I need to change this form."
"Abort - I forgot that I already signed-up."
(Ignore is the default)

I need help composing an informative message to untrained volunteers.
Yes/No/Cancel is another option for buttons.

I've heard it said that "too much detail" is an oxymoron, so;

The purpose of this workbook is to sign up volunteers for the 40th annual
free Davis Whole Earth Festival on Mother's Day weekend (May 8, 9, 10) in Davis CA.
Started in 1969, its the longest running free festival on the West Coast.
I've worked on WEF for 30 years. This year I'm the manager at Cedar Stage and have a crew to lead. I'll be backstage with these volunteers all weekend long.

Can anyone think of a better user prompt?

Thanks.

 

[ZVI]

Hi Mike,

I would recommend UserForm instead of MsgBox.

Lets assume that two forms are used: frmMain and frmDublicateFound

Controls of frmDublicateFound:

<font face=Courier New>
CommandButton       ControlTipText
---------------------------------------------------
cmdSave             Save dublicate anyway and exit
cmdEdit             Edit your form again
cmdExit             Exit without saving dublicate
</FONT>

Global variable GlobalRet can be used for identifying of the chosen action (CommandButton).
Its value can be equal to one of three global constants:
GSAVE = 1, GEDIT = 2, GEXIT = 3

Appearance of frmDublicateFound:

<font face=Courier New>
--------------------------------
      W A R N I N G:
Your e-mail is already present
      in database

    Choose your action

  [Save]   [Edit]   [Exit]
--------------------------------
</FONT>

Warning message is a background form picture copied from autoshape.

The code of standard VBA-module:

<font face=Courier New>
' Code of standard VBA module
Global Const GSAVE = 1, GEDIT = 2, GEXIT = 3
Global GlobalRet As Integer

Sub Start()
  frmMain.Show
End Sub</FONT>

The code of frmDublicateFound:

<font face=Courier New>
' Code of frmDublicateFound

Private Sub cmdSave_Click()
  GlobalRet = GSAVE
  Unload Me
End Sub

Private Sub cmdEdit_Click()
  GlobalRet = GEDIT
  Unload Me
End Sub

Private Sub cmdExit_Click()
  GlobalRet = GEXIT
  Unload Me
End Sub

Private Sub UserForm_Activate()
  GlobalRet = GEXIT ' For the case of quire closing
End Sub</FONT>

The code of frmMain:

<font face=Courier New>
' Code of frmMain

Private Sub cmdOk_Click()
  
  lblRetValue = "WARNING" ' <- debug label
  frmDublicateFound.Show  ' <- call warning form
  
  ' Action according to global variable
  Select Case GlobalRet
    Case GSAVE
      ' -> for debug
      lblRetValue = "SAVING"
      Application.Wait Now + TimeValue("0:00:02")
      ' <-
      'call MySaving
      Unload Me
      
    Case GEDIT
      'Nothing to do
      lblRetValue = "EDITING"
      
    Case GEXIT
      ' -> for debug
      lblRetValue = "EXITING"
      Application.Wait Now + TimeValue("0:00:02")
      ' <-
      Unload Me
      
  End Select
End Sub</FONT>

Example (17KB) can be downloaded from here: Warning_Form.zip

Regards,
Vladimir

 

[Greg Truby]

Is there some particular reason why you didn't just have the form process the exit event on both the phone # & e-mail address and do a lookup at the time it's entered and autopopulate the form with the rest of the data? Then the person enter the data can see whether they need to create a new record or if the old one is already good.

 

[mikerickson]

Greg,
I like the way that your idea for a UI would work. It seems a smoother interface that what I have.

 

[Gettingbetter]

Im sure you already know this (in fact I think I got this code from you years ago) but you could always use a custom msgbox if you dont want another form

Option Explicit
Private MSGHOOK As MSGBOX_HOOK_PARAMS
Private Declare Function CallNextHookEx Lib "user32" (ByVal hHook As Long, ByVal ncode As Long, ByVal wParam As Long, lParam As Any) As Long
Private Declare Function GetModuleHandle Lib "kernel32" Alias "GetModuleHandleA" (ByVal lpModuleName As String) As Long
Private Declare Function SetWindowsHookEx Lib "user32" Alias "SetWindowsHookExA" (ByVal idHook As Long, ByVal lpfn As Long, ByVal hmod As Long, ByVal dwThreadId As Long) As Long
Private Declare Function UnhookWindowsHookEx Lib "user32" (ByVal hHook As Long) As Long
Private Declare Function SendDlgItemMessage Lib "user32" Alias "SendDlgItemMessageA" (ByVal hDlg As Long, ByVal nIDDlgItem As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
Private Declare Function GetClassName Lib "user32" Alias "GetClassNameA" (ByVal hwnd As Long, ByVal lpClassName As String, ByVal nMaxCount As Long) As Long
Private Declare Function GetCurrentThreadId Lib "kernel32" () As Long
Public Declare Function GetDesktopWindow Lib "user32" () As Long
Private Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long) As Long
Private Declare Function MessageBox Lib "user32" Alias "MessageBoxA" (ByVal hwnd As Long, ByVal lpText As String, ByVal lpCaption As String, ByVal wType As Long) As Long
Private Declare Function SetDlgItemText Lib "user32" Alias "SetDlgItemTextA" (ByVal hDlg As Long, ByVal nIDDlgItem As Long, ByVal lpString As String) As Long
Private Declare Function SetWindowText Lib "user32" Alias "SetWindowTextA" (ByVal hwnd As Long, ByVal lpString As String) As Long
Private Const EM_SETPASSWORDCHAR = &HCC
Private Const WH_CBT = 5
Private Const HCBT_ACTIVATE = 5
Private Const HC_ACTION = 0
Private hHook As Long
Private Const MB_YESNOCANCEL = &H3&
Private Const MB_YESNO = &H4&
Private Const MB_RETRYCANCEL = &H5&
Private Const MB_OKCANCEL = &H1&
Private Const MB_OK = &H0&
Private Const MB_ABORTRETRYIGNORE = &H2&
Private Const MB_ICONEXCLAMATION = &H30&
Private Const MB_ICONQUESTION = &H20&
Private Const MB_ICONASTERISK = &H40&
Private Const MB_ICONINFORMATION = MB_ICONASTERISK
Private Const IDOK = 1
Private Const IDCANCEL = 2
Private Const IDABORT = 3
Private Const IDRETRY = 4
Private Const IDIGNORE = 5
Private Const IDYES = 6
Private Const IDNO = 7
Private Const IDPROMPT = &HFFFF&
Private Const GWL_HINSTANCE = (-6)
Private Type MSGBOX_HOOK_PARAMS
  hwndOwner As Long
  hHook As Long
End Type
Dim mbFlags As VbMsgBoxStyle
Dim mbFlags2 As VbMsgBoxStyle
Dim mTitle As String
Dim mPrompt As String
Dim But1 As String
Dim But2 As String
Dim But3 As String
Public Function MessageBoxH(hwndThreadOwner As Long, hwndOwner As Long, mbFlags As VbMsgBoxStyle) As Long
Dim hInstance As Long
Dim hThreadId As Long
hInstance = GetWindowLong(hwndThreadOwner, GWL_HINSTANCE)
hThreadId = GetCurrentThreadId()
With MSGHOOK
  .hwndOwner = hwndOwner
  .hHook = SetWindowsHookEx(WH_CBT, AddressOf MsgBoxHookProc, hInstance, hThreadId)
End With
MessageBoxH = MessageBox(hwndOwner, Space$(120), Space$(120), mbFlags)
End Function
Public Function MsgBoxHookProc(ByVal uMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
If uMsg = HCBT_ACTIVATE Then
  SetWindowText wParam, mTitle
  SetDlgItemText wParam, IDPROMPT, mPrompt
  Select Case mbFlags
  Case vbAbortRetryIgnore
    SetDlgItemText wParam, IDABORT, But1
    SetDlgItemText wParam, IDRETRY, But2
    SetDlgItemText wParam, IDIGNORE, But3
  Case vbYesNoCancel
    SetDlgItemText wParam, IDYES, But1
    SetDlgItemText wParam, IDNO, But2
    SetDlgItemText wParam, IDCANCEL, But3
  Case vbOKOnly
    SetDlgItemText wParam, IDOK, But1
  Case vbRetryCancel
    SetDlgItemText wParam, IDRETRY, But1
    SetDlgItemText wParam, IDCANCEL, But2
  Case vbYesNo
    SetDlgItemText wParam, IDYES, But1
    SetDlgItemText wParam, IDNO, But2
  Case vbOKCancel
    SetDlgItemText wParam, IDOK, But1
    SetDlgItemText wParam, IDCANCEL, But2
  End Select
  UnhookWindowsHookEx MSGHOOK.hHook
End If
MsgBoxHookProc = False
End Function
Public Function BBmsgbox(mhwnd As Long, mMsgbox As VbMsgBoxStyle, Title As String, Prompt As String, Optional mMsgIcon As VbMsgBoxStyle, Optional ButA As String, Optional ButB As String, Optional ButC As String) As String
Dim CustomText As Long
mbFlags = mMsgbox
mbFlags2 = mMsgIcon
mTitle = Title
mPrompt = Prompt
But1 = ButA
But2 = ButB
But3 = ButC
CustomText = MessageBoxH(mhwnd, GetDesktopWindow(), mbFlags Or mbFlags2)
Select Case CustomText
  Case IDABORT
    BBmsgbox = But1
  Case IDRETRY
    BBmsgbox = But2
  Case IDIGNORE
    BBmsgbox = But3
  Case IDYES
    BBmsgbox = But1
  Case IDNO
    BBmsgbox = But2
  Case IDCANCEL
    BBmsgbox = But3
  Case IDOK
    BBmsgbox = But1
  End Select
End Function
Sub CustomMessage()
Dim CustomText As String
CustomText = BBmsgbox(1, 3, "Duplicated Email Used", "A form with this email already exists, what would you like to do?", 32, "Add Form", "Edit Form", "Cancel")
If CustomText = "Add Form" Then
  MsgBox "Do 'Add Form' stuff", , "Add"
ElseIf CustomText = "Edit Form" Then
  MsgBox "Do 'Edit Form' stuff", , "Add"
Else
  MsgBox "Do 'Cancel' stuff", , "Add"
  Exit Sub
End If
End Sub

Cheers
GB

 

[mikerickson]

The MsgBox works fine. The problem is what words should it to show.

The question is not on the machine side of the User Interface, but the user side.

I have written VB instructions to make the computer do what I want it to.
I need help writting instructions (message box prompt) to make the user do what I want them to.

 

[arkusM]

The MsgBox works fine. The problem is what words should it to show.

The question is not on the machine side of the User Interface, but the user side.

I have written VB instructions to make the computer do what I want it to.
I need help writting instructions (message box prompt) to make the user do what I want them to.

Mike,
Maybe something like the following?
Where First Last and xxxx@xxx.com are pulls of the information entered so it is clear what info is in question.
Hope this helps!

Mark
---------------------------------------------------

It appears that First Last
with the email address: xxxx@xxx.com
already exsists in the database.

Would you like to:
- add First Last again? {Button} Yes
- go back and edit First Last's information? {Button} Re-Enter
- Discard this entry and proceed? {Button} Discard

 

[mikerickson]

I've almost convinced myself to omit the MsgBox, saving everything and including time stamps with both the original and the duplicate entry.
Multiple entries for one e-mail can be handled by calling the volunteer and asking.
Personal contact outside of meetings is a good crew building technique. Leading volunteers, who exchange their labor for admission to a festival that is free to the public, and getting them to show up on time at 9am demands more personal contact than other situations.

 

[arkusM]

I've almost convinced myself to omit the MsgBox, saving everything and including time stamps with both the original and the duplicate entry.
Multiple entries for one e-mail can be handled by calling the volunteer and asking.
Personal contact outside of meetings is a good crew building technique. Leading volunteers, who exchange their labor for admission to a festival that is free to the public, and getting them to show up on time at 9am demands more personal contact than other situations.

Life is always a series of trade-offs eh? <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
But, do you want to contact with the volunteers to be initiated because they made an "error".<o></o>
As someone who volunteers my time, I personally am contentious of doing things right and not having someone else having to go back and fix my mistakes (I don’t want others to spend time one what they were relying on me to do). But that is me. I would not pretend to understand the nuances of your event and volunteers.<o></o>
Good luck to you! <o></o>

 

[mikerickson]

What I settled on is a MsgBox with the prompt

"aName@domain.com" has already been submitted
Is "aName@domain.com" spelled correctly?

Yes(default)-save with timestamp No-return to userform

If the user wants to submit nothing, they have to use the CANCEL button on the UF.