Show Custom Error Message If Opening Workbook Macro Code Fails
May 17, 2008
I cannot figure out how to get my error handler to work, or actually, not work. It seems to work fine when there is an error, but the code still gets read even when there was not an error. Basically, I am trying to open a file, which may or may not be there. When it is not there I want a message to pop up informing the user. However, when the file is there and it opens, the error handler still gives the message box. Any ideas what I am doing wrong?
Private Sub btnOK_Click()
Application. ScreenUpdating = False
Dim LCSfile As String
LCSfile = frmSelectFile.Listbox1.Value
On Error Goto ErrHandler
Workbooks.Open Filename:=sPath & sDate & "" & LCSfile & "QUANT.CSV"
ErrHandler:
MsgBox ("File is not quantitated. Please select another file.")
Application.ScreenUpdating = True
End Sub
View 2 Replies
ADVERTISEMENT
Aug 31, 2006
I have a userform with a combobox (cmbSearch) , a textbox (textbox1) and a listbox (listbox1)....and a commandbutton (cmbFind). This userform is used for searching records in my worksheet....where user selects a "Search By" category from the combobox and then enters relevent text search criteria in the textbox....and all the search results are shown in the listbox. This part works fine untill someone tries to search a "non-existent" record. e.g. a customer name thats not in the database and it gives an error. I would incorporate in my code to have a Message box pop up on these type of searches saying "textbox1.value Not Found!" insetad of error message.
Private Sub cmbFind_Click()
Set c = Range("a65536").End(xlUp).Offset(1, 0)
'write userform entries to database
Dim DataSH As Worksheet
Set DataSH = Sheets("ComplaintData")
With DataSH
.Range("L1").Value = cmbSearch.Value
.Range("L2").Value = TextBox1.Text
.Range("N1"). CurrentRegion.Clear
.Range("A1").CurrentRegion. AdvancedFilter Action:=xlFilterCopy, criteriarange:=.Range("L1:L2"), copytorange:=.Range("N1")
End With
lstSearch.RowSource = vbNullString
lstSearch.RowSource = "outdata"
End Sub
View 5 Replies
View Related
Aug 27, 2007
Below is a macro I recorded to help copy 'Paste Special - values only' data between two spreadsheets that have different colour and conditional formatting in cells.
However, if this is run and there is no data to paste, it throws up the End and Debug error.
Any chance that someone knows that if there is no data sitting on the Clipboard, then a msg box would appear saying "Please select the data you require to be copied" in the message box.
Sub data_input()
ActiveSheet. Unprotect
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B5:AG4804").Select
Selection.sort Key1:=Range("B5"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("B5").Select
ActiveSheet.Protect
End Sub
View 4 Replies
View Related
Mar 11, 2008
I have used the following piece of code to stop error checking in my excel spreadsheet:
Option Explicit
Private Sub Workbook_Open()
' Application.ErrorCheckingOptions.BackgroundChecking = False
End Sub
The problem is - works fine on my computer, but when distributed on the server the folloeing error is returned:
Run-time error '438':
Object doesn 't support this property or method
View 9 Replies
View Related
Aug 13, 2014
I have created a report with 22 sheets as different pages to be included or hidden depending upon the type of report needed. I had found several sources saying to use the Custom Views function. So for example I will unhide all the sheets and add as a Custom View "All Sheets" and then whittle it down to the 5 I might need for B type inspections and add that view as B Inspection. But when I try and toggle between them I'm getting an error message that says "Some view settings could not be applied" - and thew saved view I was looking for is not the same sheetwise.
View 1 Replies
View Related
May 12, 2006
I'm trying to load a custom menu in a workbook to avoid having users to load an add in to use the spreadsheet. I'm trying to add this to the "this workbook" tab but I've never done this.
View 3 Replies
View Related
Oct 4, 2006
I tried testing, msgbox displays the correct last modified file "MMO Activity Report 09-29-06.xls" in path D:MMOWorkfile but I was not able to open the workbook.
Sub testGetNewestFile()
MsgBox NewestFile("D:MMOWorkfile", "*.xls")
Workbooks.Open (NewestFile("D:MMOWorkfile", "*.xls"))
View 9 Replies
View Related
Jul 14, 2009
What is the code to enable macros upon opening a workbook and avoid receiving the message "Security Warning-Macros have been disabled" in Excel 2007?
View 3 Replies
View Related
Oct 17, 2006
I have a range named "start" and I have a form named "fMenu". I have used this same code in other spreadsheets to have the userform automatically become visible when the spreadsheet is opened and it works in other workbooks. Apparently I have been struck with the stupid stick this morning. I have a hotkey of "M" and when I use control M the form shows up and the macros invoked by each button on the form are working properly.
Sub open_menu()
Application.Goto "start"
fMain.Show
End Sub
View 4 Replies
View Related
May 2, 2012
I need a macro where user choose a value from validation, automatically lock the other cells and should show an error message.
Here is what I am looking for. User will choose Yes or no from cell H15 validation. If user choose "NO" from the validation then all other cells has to be locked in the workbook automatically except Cell H15 and a msg box needs to appear stating: "Select Yes from the validation".
View 3 Replies
View Related
May 29, 2008
I have a workbook with macros that run on my laptop, but when I email the workbook to another user and the macro is executed I get a subscript out of range message?
Both Excel versions are the same.
Environments are the same.?
View 4 Replies
View Related
Sep 25, 2007
I have a workbook which I need to give shared access.
The workbook has macros which make sheets visible and VeryHidden and also give certain sheets protection. Also a filter runs on one sheet.
When I give the workbook shared access, the macros error?
how I can give me workbook shared access but still allow the macros to hide/unhide sheets, filter and protect worksheets?
View 4 Replies
View Related
Nov 9, 2012
I Have a tool which will be used online & offline.
I would like a box to appear on the opening of the spreadsheet which asks the user "Are you online?", with options "Yes" & "No", if the user selects "Yes" then a macro (which i have already written) refreshes the spreadsheet.
if the user selects "No", the sheet is not resfreshed.
What is the best way to do this?
View 2 Replies
View Related
Apr 18, 2006
I can't open a file anymore. On opening an exclamation mark message appears telling me the file contains "Too many different cell formats". After clicking "Ok" Excel doesn't continue the opening proces. However I need the info in the file. how to retreive the info in the file?
View 2 Replies
View Related
Jul 6, 2006
I do not understand why my code returns a Run Time Error"9" Subscript out of range. With the following code, I opened a workbook and then wish to close that same workbook without saving. Eventually I will be pulling information from the workbook but for now I just want it to open and then close right away.
Private Sub cb_NewDate_Change()
ListIndex = cb_NewDate.ListIndex
If ListIndex = 0 Then
Application. ScreenUpdating = False
Workbooks.Open Filename:="I:JoeNetwork Conversion FactorsNetworkConversionFactors_Ver_2_0_0.xls"
Application.Workbooks("I:JoeNetwork Conversion FactorsNetworkConversionFactors_Ver_2_0_0.xls").Close False
End Sub
View 3 Replies
View Related
Sep 15, 2006
I am getting a runtime error 32809, Application-defined or Object-defined error when I try to open my workbook. I remember someone posting code to set the VBA References when the Workbook is opened and I think this is what I need, because the file will run on Excel 2000 and not on Excel 2003. I know the References are different, but I don't know how to change them.
View 9 Replies
View Related
Dec 18, 2007
Having a problem with a simple udf to calculate values across multiple sheets using the calling cell as a reference point. It works great until you open another workbook and then switch back. At that point all cells calling this udf return a #value! error. If I type anywhere on the sheet all of the values will recalculate but this is less than ideal for the end users here. below is example of concept
Function TestMe()
Application.Volatile (True)
Dim strCallAddress As String
Dim intSubTotal As Integer
strCallAddress = Application.Caller.Address
intSubTotal = Worksheets(2).Range(strCallAddress).Value
intSubTotal = intSubTotal + Worksheets(3).Range(strCallAddress).Value
TestMe = intSubtotal
End Function
View 4 Replies
View Related
Sep 13, 2006
I have a list of workbooks on a sheet that i am trying to open with vba one by one. If a workbook in my list does not exist or was deleted then excel displays a runtime error. Is there a way to bypass this error and have the code skip the non existant workbook and open the next one?
View 8 Replies
View Related
Dec 28, 2006
I would like to get some information when a vba code is in error.
Can we create a sub that we can refer to when a vba code is in error. Something as:
On Error goto "name of the sub"
In that sub, I would like to write in sheet1 , cell a1 the name of the sub in which the code is in error.
In cell a2, i would like to write the line in error in the VBA code.
finaly i would like to show a msgbox ("Contact your admin")
and then close the file
View 9 Replies
View Related
Sep 26, 2006
whats happening to my workbook. On a worksheet called Data, i'm pulling in a lot og data from a database. Then on another worksheet i'm looking for certain things on the data sheet and counting how many there are.
This all goes along find until i close the workbook, and then re-open.I click the automatically update button that appearsand once it finishs updating all my values are replaced with #Value.
This is the formula i'm using.
= SUMPRODUCT(--(Data!$X$1:$X$49871="4")+(Data!$X$1:$X$49871=4),--(Data!$M$1:$M$49873="FTS-Unix"))
View 6 Replies
View Related
Nov 8, 2012
Why can't I get this code to work? The first line seems to work ok but not yet sure as the rest doen't work.
I have tried NEXT_YEAR as a string and as a number but I keep getting an error.
Sub test()
NEXT_YEAR = "2013"
ChDir "J:Maxtor backupFire DeptVacations " & NEXT_YEAR
Workbooks.Open(Filename:= _
"J:Maxtor backupFire DeptVacations " & NEXT_YEAR "VacGrp - 1 - " & NEXT_YEAR & ".xls""", UpdateLinks _
:=3).RunAutoMacros Which:=xlAutoOpen
End sub
Below is the code I am trying to replace and it works fine.
ChDir "J:Maxtor backupFire DeptVacations 2013"
Workbooks.Open(Filename:= _
"J:Maxtor backupFire DeptVacations 2013VacGrp - 1 - 2013.xls", UpdateLinks _
:=3).RunAutoMacros Which:=xlAutoOpen
View 5 Replies
View Related
Aug 14, 2007
I want to be able to launch code in an add-in to perform a check when other workbooks are opened by the user. Code in the Open event for the add-in launches when Excel opens, not when each workbook is opened
View 2 Replies
View Related
Aug 23, 2012
I've been using a macro successfully for the last couple of years, but this morning when I went to use it, it decided to fail. I have a workbook which contains various spreadsheets. The macro that has failed performs the following tasks:
1. It copies a list of email addresss from an external workbook to a sheet in the current workbook (still works)
2. It copies the referral sheet I want to send to a new file, and saves it with an appropriate filename to an appropriate folder (still works)
3. The macro then creates an email with a standard subject line, attaches the new worksheet and emails it to each of the addresses as above (broken)
The error message is from Microsoft Visual Basic. "Run-time error '1004': Mail system failure. Check your mail installation."
I'm guessing there is a setting somewhere in Excel that has changed as part of an update.
I've been through a number of the options in the developer menu to remove any obvious restrictions (& reopened excel afterwards), but so far it hasn't resolved the problem.
For what it's worth, here is the macro code.
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Run "PullInSheet1"
Dim oldbook As String
[Code] .......
In case you're wondering about the pullinsheet code, I'll add it below - but I probably grabbed it from this forum a couple of years ago (like some of the above) & just made some changes.
Code:
Sub PullInSheet1()
Dim AreaAddress As String
'''''' Sheet11.UsedRange.Clear
Dim ClRange As String
ClRange = "= 'L:ADMINEMPLOY SERVICES" _
[Code] ........
There are a couple of things I've wanted to do to improve the macro, but I couldn't justify the need to spend time working it out (since writing spreadsheets isn't really my job). Since it's broken at the moment, I can...
1. I'd like to create a subject line that reflects the name of the person being referred. For some reason though, anything other than text in the cell reference caused an error for me. eg, I tried using concatenate to create my subject line, but it didn't work.
2. I'd like the copied sheet to contain all of the formatting of the original sheet. Presumably there is a paste option that will do this and I just picked the wrong one.
The mailsystem we use is Groupwise 8. I couldn't find any settings in that program that have been changed, or that I could change.
I should change the extension from xls to xlsx in the code since I'm using Excel 2010 (but changing it doesn't fix the problem).
View 4 Replies
View Related
May 11, 2009
How can i manage that my code in the Workbook_Open() event is run when i open that workbook with some code from an other workbook?
When i manually open that workbook, excel runs all events, including my Workbook_Open() event, but i need to open this one out of an event of an other workbook.
View 13 Replies
View Related
Aug 20, 2008
Recently I found this forum through a Google search along with the perfect solution to a problem I was having finding and replacing text based on a table of replacement values. Here is the thread:
Multiple substitute or replace text using a table
I incorporated the SuperSub function that steveorg developed as a result of that thread into my worksheet by creating a new module and pasting the following code into it:
Function SuperSub(OriginalText As String, rngOldText As Range)
Dim cel As Range
Dim strOldText As String, strNewText As String
' loop through list of old_text used in substitute
For Each cel In rngOldText.Cells
strOldText = cel.Value
strNewText = cel.Offset(0, 1).Value
OriginalText = Application.WorksheetFunction.Substitute(OriginalText, strOldText, strNewText)
Next cel
SuperSub = OriginalText
End Function
It works like a charm as a formula, but I also have a macro in the same workbook that, as soon as it selects the sheet with the formula, calls the SuperSub function and starts executing the code as a macro (even though there is no call to this routine in the macro). The macro ran just fine before incorporating the function into my worksheet.
The formula looks like this: =TRIM((supersub( UPPER(E2),rngSubst)))
If cell E2 contains the string "101 North Main Street, Apartment 5", it would return "101 N MAIN ST APT 5"
Here is the macro:
Sub Import()
'
Sheets("Shoebuy FTP").Select
Range("A2:R200").ClearContents 'This is where it jumps to Function SuperSub(OriginalText As String, rngOldText As Range)
The function runs for every occurrence of the formula in the worksheet, and when it is finished, all of the cells containing that formula show a value of "#VALUE!". I should mention that it does not change the formula at all, but I then have to highlight each cell that contains the formula, press <F2> to edit, then <ENTER> to get it to display the correct results again.
It is probably some stupid little thing that I didn't do when I created the function, but I can't figure it out. I have written numerous macros, but this is the first time I have ever created a custom function.
View 5 Replies
View Related
Mar 3, 2009
Is it possible to have excel 2007 open a workbook, activate the first sheet "output" and activate the first empty cell in column A - ready for user input? i.e.
I want my user to open workbook "EHB Stock" and automatically the first empty cell in column A of worksheet "output" is activated and ready for input.
The rest of my code is:
View 2 Replies
View Related
Nov 21, 2012
I am basically using a combobox in word that opens an excel spreadsheet and pulls information about the people entered in the combobox. The information pulled from excel is then pasted into the original word document.
The problem that I am having is that the macro may be needed to run twice (ex. suppose the user forgot to include an individual in the first run) and on the second run the excel document cannot be opened and it gets stuck on the loop. The macro will only work again if the word document is closed and reopend. I suspect that the excel document is not completly closing on the first runthrough.
I've included my code below. The initialize combobox code is on the bottom. I
PHP Code:
Dim WordApp As Word.ApplicationDim xlApp As Excel.Application
'Dim xlWB As Excel.WorkbookPrivate Sub CommandButton1_Click()Bios
= ActiveWorkbook.NameNewWBookName
= ActiveWindow.CaptionSet WrdDoc
= ActiveDocumentlastrow = Range("A1").End(xlDown).RowS
[Code]....
View 2 Replies
View Related
Oct 26, 2006
I have a user form with a TextBox (TextBox1), the user enters a filename and presses CommandButton1, this then opens the file. I have set an error trap so that if the file does not exist a MsgBox prompts the user to re-enter. So far so good, however if the error trap operates any further files entered are declared as non-existent even if I know they exist. I thought this would be quite easy but once again VBA bites me back!!
here is my code
Sub CommandButton1_Click()
stPath = ThisWorkbook.Path
stFilename = "Retention" & "" & TextBox1 & ".xls"
stFullname = stPath & "" & stFilename 'completes full path of for TextBox1
On Error Goto Errorhandler: 'go to this if file does not exist
Workbooks.Open stFullname
ActiveWorkbook.RunAutoMacros xlAutoOpen
View 6 Replies
View Related
Jan 11, 2009
I have one line of code that I need an error message for:
View 2 Replies
View Related
Aug 23, 2006
I'm attempting to write a macro (in Excel) that uses a path that is given in one of the fields of the worksheet to open a workbook and get find a field to return to the original worksheet. I have working code to pull the information from a worksheet within the same workbook, but I can't seem to get it to work with an external worksheet. Can someone assist me in getting this to work?
I have attached my original code that works, so all I need to do is translate it to open the external workbook and worksheet instead of using the local worksheet. This is pretty simple right?
Public Function GetRate(strClassification As String, strContractNo As String) As Integer
View 9 Replies
View Related
