To Call a Sub from a Case Statement

[Maverick99]

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.

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

 

[Colin Legg]

This layout should work fine:

[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?

 

[Maverick99]

Yes, the User Click is in the Function Module:

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:

 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

 

[Norie]

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.

 

[Maverick99]

Sorry,

This is the Mid statement.

Select Case Mid(BinString, 4, 3)

But, I was going to post for Colin, that the WriteToLog produces an Error.

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

 

[Norie]

Why do you have a call to a sub called WriteToLog within a sub called WriteToLog.

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?

 

[Maverick99]

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

 

[Norie]

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

 

[Maverick99]

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:

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