Solved

Exit Macro in Excel 2010

Posted on 2014-04-09
10
378 Views
Last Modified: 2014-04-12
I want to terminate a macro if the following condition is true:

If ActiveSheet.Name = "Sheet6" Then
terminate this macro

Any ideas?
0
Comment
Question by:cowboywm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 81

Expert Comment

by:byundt
ID: 39990442
If ActiveSheet.Name = "Sheet6" Then Exit Sub
0
 

Author Comment

by:cowboywm
ID: 39990505
This works good but before I want it to exit I want it to bring up my dialog box, CapacityWarningForm, that tells the user how to overcome the limit I set on the number of sheets for this application.  See below:

    ElseIf ActiveSheet.Name = "Sheet6" Then
        Sheets("Sheet6").Select
        ActiveWindow.SelectedSheets.Delete
        Unload ProjectNameForm
        Load CapacityWarningForm
        Exit Sub
  End If
0
 
LVL 81

Expert Comment

by:byundt
ID: 39990532
I suggest simplifying your code like this:
   ElseIf ActiveSheet.Name = "Sheet6" Then
        ActiveSheet.Delete
        Unload ProjectNameForm
        Load CapacityWarningForm
        Exit Sub
  End If 

Open in new window

0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

Author Comment

by:cowboywm
ID: 39990702
Thanks but this did not improve the situation.  My instructional dialog box, CapacityWarningForm, will not display.    Maybe I need to approach it from that angle.

How would I write the code to transfer control to a new sub macro, run the sub macro instructions and not to return to the original macro?
0
 
LVL 81

Expert Comment

by:byundt
ID: 39990712
I don't believe calling another sub in this situation will help. You might instead try hiding ProjectNameForm rather than unloading it.
   ElseIf ActiveSheet.Name = "Sheet6" Then
        ActiveSheet.Delete
       ProjectNameForm.Hide
        Load CapacityWarningForm
        Unload ProjectNameForm
        Exit Sub
  End If  

Open in new window


If hiding the userform doesn't fix the problem, could you please post a sample workbook that illustrates the problem. I'd like to trace the code.
0
 

Author Comment

by:cowboywm
ID: 39993046
Sorry, it didn't solve the problem.  Attached is the file.
Test.xlsm
0
 
LVL 81

Expert Comment

by:byundt
ID: 39993340
I changed the code snippet to hide ProjectNameForm and Show CapacityWarningForm. I should have caught that error previously, but it was obvious when I stepped through the code. After the tweak, the CapacityWarningForm now displays.
        'ActiveSheet                    'Brad changed this statement
        ProjectNameForm.Hide            'Brad deleted this statement
        CapacityWarningForm.Show        'Brad changed this statement
        Exit Sub

Open in new window


FWIW, you can eliminate all of the .Select statements in the sub. They aren't required, and will slow the macro down. Next, you can combine the Sheet1, Sheet2, Sheet3, Sheet4 and Sheet5 sections into a single one if you use Select Case instead of an If block.
Private Sub EnterNextButton_Click()
'This section adds the new project ID to the cell's name
Dim ws As Worksheet
    Set ws = Sheets.Add
    Select Case ws.Name
    Case "Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5"
        Sheets("NewEntryTemplate").Cells.Copy
        Range("A1").PasteSpecial
        ActiveWorkbook.Names.Add Name:="_" & SBINumberTextBox.Text & "ProjectName", RefersToR1C1:="=" & ws.Name & "!R3C1"
        ActiveWorkbook.Names.Add Name:="_" & SBINumberTextBox.Text & "Jan", RefersToR1C1:="=" & ws.Name & "!R21C7"
        ActiveWorkbook.Names.Add Name:="_" & SBINumberTextBox.Text & "Feb", RefersToR1C1:="=" & ws.Name & "!R21C12"
        ActiveWorkbook.Names.Add Name:="_" & SBINumberTextBox.Text & "Mar", RefersToR1C1:="=" & ws.Name & "!R21C18"
        ActiveWorkbook.Names.Add Name:="_" & SBINumberTextBox.Text & "Apr", RefersToR1C1:="=" & ws.Name & "!R21C23"
        ActiveWorkbook.Names.Add Name:="_" & SBINumberTextBox.Text & "May", RefersToR1C1:="=" & ws.Name & "!R21C28"
        ActiveWorkbook.Names.Add Name:="_" & SBINumberTextBox.Text & "Jun", RefersToR1C1:="=" & ws.Name & "!R21C34"
        ActiveWorkbook.Names.Add Name:="_" & SBINumberTextBox.Text & "Jul", RefersToR1C1:="=" & ws.Name & "!R21C39"
        ActiveWorkbook.Names.Add Name:="_" & SBINumberTextBox.Text & "Aug", RefersToR1C1:="=" & ws.Name & "!R21C44"
        ActiveWorkbook.Names.Add Name:="_" & SBINumberTextBox.Text & "Sep", RefersToR1C1:="=" & ws.Name & "!R21C50"
        ActiveWorkbook.Names.Add Name:="_" & SBINumberTextBox.Text & "Oct", RefersToR1C1:="=" & ws.Name & "!R21C55"
        ActiveWorkbook.Names.Add Name:="_" & SBINumberTextBox.Text & "Nov", RefersToR1C1:="=" & ws.Name & "!R21C60"
        ActiveWorkbook.Names.Add Name:="_" & SBINumberTextBox.Text & "Dec", RefersToR1C1:="=" & ws.Name & "!R21C66"
    Case "Sheet6"
        'ActiveSheet
        Unload ProjectNameForm
        CapacityWarningForm.Show
        Exit Sub
    End Select
