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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
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
gogetsomeAuthor Commented:
Thank you everyone for your help. Without good people like yourselves, I would not be where I am today.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.