Help with mesg box vbOkCancel

abordeau · #1 05-22-2014, 11:11 AM

Hi All,
I have a form with the following code:

Code:

' PointsUsed tab (on form)
Private Sub ComboClockuse_Change()
Dim lblemplnameUSE As String
Dim sheet As Worksheet
Set sheet = ActiveWorkbook.Sheets("Totals")
Dim Ret_Type As Integer
If Trim(Me.ComboClockUse.Value) = "" Then
  Me.ComboClockUse.SetFocus
  Exit Sub
  End If
 
'if vlookup encounters an error
On Error GoTo ErrHandler:
'Vlookup to find employee name
    Me.lblemplnameUSE = Application.WorksheetFunction.VLookup(ComboClockUse.Value, sheet.Range("EmpClock"), 2, False)
    Me.lblAvailPts = Application.WorksheetFunction.VLookup(ComboClockUse.Value, sheet.Range("AvailPts"), 11, False)
 
    Exit Sub
 
ErrHandler:
  Ret_Type = Msgbox("Clock Number not found.  Click OK to enter new employee", vbOKCancel, "Invalid Clock Number")
   ' if ok or cancel buttons are pressed
Select Case Ret_Type
Case 1 'ok
    Unload Me
        Copper_Crusaders_Points.Show
    MultiPage1.Value = 2
Case 2 'cancel
    Unload Me
        Copper_Crusaders_Points.Show
      MultiPage1.Value = 1
End Select
Exit sub
End Sub

My problem is that no matter which option button is selected, all I get is the results for Case 2 and then the vlookup doesn't work. So if the user wants to cancel and reenter a new clock number they have to close the form and manually reopen it.
this was working until I decided I wanted a vbOKCancel instead of a VbOkOnly.
What should I do?
Thanks,
Angelina
PS I uploaded a sample and provided a link to onedrive for those who would like to take a look at the whole project.
https://onedrive.live.com/?cid=B3C47...091AE166%21105

BobBridges · #2 05-22-2014, 02:54 PM

Angelina, I don't use forms in Excel much—ok, I'm not sure I've ever used one in Excel, or maybe once—but I do it in Access some, and I think I see a problem. Let's see whether I follow this. First, if I indent your code to be sure I understand it correctly, it would look like this, right?

Code:

Ret_Type = Msgbox("Clock Number not found.  Click OK to enter new employee", vbOKCancel, "Invalid Clock Number")
Select Case Ret_Type
  Case 1 'ok
    Unload Me
    Copper_Crusaders_Points.Show
    MultiPage1.Value = 2
  Case 2 'cancel
    Unload Me
    Copper_Crusaders_Points.Show
    MultiPage1.Value = 1
  End Select

And 2) the object "Me" is the form itself?

Well, isn't that probably the problem? Whichever button you push in response to the MsgBox, the first thing it does is Unload Me. Immediately the form is unloaded, the code stops executing, and the user is left at that point to his own devices.

Certainly I would assume that the Unload statement should come last, after the Show method and setting the value of MultiPage1, like this:

Code:

Ret_Type = Msgbox("Clock Number not found.  Click OK to enter new employee", vbOKCancel, "Invalid Clock Number")
Copper_Crusaders_Points.Show
Select Case Ret_Type
  Case 1: MultiPage1.Value = 2 'Ok
  Case 2: MultiPage1.Value = 1 'Cancel
  End Select
Unload Me

There are other ways to do it, of course. And maybe even this won't work, but certainly the Unload should come later, no?

macropod · #3 05-22-2014, 04:26 PM

The 'Unload Me' doesn't belong there at all IMHO.