Solved

Exit Macro in Excel 2010

Posted on 2014-04-09
10
375 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
  • 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
algorithm 15 110
SonicWALL Net Extender stops being able to establish VPN connections 6 224
SQL 2016 licensing 6 49
Where did System.Data.Objects go? 2 27
Dependencies in Software Design In software development, the idea of dependencies (http://en.wikipedia.org/wiki/Coupling_%28computer_programming%29) is an issue of some importance. This article seeks to explain what dependencies are and where they …
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
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…

777 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