Select case with msgBox not working

Hello, I'm trying to get confirmation from the user with a yes/no message box before I populate a grid with data from the database. The case vbNo is working correctly by exiting the procedure, yet the case VBYes is not running the code necessary to populate the grid.

Could someone please look at my code and help figure out why?

Here is the code:


Select Case MsgBox("Are you sure you want to use the schedule 70?" _
, vbYesNo)
Case vbYes
'Get 70 data to grid
On Error GoTo ErrorHandler

    CancelLogic = True
   
    Dim userinfo As New RetrieveGlobals.retrieveuserinfo
   
    Dim adCNN As ADODB.Connection
    Dim adrst As ADODB.Recordset
    Dim strcmd As String
    Dim strCnn As String
   
    Dim response As Variant

    sqluid = userinfo.retrieve_user()
    sqlpwd = userinfo.sql_password()
    SQLdb = userinfo.intercompany_id()
    sqlsvr = userinfo.sql_datasourcename()
   
    With CLIN_Filter
        .user = sqluid
        .password = sqlpwd
        .SERVER = sqlsvr
        .DataBase = SQLdb
       
        .Filter_Type = "CLIN"
        .Height = 7000
        .dHeight = 3750
        .Width = 8500
        .dWidth = 7800
        .addSorts = 2
        .reportCount = 4
       
        .sp_Name = "csp_ws_lookup_item_clin_Only70"
         
         response = .load_view

        CLIN_Filter_RowReturned response

    End With
   
    Exit Sub
   
ErrorHandler:

    Exit Sub
   
Case vbNo
Exit Sub

End Select

End Sub
gogetsomeAsked:
Who is Participating?
 
Carl TawnSystems and Integration DeveloperCommented:
Its possibly erroring, your error handler doesn't do anything. Try adding a msgbox into the error handler to see if it is dropping into there.
0
 
ComputronCommented:
Replace the top like this

Select Case MsgBox("Are you sure you want to use the schedule 70?" , vbYesNo)
Case vbYes
MsgBox "I Clicked Yes"
'Get 70 data to grid
On Error GoTo ErrorHandler

I think you'll find it works
0
 
nffvrxqgrcfqvvcCommented:
You dont need to exit the sub if they click no because in the select case statement there is nothing to execute, below is another way to use select case for user choice

Dim Choice As Long
   
    Choice = MsgBox("some message", vbYesNoCancel)
   
    Select Case Choice
        Case vbYes
            Debug.Print "yes"
        Case vbNo
            Debug.Print "no"
        Case vbCancel
            Debug.Print "cancel"
    End Select
0
 
gogetsomeAuthor Commented:
Thank you everyone for your help. Without good people like yourselves, I would not be where I am today.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.