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
 

Colin Legg

MrExcel MVP, Like totally RAD man
Joined
Feb 28, 2008
Messages
3,497
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?
 

Maverick99

Board Regular
Joined
Feb 9, 2008
Messages
108
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
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,361
Office Version
365
Platform
Windows
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

Board Regular
Joined
Feb 9, 2008
Messages
108
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
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,361
Office Version
365
Platform
Windows
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?
 

Maverick99

Board Regular
Joined
Feb 9, 2008
Messages
108
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,361
Office Version
365
Platform
Windows
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

Board Regular
Joined
Feb 9, 2008
Messages
108
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
 

Forum statistics

Threads
1,081,415
Messages
5,358,533
Members
400,502
Latest member
price83

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top