To Call a Sub from a Case Statement

Maverick99

Board Regular
Joined
Feb 9, 2008
Messages
108
I have run across some code that uses Case Statements, based upon a user's Button Selection from a UserForm. This is located in the UserForm Event Module.

I thought this would be straight forward to have it call a Sub "WriteToLog," if the Case Statement is true. However, it's not working for some reason. Does anyone know how this should be written. I have tried to just Call Sub, but does not work.

Code:
Private Sub Button1_Click()
    Select Case Button1.Caption
        Case "OK": UserClick = vbOK
        Case "Cancel": UserClick = vbCancel
        Case "Abort": UserClick = vbAbort
        Case "Retry": UserClick = vbRetry
        Case "Ignore": UserClick = vbIgnore
        Case "Yes": UserClick = vbYes
            Private Sub WriteToLog()   'Does Not Work, it expects End of Sub from the "Yes" Case
                 Call WriteToLog
            Exit Sub
        Case "No": UserClick = vbNo
    End Select
    Unload Me
End Sub
Thanks,

Maverick
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
This layout should work fine:
Code:
[COLOR=blue]Private[/COLOR] [COLOR=blue]Sub[/COLOR] Button1_Click()
    [COLOR=blue]Select[/COLOR] [COLOR=blue]Case[/COLOR] Button1.Caption
        [COLOR=blue]Case[/COLOR] "OK": UserClick = vbOK
        [COLOR=blue]Case[/COLOR] "Cancel": UserClick = vbCancel
        [COLOR=blue]Case[/COLOR] "Abort": UserClick = vbAbort
        [COLOR=blue]Case[/COLOR] "Retry": UserClick = vbRetry
        [COLOR=blue]Case[/COLOR] "Ignore": UserClick = vbIgnore
        [COLOR=blue]Case[/COLOR] "Yes": UserClick = vbYes
            [COLOR=blue]Call[/COLOR] WriteToLog
        [COLOR=blue]Case[/COLOR] "No": UserClick = vbNo
    [COLOR=blue]End[/COLOR] [COLOR=blue]Select[/COLOR]
    [COLOR=blue]Unload[/COLOR] [COLOR=blue]Me[/COLOR]
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]
 
[COLOR=blue]Private[/COLOR] [COLOR=blue]Sub[/COLOR] WriteToLog()
    [COLOR=green]'code to perform this task in here[/COLOR]
 
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]

I take it UserClick is declared somewhere?
 
Upvote 0
Yes, the User Click is in the Function Module:

Code:
Function MyMsgBox(ByVal Prompt As String, Optional ByVal Buttons As Integer, Optional ByVal Title As String) As Integer

MyMsgBox = UserClick

End Function
Colin, Since you're an MVP would you mind answering one quick question for me?

The code takes DECTOBIN and turns an integer into a Binary String:

3 = 0000000011

Another set of Case Statements uses a Mid function and looks for a Value from this:

eg. 0000000011 to "011" to be used as:

Code:
 Case "011" 'Exclamation
            With ImageExlamation
                .Visible = True
                .Left = 10
                .Top = 8
            End With
I don't think the Mid statement is working correctly. As I step through the code, it just passes right over this. How should the Mid Statement be written to work with this Case Statement.

Thanks for your insight..

Maverick
 
Upvote 0
Maverick

Your question seems to be related to a Mid statement, yet you've not posted any code that uses one.

I think it might help to see that.:)
 
Upvote 0
Sorry,

This is the Mid statement.

Code:
Select Case Mid(BinString, 4, 3)
But, I was going to post for Colin, that the WriteToLog produces an Error.

Code:
Private Sub Button2_Click()
    Select Case Button2.Caption
        Case "OK": UserClick = vbOK
        Case "Cancel": UserClick = vbCancel
          Call WriteToLog
        Case "Abort": UserClick = vbAbort
        Case "Retry": UserClick = vbRetry
        Case "Ignore": UserClick = vbIgnore
        Case "Yes": UserClick = vbYes
        Case "No": UserClick = vbNo
    End Select
    Unload Me
End Sub


Private Sub WriteToLog()
  Call WriteToLog  ' I'm assuming that I can activate the other module with all its code...
End Sub
The Error is the Private Sub WriteToLog()
---- Ambiguous name detected WriteToLog

Maverick
 
Upvote 0
Why do you have a call to a sub called WriteToLog within a sub called WriteToLog.:eek:

As for the Mid problem, I suggest that you assign the results of the function to a variable and use that in the Select Case.

Then you should be able to set a watch on that variable and step through the code and see what's a-happening.:)

By the way what is that userform code meant to do?

Why is the Select Case based on the caption of a button?
 
Upvote 0
Norie,

I read that one should keep "Business Logic" code from separate from the "Event Codes" to better encapsulate each process, that's why. I don't know if it's right or wrong, just what I read.

The UserForm is a Custom Message Box, that allows a couple more tweaks than Excel's Message Box. But, if this can't be worked out, then I'll just go with the Standard. Although, I would still need to trim the numbers down.

Obviously, it also produces a text string, since it is in " ".

Maverick
 
Upvote 0
Maverick

What's 'Business Logic' code and where did you read about it?

Sure certain types of code should be separated, and certain types of code must go in specific locations.

By the way what, in words, are you trying to do?

Why are you basing the Select Case on the caption of a button?

The only way to change the caption on a userform is in design mode or progrmatically.

ie the user can't do it manually

If you want to allow the user to make some sort of choice then there are probably better ways.

eg listbox, combobox, option buttons, check boxes etc
 
Upvote 0
Norie,

I read about business logic in PED2. However, I just figured out the Message box display dilemma. After posting to you, it came to me to use the:

Code:
Right(BinString,3)
and now it works fine...well for this portion. This was to put one of the Message box icons on the "Are you sure you want to do this" type of Message Box. If they select Yes, then that fires off a number of events to record in a Dept. Log file what they just filled out in the UserForm.

Since this is a minor form, I really don't want to include all of the necessary code for these events. This code/message will be fired from the main UserForm. From that main UserForm, I Call the primary Write module.

Maverick
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top