Solved

Exit Macro in Excel 2010

Posted on 2014-04-09
10
371 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 80

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 80

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
 

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 80

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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

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

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 80

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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

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 …
The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
Viewers will learn the different options available in the Backstage view in Excel 2013.

705 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now