+ Reply to Thread
Results 1 to 16 of 16

What does "400" mean?

  1. #1
    Forum Contributor
    Join Date
    08-22-2005
    Location
    Denmark
    MS-Off Ver
    Excel 365
    Posts
    349

    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 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

  2. #2
    Forum Contributor VBA Noob's Avatar
    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

  3. #3
    Forum Contributor
    Join Date
    08-22-2005
    Location
    Denmark
    MS-Off Ver
    Excel 365
    Posts
    349
    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.

  4. #4
    Forum Contributor starryknight64's Avatar
    Join Date
    09-27-2006
    Location
    Missouri
    MS-Off Ver
    2003 (Work) & 2007 (Home)
    Posts
    193

    Post the code?

    Perhaps it would be beneficial to post the code?

  5. #5
    Forum Contributor starryknight64's Avatar
    Join Date
    09-27-2006
    Location
    Missouri
    MS-Off Ver
    2003 (Work) & 2007 (Home)
    Posts
    193
    I just got the same error!

    I mean, exactly! A message box with just the text "400" in it.

    What I was doing was changing worksheets incorrectly. Check your code where you might be switching worksheets, make sure those lines of code are good.

    And I do think this corresponds to VBA Noob's error listing #400. Even though it doesn't say "Error #400: Form already displayed; can't show modally," I think thats what it means.
    starryknight64

  6. #6
    Forum Contributor
    Join Date
    08-22-2005
    Location
    Denmark
    MS-Off Ver
    Excel 365
    Posts
    349
    Well, I found another solution and deleted the code. I might be able to reconstruct it, but until further I will just imagine you are right that it really means "Error 400".

    NSV

  7. #7
    Forum Contributor starryknight64's Avatar
    Join Date
    09-27-2006
    Location
    Missouri
    MS-Off Ver
    2003 (Work) & 2007 (Home)
    Posts
    193

    Screenshot

    Here's a screenshot of the Error 400...only this time its for a different reason than the one I explained above.

    What happened here is I tried executing my code, ran into a confusing error message, then hit play again and ran into the error 400, complete with description...

    Attached Images Attached Images
    Last edited by starryknight64; 10-23-2006 at 02:53 PM.

  8. #8
    Forum Contributor
    Join Date
    08-22-2005
    Location
    Denmark
    MS-Off Ver
    Excel 365
    Posts
    349
    In my case I don't know why it would come up with this "Form already displayed" error, whatever that means. It was only asked to copy something from another sheet...

  9. #9
    Forum Contributor
    Join Date
    08-22-2005
    Location
    Denmark
    MS-Off Ver
    Excel 365
    Posts
    349
    Now I'm stuck again. The problem part of the code goes:

    Sub overf()
    Range("A3").Select
    Selection.Copy
    Sheets("Calc").Select
    Range("J4").Select
    ....
    etc.

    I found out that the macro runs OK from Module1 where it was recorded, but moving it to the sheet where it is supposed to operate from, creates two different errors:
    1) Running it stepwise with F8 gives Run-time error '1004' Application-defined or object-defined error.
    2) Running it with the button Run Macro makes it return "400" without further explanation.

    The stepwise execution reveals that there is no problem in entering sheet "Calc", but the next step Range("J4").Select makes it stop.

    Anyone has a hint, or will I have to run it from the Module (I don't even know what purpose a 'Module' serves)?


    NSV

  10. #10
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474
    Sub overf()
    Range("A3").Select
    Selection.Copy
    Sheets("Calc").Select
    Range("J4").Select
    ....
    etc.
    just as a test what happens when your code goes like this

    Sheets("Sheet1").Range("A3").Copy Destination:=Sheets("Calc").Range("J4")

  11. #11
    Forum Contributor
    Join Date
    08-22-2005
    Location
    Denmark
    MS-Off Ver
    Excel 365
    Posts
    349
    Sorry, but it gives the same result.

    I attach the complete code. The sheet, in which the code should run, is called "Optimering".
    The procedure should take an input from "Optimering", bring it to a calculating sheet called "Calc", take the new result back to "Optimering and finally put the cursor in a new position.

    By the way, all comments to my way of coding are very appreciated. I'm quite selftaught with the macro recorder as the only help.


    Please Login or Register  to view this content.
    Last edited by davesexcel; 03-05-2020 at 07:02 AM.

  12. #12
    Forum Contributor starryknight64's Avatar
    Join Date
    09-27-2006
    Location
    Missouri
    MS-Off Ver
    2003 (Work) & 2007 (Home)
    Posts
    193
    I'm not sure if this does exactly what you want it to, but give this a shot.

    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    04-21-2012
    Location
    Greensboro, NC
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: What does "400" mean?

    I get exactly the same thing...no error message or anything...just a window that pops with "400" and an ok button, which, when pressed, kills the process. The difference is that mine blows up when I do Workbook.RefreshAll

  14. #14
    Registered User
    Join Date
    01-14-2011
    Location
    N/A
    MS-Off Ver
    Excel/Access/Word 2010
    Posts
    11

    Re: What does "400" mean?

    The way to make a sheet active is Sheets("whatever").Activate, not Sheets("whatever").Select. Only when a sheet is active can you use the Range method without any prefix. That is, Range("whatever") alone always means ActiveWorkbook.ActiveSheet.Range("whatever").

    However it's better to not worry about which sheet is active and also not use the Windows Clipboard. These are accomplished in one line as follows:

    Sheets("source").Range("source").Copy Destination:=Sheets("target").Range("target")

    ...or simply:

    Sheets("source").Range("source").Copy Sheets("target").Range("target")

    And just in case some other workbook might be active:

    ThisWorkbook.Sheets("source").Range("source").Copy ThisWorkbook.Sheets("target").Range("target")

    ...where ThisWorkbook is the Excel built-in for the workbook containing the running code.
    Last edited by jasmith4; 10-10-2013 at 12:01 PM. Reason: more info

  15. #15
    Registered User
    Join Date
    04-16-2015
    Location
    Cardiff, Wales
    MS-Off Ver
    2007
    Posts
    5

    Re: What does "400" mean?

    Hi. It once happened to me and it was because I had a formula wrong (I tried to calculate the standard deviation of a single value).

  16. #16
    Registered User
    Join Date
    03-19-2013
    Location
    Eugene, Oregon, USA
    MS-Off Ver
    Excel 2003, 2013, 2016, 365
    Posts
    5

    Re: What does "400" mean?

    I have seen this error when trying to change a cell's format (e.g. setting the NumberFormat property) on a worksheet that is protected with none of the protection options Format cells, Format columns, or Format rows set and user-interface-only protection not set. A solution is to use user-interface-only protection, which can be very useful and save a lot of programming effort when used properly, but it requires an understanding of its quirks and limitations. (See Chip Pearson's page: http://www.cpearson.com/excel/Protection.aspx.)

    It is likely that the 400 error will pop up when attempting to change other cell properties under those conditions but I haven't done any extensive verification of that.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1