Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Exit Macro in Excel 2010

Posted on 2014-04-09
10
Medium Priority
?
388 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure 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 2000 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

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

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…
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
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 a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

688 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