'This section adds the new project name to the new project worksheet
        Application.Goto Reference:="_" & SBINumberTextBox.Text & "ProjectName"
        ActiveCell.FormulaR1C1 = ProjectNameTextBox.Text
'This section freezes the panes in the new project worksheet
        Range("C3").Select
        ActiveWindow.FreezePanes = True
'This section renames the new project name worksheet
    Select Case ws.Name
    Case "Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5"
        ws.Name = "SBI " & SBINumberTextBox.Text
    End Select
'This section adds the new project Name and updates the cell's formulas in the Summary worksheet
        Sheets("Summary").Select
        Application.Goto Reference:="NextProjectSummaryPage"
        ActiveCell.Offset(-1, 0).Rows("1:1").EntireRow.Select
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        ActiveCell.Range("A1:M1").Select
        ActiveCell.FormulaR1C1 = "=_" & SBINumberTextBox.Text & "ProjectName"
        ActiveCell.Offset(0, 1).FormulaR1C1 = "=_" & SBINumberTextBox.Text & "Jan"
        ActiveCell.Offset(0, 2).FormulaR1C1 = "=_" & SBINumberTextBox.Text & "Feb"
        ActiveCell.Offset(0, 3).FormulaR1C1 = "=_" & SBINumberTextBox.Text & "Mar"
        ActiveCell.Offset(0, 4).FormulaR1C1 = "=_" & SBINumberTextBox.Text & "Apr"
        ActiveCell.Offset(0, 5).FormulaR1C1 = "=_" & SBINumberTextBox.Text & "May"
        ActiveCell.Offset(0, 6).FormulaR1C1 = "=_" & SBINumberTextBox.Text & "Jun"
        ActiveCell.Offset(0, 7).FormulaR1C1 = "=_" & SBINumberTextBox.Text & "Jul"
        ActiveCell.Offset(0, 8).FormulaR1C1 = "=_" & SBINumberTextBox.Text & "Aug"
        ActiveCell.Offset(0, 9).FormulaR1C1 = "=_" & SBINumberTextBox.Text & "Sep"
        ActiveCell.Offset(0, 10).FormulaR1C1 = "=_" & SBINumberTextBox.Text & "Oct"
        ActiveCell.Offset(0, 11).FormulaR1C1 = "=_" & SBINumberTextBox.Text & "Nov"
        ActiveCell.Offset(0, 12).FormulaR1C1 = "=_" & SBINumberTextBox.Text & "Dec"
'This section closes the dialog box
    Unload ProjectNameForm
    Add_Next_Row
End Sub

Open in new window

TestQ-28409180x.xlsm
0
 

Author Comment

by:cowboywm
ID: 39996686
Yes, this works beautifully.  It performs just as I would like.  I have also been able to learn how to compact code using Case statements.  Now the final component is when I delete Sheet6 (ActiveWindow.SelectedSheets.Delete), is there any way to have the macro select the Delete button in the warning message without intervention from the user?  Message: Data may exist in sheet(s) selected for deletion. To permanently delete the data, press Delete.
0
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39996737
Instead of trying to press the button, why not suppress the warning message?
Sub InobtrusiveSheetDeleter()
Application.DisplayAlerts = False
ActiveSheet.Delete
End Sub

Open in new window

You can compact your code some more by using loops. Instead of this:
        ActiveCell.Offset(0, 1).FormulaR1C1 = "=_" & SBINumberTextBox.Text & "Jan"
        ActiveCell.Offset(0, 2).FormulaR1C1 = "=_" & SBINumberTextBox.Text & "Feb"
        ActiveCell.Offset(0, 3).FormulaR1C1 = "=_" & SBINumberTextBox.Text & "Mar"
        ActiveCell.Offset(0, 4).FormulaR1C1 = "=_" & SBINumberTextBox.Text & "Apr"
        ActiveCell.Offset(0, 5).FormulaR1C1 = "=_" & SBINumberTextBox.Text & "May"
        ActiveCell.Offset(0, 6).FormulaR1C1 = "=_" & SBINumberTextBox.Text & "Jun"
        ActiveCell.Offset(0, 7).FormulaR1C1 = "=_" & SBINumberTextBox.Text & "Jul"
        ActiveCell.Offset(0, 8).FormulaR1C1 = "=_" & SBINumberTextBox.Text & "Aug"
        ActiveCell.Offset(0, 9).FormulaR1C1 = "=_" & SBINumberTextBox.Text & "Sep"
        ActiveCell.Offset(0, 10).FormulaR1C1 = "=_" & SBINumberTextBox.Text & "Oct"
        ActiveCell.Offset(0, 11).FormulaR1C1 = "=_" & SBINumberTextBox.Text & "Nov"
        ActiveCell.Offset(0, 12).FormulaR1C1 = "=_" & SBINumberTextBox.Text & "Dec"

Open in new window

Try it like this:
Dim i As Long
For i = 1 To 12
     ActiveCell.Offset(0, i).Formula = "=_" & SBINumberTextBox.Text & Format(DateSerial(0, i, 1), "mmm")
Next

Open in new window

0
 

Author Closing Comment

by:cowboywm
ID: 39996780
All solutions worked well and contributed to the overall success of this macro application.  Thank you for all suggestions!
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Many of the most common information processing tasks require sorting data sets.  For example, you may want to find the largest or smallest value in a collection.  Or you may want to order the data set in numeric or alphabetical order. …
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question