Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Exit Macro in Excel 2010

Posted on 2014-04-09
10
Medium Priority
?
392 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
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…

885 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