macro keeps running after exit sub?

O

orsm6

Active Member
Joined
Oct 3, 2012
Messages
496
Office Version
  1. 365
Platform
  1. Windows
Hi all - From a main macro i call another (in same module). the second macro checks ranges and if conditions not met, it is supposed to give message and exit sub.... but for some reason it gives the message and continues running the sub.... any ideas?

here are codes.
main macro
Code: 
Sub register()

    'perform pre check before registering check in
    Call precheck

check conditions
Code: 
Sub precheck()

    'perform check to ensure all answers were given before submitting to register
        If Range("C3") = "" Or _
           Range("l3") = "" Or _
           Range("n9") = "" Or _
           Range("n11") = "" Or _
           Range("n14") = "" Or _
           Range("n16") = "" Or _
           Range("n27") = "" Or _
           Range("n30") = "" Then

    'give message that there are missing details
      MsgBox ("MISSING INFORMATION " & vbNewLine & _
      vbNewLine & "Minimum details required are: " & vbNewLine & vbNewLine & _
                "1. Name," & vbNewLine & _
                "2. Temperature," & vbNewLine & _
                "3. All questions answered") & vbNewLine & vbNewLine
        Exit Sub
        End If

End Sub
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
What's after the Call precheck line in the register sub?
 
Upvote 0
JoeMo said:
What's after the Call precheck line in the register sub?
Click to expand...
Code: 
'perform ALERT check before registering check in
    Call alertcheck
    
    Application.ScreenUpdating = False
    
    'opens sign in register ready to copy new sign in data
    Var = Worksheets("Config").Cells(4, 4).Value
    Set tempbook = Workbooks.Open(Var)
    
    'activate check in sheet
    ThisWorkbook.Worksheets("CheckIn").Activate

        'Start----------------Searching for Empty Row in Reg ----------
            For h = 5 To 50000
                If tempbook.Worksheets("Reg").Cells(h, 2).Value = "" Then
                    x = h
                Exit For
                Else
                    x = 50000
                End If
            Next
    
        If x = 50000 Then
                MsgBox ("Sorry,Maximum 50000 Check In's can be entered, please archive register and start a new register")
                Exit Sub
                Else
        End If

    'add date of entry to register
        With tempbook.Worksheets("Reg")
            Dim rw As Integer
        
                rw = .Range("A65536").End(xlUp).Row + 1
                .Range("A" & rw).Value = Date
        
        End With
    
    'copy response data to register - name
    ThisWorkbook.Worksheets("CheckIn").Cells(3, 3).Copy
        tempbook.Worksheets("Reg").Cells(x, 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    'copy response data to register - company
    ThisWorkbook.Worksheets("CheckIn").Cells(5, 3).Copy
        tempbook.Worksheets("Reg").Cells(x, 3).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    'copy response data to register - temp
    ThisWorkbook.Worksheets("CheckIn").Cells(3, 12).Copy
        tempbook.Worksheets("Reg").Cells(x, 4).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    'copy response data to register - Q1
    ThisWorkbook.Worksheets("CheckIn").Cells(9, 15).Copy
        tempbook.Worksheets("Reg").Cells(x, 5).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    'copy response data to register - Q2
    ThisWorkbook.Worksheets("CheckIn").Cells(11, 15).Copy
        tempbook.Worksheets("Reg").Cells(x, 6).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    'copy response data to register - Q3
    ThisWorkbook.Worksheets("CheckIn").Cells(14, 15).Copy
        tempbook.Worksheets("Reg").Cells(x, 7).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    'copy response data to register - Q4
    ThisWorkbook.Worksheets("CheckIn").Cells(16, 15).Copy
        tempbook.Worksheets("Reg").Cells(x, 8).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    'copy response data to register - Q5
    ThisWorkbook.Worksheets("CheckIn").Cells(27, 15).Copy
        tempbook.Worksheets("Reg").Cells(x, 9).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        
    'copy response data to register - Acknowledgement
    ThisWorkbook.Worksheets("CheckIn").Cells(30, 15).Copy
        tempbook.Worksheets("Reg").Cells(x, 10).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
      
    'close register
    tempbook.Activate
    ActiveWorkbook.Close True

    Range("c3").Select
End Sub
 
Upvote 0
Use a boolean variable (YesNo) to tell sub register keep going or not.
VBA Code: 
'public variable declare
Dim YesNo As Boolean
'------------------------------------
Sub register()
    'perform pre check before registering check in
    Call precheck
    ' test whether sub precheck is fulfilled
    If YesNo Then
         'Main code here
    End If
End sub
'------------------------------------
Sub precheck()
 'perform check to ensure all answers were given before submitting to register
        If Range("C3") = "" Or _
           Range("l3") = "" Or _
           Range("n9") = "" Or _
           Range("n11") = "" Or _
           Range("n14") = "" Or _
           Range("n16") = "" Or _
           Range("n27") = "" Or _
           Range("n30") = "" Then

    'give message that there are missing details
      MsgBox ("MISSING INFORMATION " & vbNewLine & _
      vbNewLine & "Minimum details required are: " & vbNewLine & vbNewLine & _
                "1. Name," & vbNewLine & _
                "2. Temperature," & vbNewLine & _
                "3. All questions answered") & vbNewLine & vbNewLine
    ' assign "false" to stop sub register
    YesNo = False
    Else
    YesNo = True
    Exit Sub
End If
End Sub
 
Upvote 0
bebo021999 said:
Use a boolean variable (YesNo) to tell sub register keep going or not.
VBA Code: 
'public variable declare
Dim YesNo As Boolean
'------------------------------------
Sub register()
    'perform pre check before registering check in
    Call precheck
    ' test whether sub precheck is fulfilled
    If YesNo Then
         'Main code here
    End If
End sub
'------------------------------------
Sub precheck()
 'perform check to ensure all answers were given before submitting to register
        If Range("C3") = "" Or _
           Range("l3") = "" Or _
           Range("n9") = "" Or _
           Range("n11") = "" Or _
           Range("n14") = "" Or _
           Range("n16") = "" Or _
           Range("n27") = "" Or _
           Range("n30") = "" Then

    'give message that there are missing details
      MsgBox ("MISSING INFORMATION " & vbNewLine & _
      vbNewLine & "Minimum details required are: " & vbNewLine & vbNewLine & _
                "1. Name," & vbNewLine & _
                "2. Temperature," & vbNewLine & _
                "3. All questions answered") & vbNewLine & vbNewLine
    ' assign "false" to stop sub register
    YesNo = False
    Else
    YesNo = True
    Exit Sub
End If
End Sub
Click to expand...
perfect, works great. thank you bebo
 
Upvote 0
bebo021999 said:
Use a boolean variable (YesNo) to tell sub register keep going or not.
VBA Code: 
'public variable declare
Dim YesNo As Boolean
'------------------------------------
Sub register()
    'perform pre check before registering check in
    Call precheck
    ' test whether sub precheck is fulfilled
    If YesNo Then
         'Main code here
    End If
End sub
'------------------------------------
Sub precheck()
 'perform check to ensure all answers were given before submitting to register
        If Range("C3") = "" Or _
           Range("l3") = "" Or _
           Range("n9") = "" Or _
           Range("n11") = "" Or _
           Range("n14") = "" Or _
           Range("n16") = "" Or _
           Range("n27") = "" Or _
           Range("n30") = "" Then

    'give message that there are missing details
      MsgBox ("MISSING INFORMATION " & vbNewLine & _
      vbNewLine & "Minimum details required are: " & vbNewLine & vbNewLine & _
                "1. Name," & vbNewLine & _
                "2. Temperature," & vbNewLine & _
                "3. All questions answered") & vbNewLine & vbNewLine
    ' assign "false" to stop sub register
    YesNo = False
    Else
    YesNo = True
    Exit Sub
End If
End Sub
Click to expand...
hmmm bugger - seems it might not be working haha. I didn't try to run the macro if the conditions ARE met... with everything correct it won't run the macro.
 
Upvote 0
I was looking at this all the wrong way.... rather than calling macros from the main i have done the following.

launch precheck first.
if condition not met exit sub
else run main code

this does what i need it to now.

thanks all for your help
 
Upvote 0
So only one sub needed, I think.
VBA Code: 
sub register()
 'perform check to ensure all answers were given before submitting to register
        If Range("C3") = "" Or _
           Range("l3") = "" Or _
           Range("n9") = "" Or _
           Range("n11") = "" Or _
           Range("n14") = "" Or _
           Range("n16") = "" Or _
           Range("n27") = "" Or _
           Range("n30") = "" Then

    'give message that there are missing details
      MsgBox ("MISSING INFORMATION " & vbNewLine & _
      vbNewLine & "Minimum details required are: " & vbNewLine & vbNewLine & _
                "1. Name," & vbNewLine & _
                "2. Temperature," & vbNewLine & _
                "3. All questions answered") & vbNewLine & vbNewLine
      exit sub
       Else
    ' Main code paste here
End If
End Sub
 
Upvote 0
Solution
bebo021999 said:
So only one sub needed, I think.
VBA Code: 
sub register()
 'perform check to ensure all answers were given before submitting to register
        If Range("C3") = "" Or _
           Range("l3") = "" Or _
           Range("n9") = "" Or _
           Range("n11") = "" Or _
           Range("n14") = "" Or _
           Range("n16") = "" Or _
           Range("n27") = "" Or _
           Range("n30") = "" Then

    'give message that there are missing details
      MsgBox ("MISSING INFORMATION " & vbNewLine & _
      vbNewLine & "Minimum details required are: " & vbNewLine & vbNewLine & _
                "1. Name," & vbNewLine & _
                "2. Temperature," & vbNewLine & _
                "3. All questions answered") & vbNewLine & vbNewLine
      exit sub
       Else
    ' Main code paste here
End If
End Sub
Click to expand...
yep... discovered this a little while ago lol... thanks for helping though
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
VBA: Am I double-calling a Sub given nested If statements? Or other inefficiencies?
Replies
8
Views
235
dreid1011
dreid1011
TheWaterDog
  • Solved
MsgBox - vbNo does not exit sub
Replies
2
Views
374
TheWaterDog
TheWaterDog
I
  • Question
Copy worksheet but the new worksheet to omit some items
2
Replies
11
Views
418
ipbr21054
I
K
  • Solved
Simple implementation question
Replies
5
Views
414
Katolux
K
R
  • Solved
Continue or Exit Macro based on cell value
Replies
2
Views
502
rachel06
R

Forum statistics

Threads
1,214,599
Messages
6,120,447
Members
448,966
Latest member
DannyC96

Share this page

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