Calling modules

**guerillaexcel** · 10-29-2010, 04:08 AM

Right, I'm the first to admit I'm an idiot when it comes to VBA so please bear with me (plus its friday and my synapses aren't firing like they should).

I've inherited a spreadsheet that does various things but basically it contains:
module1 - error checking script
module2 - prints the page

I have a button on my spreadsheet that I would like to push and call module, if there are no errors then call module2.

Anyone able to help?

below is my printing script from module2.

    Sheets("Sheet2").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Sheets("SHEET1").Select

Thank you

**royUK** · 10-29-2010, 04:40 AM

All you need is to add either a Forms Button, in which case you right click it & assign the macro.

If you use a Controls(ActiveX) Button then simply use

Option Explicit

Private Sub CommandButton1_Click()
'change this to the name of your macro
MyMacro
End Sub

Adding the buttons depends on your version of excel

**guerillaexcel** · 10-29-2010, 05:57 AM

Thanks roy.

So now I have a 2nd error checking module, module3.

When I push the button, module 1 (1st error checking), if it passes this then I want to run module3 (2nd error checking) and finally if it passes that then run module 2 (print).
If either 1st or 2nd error checking module criteria don't pass, I'd like macro to stop and not print.
Any ideas?

**romperstomper** · 10-29-2010, 06:03 AM

Impossible to give specifics without seeing your code. If the error checking routines throw an error when they fail, then you can trap that in the calling routine with an error handler, or you can convert the called subs to functions that return True or False depending on whether the error check succeeds or fails. The calling sub can then test this return value before proceeding or not.

**royUK** · 10-29-2010, 06:04 AM

I think it would be better to see the code, can you attach a workbook

**guerillaexcel** · 10-29-2010, 06:24 AM

I can't attach a workbook as it contains client info, but I can attach the code that is calling the modules if that helps?

    Sub Print()
    '  1st error checking module
    ErrorCheck1

    ' 2nd error checking module
    ErrorCheck2

    ' I would like the macro to stop if either the above criteria aren't met
    Print1
    
    End Sub

is it possible to have the macro stop before Print1 is called depending on the outcome of ErrorCheck1 and ErrorCheck2?

**royUK** · 10-29-2010, 06:29 AM

We need to see all the code

**guerillaexcel** · 10-29-2010, 07:05 AM

Im afraid the spreadsheet is too complex for me to extract the relevant data and as it contains sensitive data I'm sure you'll understand that I can't post it on a forum.

Originally Posted by romperstomper

Impossible to give specifics without seeing your code. If the error checking routines throw an error when they fail, then you can trap that in the calling routine with an error handler, or you can convert the called subs to functions that return True or False depending on whether the error check succeeds or fails. The calling sub can then test this return value before proceeding or not.

How exactly would I do this? Here is the error checking part of the code for Module1.

    Range("A101").Select
    Warnings = ActiveCell.Value
    If Warnings <> "" Then
    MyNote = MyNote & vbNewLine & Warnings
    End If
            
    Answer = MsgBox(MyNote, vbYesNo, "There are errors")
   
        
    If Answer = vbNo Then
        MsgBox "Please amend "
        
        
   Else

Does this help?

**romperstomper** · 10-29-2010, 07:14 AM

Not really - we need to see the entire routine to provide actual code.

In rough terms though, your sub needs to be changed to a function that returns a Boolean and then you need to set that to False if the validation fails. Your calling code then checks that like so:

If ErrorCheck1 = False then
   msgbox "First error check failed"
   Exit sub
Else
   If ErrorCheck2 = False then
      msgbox "Second error check failed"
      Exit sub
   Else
      Print1
   End If
End If