#1
|
|||
|
|||
Help with mesg box vbOkCancel
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 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 |
#2
|
||||
|
||||
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 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 |
#3
|
||||
|
||||
The 'Unload Me' doesn't belong there at all IMHO.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|