+ Reply to Thread
Results 1 to 14 of 14

Conflicting formatting results

  1. #1
    Forum Contributor
    Join Date
    10-16-2010
    Location
    Memphis, TN
    MS-Off Ver
    Excel 2003, 2010
    Posts
    151

    Conflicting formatting results

    I am working on a project where I bring data in from our accounting software's database to create some inventory count sheets. In order to ensure I have the latest data, I have a macro that is supposed to update the table, and format the columns to autofit the contents.

    Please Login or Register  to view this content.
    When it runs, it does the update, it does the autofit, and then it sets the columns of the table portion of the sheet to 8.43. I have tried using a range specifying the exact columns I needed formatted and get the same result. I don't see what I am doing wrong, and when I step through it using the debugger it finishes correctly.

    Thanks for your help!
    Last edited by jacob@thepenpoint; 01-25-2012 at 09:07 AM.
    Jacob Albers
    Excel 2003 & 2010

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,271

    Re: Conflicting formatting results

    Hey Jacob,

    Perhaps you are not on the correct sheet? You might need to select a sheet before doing the autofit?

    If that doesn't work... Try to put a DoEvents between the refresh and autofit lines.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Contributor
    Join Date
    10-16-2010
    Location
    Memphis, TN
    MS-Off Ver
    Excel 2003, 2010
    Posts
    151

    Re: Conflicting formatting results

    Thanks Marvin

    I tried both of those suggestions and am still getting the same result. It doesn't make any sense to me that it would actually do the autofit then take it back to the default column width.

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,271

    Re: Conflicting formatting results

    Hey Jacob,

    What happens if you do the Cells.Columns.AutoFit in the immediate window? Does that work correctly? It did for me using 2010.

  5. #5
    Forum Contributor
    Join Date
    10-16-2010
    Location
    Memphis, TN
    MS-Off Ver
    Excel 2003, 2010
    Posts
    151

    Re: Conflicting formatting results

    Works perfectly in the immediate window.

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,271

    Re: Conflicting formatting results

    Hey Jacob,

    I'm thinking you are selecting the wrong sheet in your code OR the code is fireing so fast the data hasn't refreshed yet.
    Something like:
    Please Login or Register  to view this content.
    to try to ensure you are working on the correct sheet and the update is done.

  7. #7
    Forum Contributor
    Join Date
    10-16-2010
    Location
    Memphis, TN
    MS-Off Ver
    Excel 2003, 2010
    Posts
    151

    Re: Conflicting formatting results

    Thanks Marvin

    I am still getting the same result. I know it takes about 5 seconds to populate the table originally from the database it is coming from, so I don't know of any way to slow the macro down to let the data refresh. After watching this thing run several times, it looks like you are correct; the refresh takes longer to complete than the macro takes to run so the refresh undoes what the autofit has accomplished.

    Right now, I feel like a lost ball in the high weeds. Is there a way to call one macro from another? Would it help to break these activities into two separate macros and call the autofit from the end of the refresh all? Or is there a way to make the program wait until the first command has completed its action before moving along to the second command?

  8. #8
    Forum Contributor
    Join Date
    10-16-2010
    Location
    Memphis, TN
    MS-Off Ver
    Excel 2003, 2010
    Posts
    151

    Re: Conflicting formatting results

    I just tried something to see if I could see what was happening when. I deleted some data in my table, then ran the macro again. The autofit took place before the missing data was replaced by the refresh, so it is firing too fast.

  9. #9
    Forum Contributor
    Join Date
    10-16-2010
    Location
    Memphis, TN
    MS-Off Ver
    Excel 2003, 2010
    Posts
    151

    Re: Conflicting formatting results

    Marvin, thanks for pointing me in the right direction. I was going around in circles trying to find something wrong with the code while it was just getting ahead of itself.

    I am going to call this one solved. Not exactly what I was looking for, but it works. Instead of combining both into one macro, I just added a button for the user and one will update the data, and the other will reformat the results. This after trying a wait statement to give it a few seconds to update which didn't work either. Anyway, the 2 macro solution will work.

  10. #10
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,271

    Re: Conflicting formatting results

    Hey jacob,

    A real mystery is one you never figure out. Now - how do you make one line of code finish before the next one runs?

    Try putting a mandatory .Calculate in between your two lines of code. DoEvents (I believe) waits for other windows applications to pause before continuing. I always follow any SendKeys statements in my code with DoEvents to make sure that they were sucked out of the keyboard buffer, before proceeding. Maybe telling Excel it needs to recalculate the entire workbook would be the pause you need.
    See http://www.decisionmodels.com/calcsecretsh.htm or http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

    BTW - do you have a Application.ScreenUpdate = False in your code? This might be keeping the AutoFit from knowing how wide things are.

  11. #11
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Conflicting formatting results

    I suspect your query is set to refresh in the background. You simply need to turn that off, or refresh that table specifically and then you specify the Backgroundquery:=False argument.
    Good luck.

  12. #12
    Forum Contributor
    Join Date
    10-16-2010
    Location
    Memphis, TN
    MS-Off Ver
    Excel 2003, 2010
    Posts
    151

    Re: Conflicting formatting results

    I tried the DoEvents between the two lines with no luck, I'll try the .calculate to see if that helps. I never did an Application.ScreenUpdate line in the code, so I am watching it do its thing. I generally leave that til last so that I can see that everything is proceeding as expected, (plus it reminds me of the magic drawing board of Captain Kangaroo watching all the screen updates).

    Thanks for all of your help and suggestions. I will continue to play with it to see if I can get it to work properly.

  13. #13
    Forum Contributor
    Join Date
    10-16-2010
    Location
    Memphis, TN
    MS-Off Ver
    Excel 2003, 2010
    Posts
    151

    Re: Conflicting formatting results

    I'll try that. Thanks

  14. #14
    Forum Contributor
    Join Date
    10-16-2010
    Location
    Memphis, TN
    MS-Off Ver
    Excel 2003, 2010
    Posts
    151

    Re: Conflicting formatting results

    Well, I finally found a solution that worked! I didn't need to autofit the cells in the first place, I just needed to untick the data property that resets the cell width. AAACCCKKKK!
    Please Login or Register  to view this content.
    Thanks everyone for their suggestions!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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