Consolidating Macros

[tvkrys]

Hi all -

I have a file that requires multiple operations which I have broken up into several macros. I would like to string them together into one macro but there are two problems. I also have a related question.

1. Can a macro call up another macro or must the full code be added to a combined macro? If I can call it up, what would the code look like? Let’s say the macro to be called up is named “DeleteLeft” and is located in my PERSONAL.XLS file.

2. At the end of one macro, there is a decision to be made. If the answer is yes, I run a second macro. If the answer is no, I run a third macro, and then the second one. Is there a way to create a pop-up window at the end of the first macro that asks me if there is a cash issue, with a yes and a no button. Then, based on my answer, run the appropriate macro(s)? If so, what would the code look like?

3. The other problem is that there may or may not be an undisclosed number of links that must be removed. At the time, they are all within an established selection. Currently, I just keep clicking on a button that runs a custom “RemoveLink” macro. When there are no more links to remove, I get a run time error message, click “End” and then click on the next macro. (That way I don’t have to count how many links there are to remove. I just keep clicking till I get the error message.) Is there a way to have the macro run a loop until it runs out of an undetermined number of links to remove and then continue on with another macro or code? (The number of links to be removed numbers anywhere from 0 to 76, if that matters.)

I hope this all makes sense...
TIA, Krys

 

[hsk]

Sub all_macro()
    x = MsgBox("Enter Choise", vbYesNoCancel)
    Select Case x
        Case 6
            ' if responce is  "Yes"
            call macro1
        Case 7
            ' if responce is  "No"
            call macro2 
        Case 2
            'if responce is  "Cacel"
            call macro3 ( or what ever action u want)
   End Select
End Sub

See if this helps

EDIT: Changed Quote to Code tags - Moderator

 

[tvkrys]

hsk

Thanks for your help. I'm trying to apply it to my situation. I have never worked with MsgBox before, so I have several questions...

1. Once the Yes, No and Cancel instructions are spelled out in the macro, can I continue on with steps that they now have in common following the Yes/No resolution, or do I have to end the macro and start a new one? If I can continue, how do I indicate that I’ve joined them up?

2. If I can continue, can I put another MsgBox later in the macro? If so, is the second one also prefaced by “x = ” or do I use a different letter?

3. What are the Case numbers about? I would think that they would be Case 1, Case 2, and Case 3. Why aren’t they? And what are the other Cases? Would the numbers match or be different if I can have more than one MsgBox in a macro?

4. Also, if I can continue, how do I say if you choose this, don’t do anything?

5. How do I say if you choose No OR Cancel, exit the macro?


Thanks again! Krys

 

[hsk]

1. Once the Yes, No and Cancel instructions are spelled out in the macro, can I continue on with steps that they now have in common following the Yes/No resolution, or do I have to end the macro and start a new one? If I can continue, how do I indicate that I’ve joined them up?

2. If I can continue, can I put another MsgBox later in the macro? If so, is the second one also prefaced by “x = ” or do I use a different letter?

x = MsgBox("Enter Choice", vbYesNoCancel)

This will display the message box with 3 choices to user. The macro holds for the response. U press any of the button, the resumes and variable X is loaded with appropriate value.
That means , if user presses 'Yes' button then value 6 is assigned to X
and so on .....
This way macro can understand the response from user.
This is just to demonstrate the use of msgbox with Yes/No/Cancel buttons.
Suppose u want to follow certain steps if the user presses Yes button, then code these steps in macro1 and call that macro if X = 6 ....

3. What are the Case numbers about? I would think that they would be Case 1, Case 2, and Case 3. Why aren’t they? And what are the other Cases? Would the numbers match or be different if I can have more than one MsgBox in a macro?

Select Case x 
        Case 6 
           call macro1
        Case 7
           call macro2
.........

This is a syntax which means,
if x=6 then
call macro1
else
if x = 7 then
call macro2
else .............

