Error Message Vba 400
Contents |
Tech Support Guy, we highly recommend that you visit our Guide for New Members. Solved: VBA error "400" running an Excel macro. Discussion in 'Business Applications' started by exerguy, Dec 2, 2008. Thread Status: Not open for further replies. Advertisement exerguy Thread Starter Joined: excel vba 400 error Nov 10, 2008 Messages: 21 I have created a macro that goes through my workbook and is display error message vba supposed to clear the interior color from cells A1:I900 on each page. When I try to run the macro I get a pop up that vba error message object required just says "400" in it with a red circle that has a white "X" through it. Here is the code: Sub Clearcolors() Dim ws As Worksheet Dim RngH As Range Dim RngHD As Range For Each ws In ThisWorkbook.Worksheets ws.Select Set RngH
Vba Error Message If File Does Not Exist
= ws.Range("A1:I" & Range("I900").End(xlUp).Row) For Each RngHD In RngH RngHD.Interior.ColorIndex = xlNone Next RngHD Next ws End Sub What can I do to fix this problem? Any suggestions? exerguy, Dec 2, 2008 #1 Sponsor OBP Trusted Advisor Joined: Mar 8, 2005 Messages: 19,074 The first thing you need to do is to add an Error trap to find out what the Error description is, (if it can tell you that is) Add this as the first row of code after the "Dim RngHD vba error message dialog box As Range" On Error GoTo Errorcatch and at the end of the code put exit sub Errorcatch: MsgBox Err.Description OBP, Dec 2, 2008 #2 exerguy Thread Starter Joined: Nov 10, 2008 Messages: 21 Okay. I added the error catch and the error I get is: "Method 'Select' of object '_worksheet' failed." I'm not too sure where to go from here. Any suggestions? exerguy, Dec 2, 2008 #3 bomb #21 Joined: Jul 1, 2005 Messages: 8,284 Suggestion 1: check for hidden sheets; you'll have a job trying to select those. Suggestion 2: explain what the purpose of the code is, since it doesn't seem to work even with ws.Select suppressed. bomb #21, Dec 3, 2008 #4 exerguy Thread Starter Joined: Nov 10, 2008 Messages: 21 I do have one hidden sheet. Unhiding it allows the macro to work, but is there a way to do this without unhiding it? exerguy, Dec 3, 2008 #5 bomb #21 Joined: Jul 1, 2005 Messages: 8,284 Not sure why you're specifying row 900. However, can't you just simplify it? Sub Clearcolors() For Each Sheet In ThisWorkbook.Worksheets x = Sheet.Range("I" & Rows.Count).End(xlUp).Row Sheet.Range("A1:I" & x).Interior.ColorIndex = xlNone Next Sheet End Sub bomb #21, Dec 3, 2008 #6 exerguy Thread Starter Joined: Nov 10, 2008 Messages: 21 I was specifying row 900 as that was the last row with data in it. I guess that doesn't need to be done. Simplifying it seems to work faster. Thanks. I'll go with this and forget about hiding
Forum Microsoft Office Application Help - Excel Help forum Excel Programming / VBA / Macros What does "400" mean? To get replies by our experts at nominal charges, follow this link to buy points and post your thread in our handling errors in vba Commercial Services forum! Here is the FAQ for this forum. + Reply to Thread
Vba Clear Error
Page 1 of 2 1 2 Last Jump to page: Results 1 to 15 of 16 What does "400" mean? Thread Tools
Excel Error 400 When Running Macro
Show Printable Version Subscribe to this Thread… Rate This Thread Current Rating Excellent Good Average Bad Terrible Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode 09-12-2006,06:54 AM #1 https://forums.techguy.org/threads/solved-vba-error-400-running-an-excel-macro.775340/ nsv View Profile View Forum Posts Forum Contributor Join Date 08-22-2005 Location Denmark MS-Off Ver Excel 2013 Posts 333 What does "400" mean? I made myself a macro by combining a series of recordings made when I performed the needed task step by step. One of the steps was to go to another sheet, copy a column of formulas and return to paste it into the original sheet. When I ran this step http://www.excelforum.com/showthread.php?t=575702 of the macro directly from the module, where it is generated at the recording, there was no problems, but adding it to the rest of the sequence made the macro stop when the cursor was at the 'foreign' sheet and the area to copy was marked. A message informed me of the figure 400 and gave me an OK botton. There was no error message. As far as I know the answer to everything is 42 rather than 400, so what does this figure mean? NSV Register To Reply 09-12-2006,08:05 AM #2 VBA Noob View Profile View Forum Posts Forum Contributor Join Date 04-25-2006 Location London, England MS-Off Ver xl03 & xl 07(Jan 09) Posts 11,988 Hi, I'm assuming it was error message 400. Attached is a list of errors That error referes to "Form already displayed; can't show modally (version 97)" http://support.microsoft.com/?kbid=146864 VBA Noob Register To Reply 09-13-2006,07:12 AM #3 nsv View Profile View Forum Posts Forum Contributor Join Date 08-22-2005 Location Denmark MS-Off Ver Excel 2013 Posts 333 Thanks for the link; seems to be very useful. However, I'm quite positive that it did not write "Error 400"; only "400" and an OK button. Register To Reply 09-27-2006,12:18 PM #4 starryknight64 View Profile View Forum Posts Visit Homepage Forum Contributor Join Date 09-27-2006 Locat
Forums Excel Questions Visual Basic error: 400 Results 1 to 2 of 2 Visual Basic error: 400This is a discussion on Visual Basic error: 400 within the Excel Questions forums, part of the Question Forums category; Hi I have a macro which http://www.mrexcel.com/forum/excel-questions/481544-visual-basic-error-400-a.html had been working fine, which had the following code: With Range("Contact") .Locked = False ... LinkBack LinkBack URL About LinkBacks Bookmark & Share Digg this Thread!Add Thread to del.icio.usBookmark in TechnoratiTweet this thread Thread Tools Show Printable Version Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Jul 15th, 2010,05:32 PM #1 peejay Board Regular Join Date Jul 2003 Location Sydney Posts 53 Visual Basic error: 400 Hi I have a macro which had been error message working fine, which had the following code: With Range("Contact") .Locked = False .Copy .PasteSpecial Paste:=xlPasteValues .Interior.ColorIndex = 36 End With where the range "Contact" is on the active sheet, and the active sheet is unprotected. For some reason, the macro started throwing up an error box, titled 'Microsoft Visual Basic', showing only '400' (with OK & Help buttons). I've fixed the problem, changing the 1st two lines to the following: Range("Contact").Select With Selection but, I'd be interested in vba error message understanding why this has occurred. Thanks, PJ Share Share this post on Digg Del.icio.us Technorati Twitter cheers, PJ Reply With Quote Jul 16th, 2010,02:11 PM #2 xenou MrExcel MVPModerator Join Date Mar 2007 Location Clev. OH, USA Posts 14,851 Re: Visual Basic error: 400 400 errors are, by definition, unspecified and (for all intents and purposes) mysterious. Your code should not fail here, as far as I can tell. You may even find that changing it back, it will now work again. The only way I know of to troubleshoot 400 errors is to step through the code line by line - and watch for the moment when it crashes. From on outside perspective, the fact that you are unlocking a cell in order to do something with it suggests that there's an issue with worksheet protection. You say the sheet is unprotected - but if so, why must the cell be unlocked? I suspect there are other factors at play - though I can't say what or how, or why your code fix has made a difference. Another try, without selection, might be: Code: Dim r As Range Set r = Range("Contacts") With r '// code End With I'm sure this is unsatisfactory as a response. If you're still interested in why, then give more details - what's this about with unlocking the cell, what's in the range and what's the