The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.
One of a set of tools we're offering as a way of saying thank you for being a part of the community.
Course Of The Month
3 days, 10 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
On Error Excel 2007 VBA: Getting it in the right place
Posted on 2012-12-26
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
[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
-
2
2
5 Comments
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
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.
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
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!
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
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.
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.
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
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…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Suggested Courses
Course of the Month3 days, 10 hours left to enroll
691 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.