(' if response is "Yes" ---- this is a comment and will not be executed, probably u know that !!! )

4. Also, if I can continue, how do I say if you choose this, don’t do anything?

Select Case x 
        Case 6 
                   call macro1 
        Case 7 
                   call macro2 
        Case 2 
                   call macro3 ( or what ever action u want) 
        Case else 
                   msgbox 'Invalid choice'
End Select

This means if response is anything other than yes/no or cancel the msgbox will be displayed ........
But as u can see ur 1st msgbox will have three buttons Yes / No / Cancel so user has to select one from these 3 ..... now if don't want to give choice to user then in place of
x = MsgBox("Enter Choice", vbYesNoCancel)
u can use
x = MsgBox("Enter Choice", vbOKCancel)
or
x = MsgBox("Enter Choice", vbOkOnly)
or just
x = MsgBox("Enter Choice", vbYesNo)

5. How do I say if you choose No OR Cancel, exit the macro?

Sub all_macro() 
    x = MsgBox("Enter Choice", vbYesNoCancel) 
    Select Case x 
        Case 6 
                call macro1 
        Case 7 
                msgbox 'No action taken'
        Case 2 
                msgbox 'No action taken'
    End Select 
End Sub
.
.
.
sub macro1()
.......
end sub

This means if user select Yes then call macro1, else if user selects No or Cancel just display msg 'No action taken' and end macro.
In this case U do not need to code anything extra to end macro.
This is how the macro will proceed,
Display Msgbox with msg - 'Enter Choice' - with 3 buttons
If user pressed Yes then go to macro1, execute all the steps in macro1, on end sub in macro1 return to all_macro.
execute next statement after 'end select' (which is this case is end sub so the macro all_macro will be ended)

If user pressed No or Cancel then display msgbox - 'No action taken'
execute next statement after 'end select' (which is this case is end sub so the macro all_macro will be ended)

Probably this answers your questions. Let me know if this has increased the confusion ........

 

[tvkrys]

hsk -
Sorry I didn't get back to you until now... for some reason my "View Your Posts" list has stopped flagging that there has been a response and although I have my preferences set-up to be emailed when there is a response, except for the very first response I got when after I signed up in January, I haven't received any email notifications!

Anyway, I am still confused but don't have time to write comments/questions to get straightened out right now! Keep your eyes open for me starting this one up again! Thanks for your help! Krys

 

[Jon Peltier]

Sub all_macro()
    x = MsgBox("Enter Choise", vbYesNoCancel)
    Select Case x
        Case 6
            ' if responce is  "Yes"
            call macro1
        Case 7
            ' if responce is  "No"
            call macro2 
        Case 2
            'if responce is  "Cacel"
            call macro3 ( or what ever action u want)
   End Select
End Sub

Don't make it so hard to read. Why memorize the values 6, 7, and 2, and why make readers, particularly a newcomer like tvkrys or a forgetful old-timer like me remember them? There are perfectly good named constants vbYes, vbNo, and vbCancel which were invented for just this purpose. The use of these constants also makes the code self-documenting. So does more descriptive variable naming, like 'Answer' instead of 'x'. The keyword 'Call' is unnecessary as well, especially if the procedures being called are named in a descriptive fashion.

Sub all_macro()
    Dim Answer As Long

    Answer = MsgBox("Enter Choice", vbYesNoCancel)
    Select Case Answer
        Case vbYes
            ' response is  "Yes"
            macro1
        Case vbNo
            ' response is  "No"
            macro2 
        Case vbCancel
            ' response is  "Cancel"
            macro3
   End Select
End Sub

 

[tvkrys]

First, let me say thanks to you both for your assistance on this. I’m always amazed how patient and giving most of the members of this forum are. When you take the time to explain things, rather than just giving an answer, we less experienced members can hopefully learn and contribute in the future. Even when the approaches are different, we all learn!

Now, let me restate my scenario:

Scenario 1
1. do stuff (may be a call macro 4 or just lines of code)
2. Reach decision point. Use message box to determine what to do.
a. If yes, run macro 1
b. If no, run macro 2
c. If cancel, exit macro completely
3. If not canceled, then run macro 3 (in other words, after you run either macro 1 or macro 2, run macro 3)

I can’t quite see through what you’re both saying to crack the code at “Cancel”


Sub main_macro()

(Do various steps then, when get to decision point...)
Answer = MsgBox("Enter Choice", vbYesNoCancel)
Select Case Answer
Case vbYes
' response is "Yes"
macro1
Case vbNo
' response is "No"
macro2
Case vbCancel
' response is "Cancel"
(What do I put here? Seems like hsk’s suggestion would still run macro 3 below.)
End Select
Call macro 3
End Sub

Now, on to scenerio 2. (“Yes” does not run Macro 1 but does run macro 3.)

Scenario 2
1. do stuff
2. Decision point, use message box.
a. If yes, don’t do anything at this point, but continue on to 3.
b. If no, run macro 2
c. If cancel, exit macro completely
3. If not canceled, then run macro 3

What does the code at 2a. look like?

Other questions:
One of hsk’s examples suggests that macros 1, 2 and 3 have to be listed below the main macro. Does that mean they have to be in the same module or can they be separate ones from any modules in the general pool of macros?

Can I have another msgbox after macro 3 or can I only out in one per main macro?

Do the buttons have to be YesNo, YesNoCancel, OKCancel, OkOnly, or can I put any text on them? If restricted to canned buttons, where would I find a list of what’s available? If not restricted, how would I make/define them? Is there a character number limitation?

Thanks again!
Krys

 

[Jon Peltier]

The code is easier to read in MrExcel if you put it into code brackets.

To do what you want with the first macro, including whether it is scenario 1 or 2, add another decision, perhaps within the first case:

Sub main_macro() 
    Dim Answer As Long
    Dim Answer2 As Long

    'Do various steps then, when get to decision point...
    Answer = MsgBox("Enter Choice", vbYesNoCancel) 
    Select Case Answer 
        Case vbYes 
            ' response is "Yes" 
            Answer2 = MsgBox("Run macro1?", vbYesNoCancel)
                Case vbYes
                    macro1
                Case vbNo
                    ' do nothing
                Case vbCancel
                    Goto ExitSub
        Case vbNo 
            ' response is "No" 
            macro2 
        Case vbCancel 
            ' response is "Cancel" 
            Goto ExitSub
    End Select 
    macro 3

ExitSub: 
    ' put stuff here that you might need to do at the end no matter what was run
    ' reset environment that you may have changed, for example:
    ' Application.ScreenUpdating = True
End Sub

Can I have another msgbox after macro 3 or can I only out in one per main macro?

As many as you want, so long as you can keep track of them and they follow your logic.

Do the buttons have to be YesNo, YesNoCancel, OKCancel, OkOnly, or can I put any text on them? If restricted to canned buttons, where would I find a list of what’s available?

You have various choices. To find them you need to explore the VB Editor's tools. In the VBE, go to View menu > Object Browser. This brings up an encyclopedia of all the classes, objects, and methods available to you. Type "msgbox" in the search box (left of the binoculars) and press the search button (the binoculars). The first class in the search results list, vbMsgBoxResult, shows all the possible results. The second, vbMsgBoxStyle shows all the options for how the MsgBox is displayed. Many of these can be combined, for example, using vbYesNoCancel + vbExclamation gives you the three buttons and the exclamation icon. The built-in options include OK or OK-Cancel, Yes-No or Yes-No-Cancel, Retry-Cancel or Abort-Retry-Cancel. If you are clever, these can satisfay 95% of your needs.

There are complicated means for changing the text on these buttons, and I don't use these. Alternatively you could design your own userform with whatever buttons you want, but you then have to program everything about the buttons; unlike the MsgBox, a userform doesn't automatically tell you what the user clicked. You should google on vba userform and look for a tutorial.

 

[hsk]

Its all about the logic. There can be n number of ways to code it. U shud go for best suited as per your requirement,
One way is already give by Jon....
Another may be

Sub main_macro() 
    Dim Answer As Long 
    Dim Answer2 As Long 

    'Do various steps then, when get to decision point...
    Scen     =  MsgBox("Is this scenario 1", vbYesNoCancel) 
    'Yes = scenario 1 / No = scenario 2 / Cancel = neither
    Answer = MsgBox("Enter Choice", vbYesNoCancel) 
    Select Case True And True 
        Case Scen = vbYes And Answer = VbYes 
                call macro1
                call macro3
        Case Scen = vbYes And Answer = VbNo 
                call macro2
                call macro3
        Case Scen = vbNo And Answer = VbYes
                call macro3
        Case Scen = vbNo And Answer = VbNo 
                call macro2
                call macro3  
    End Select
End Sub

If you do not want to take any action on Cancel, just don't code it and u will come out of select with no action taken.

Does that mean they have to be in the same module or can they be separate ones from any modules in the general pool of macros?

I don't think 'same module' is required, though i am not sure in what order the called macro will be searched in the project.

 

[Jon Peltier]

hsk's approach would require more rework if you change from macro3, say, to macro4: you have to change the name of the new macro in three places, tripling the chances of programmer error.

Does that mean they have to be in the same module or can they be separate ones from any modules in the general pool of macros?

Missed this one. The other macros need not be in the same module nor in any particular order in any modules. They can even be in a different workbook, but then you have to get involved with setting references to the other workbook.