Solved

On Error Excel 2007 VBA: Getting it in the right place

Posted on 2012-12-26
5
153 Views
Last Modified: 2012-12-27
I want the Msgbox to show ONLY if file can't save or doesn't have values in the 3 cells. I never seem to be able to figure out where to put it so it does NOT pop up always.

Sub SaveBook()
'Saves the workbook

If Range("checksave").Value = 3 Then
MsgBox ("Created")
ActiveWorkbook.SaveAs Filename:=Range("path").Text & Range("filename").Text
On Error GoTo Err

::::My code here

Else: MsgBox ("You must enter a MID and Customer ID and Business Name before saving.")
Exit Sub
End If
Err:
MsgBox ("You must enter a MID and Customer ID and Business Name before saving.")
Worksheets("Dashboard").Shapes("Rounded Rectangle 50").Delete

End Sub

Open in new window

0
Comment
Question by:Dreamboat
  • 2
  • 2
5 Comments
 
LVL 10

Accepted Solution

by:
tdlewis earned 400 total points
ID: 38722202
Swap lines 12 and 13 of your code so that you have:
Else: MsgBox ("You must enter a MID and Customer ID and Business Name before saving.")
End If
Exit Sub

Open in new window

0
 
LVL 2

Assisted Solution

by:hanklm
hanklm earned 100 total points
ID: 38722530
The On Error Goto command affects all commands in the Sub that follow it, so if you wanted to detect an error during file save, you would want the On Error statement  before the SaveAs call and any other statements you expect might fail.

If code logic detects an error, jump to error handling code.  In your example this avoids duplicating the MsgBox code, but it is worth noting that the SaveAs command can encounter a wide range of errors, so you might also want to check the value of ErrNo for situations like path-not-found or access-denied, etc.

Sub SaveBook()
'Saves the workbook

On Error GoTo Err
If Range("checksave").Value = 3 Then
MsgBox ("Created")
ActiveWorkbook.SaveAs Filename:=Range("path").Text & Range("filename").Text

::::My code here

Else
goto Err
End If

Exit Sub
Err:
MsgBox ("You must enter a MID and Customer ID and Business Name before saving.")
Worksheets("Dashboard").Shapes("Rounded Rectangle 50").Delete

End Sub

Open in new window

0
 
LVL 22

Author Comment

by:Dreamboat
ID: 38722588
so you might also want to check the value of ErrNo for situations like path-not-found or access-denied, etc.
Nope. They won't get that. If they do, it'll be once and never again.

Thanks, guys! I'll check tomorrow at work. Happy Holidays!
0
 
LVL 22

Author Comment

by:Dreamboat
ID: 38723502
Here's what I'm using, which appears to work. :)

Sub SaveBook()
'Saves the workbook

If Range("checksave").Value = 3 Then
MsgBox ("Created")
ActiveWorkbook.SaveAs Filename:=Range("path").Text & Range("filename").Text
On Error GoTo Err

:::My code here

Else: MsgBox ("You must enter a MID and Customer ID and Business Name before saving.")
Worksheets("Dashboard").Shapes("Rounded Rectangle 50").Delete
End If
Exit Sub

End Sub

Open in new window

0
 
LVL 2

Expert Comment

by:hanklm
ID: 38724867
It looks like you removed the label: Err.  This is not good.  I'm not sure what the behavior will be when an error is encountered, but I doubt it will be what you want.

I suspect if you compiled the VBA project. It would tell you that the label could not be found. I highly recommend using the compile feature (Debug / Compile VBAProject).  It helps discover errors (like this one) you might otherwise miss.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

820 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