Thread: 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.

Sub UpdateAll()
'
ActiveWorkbook.RefreshAll
Cells.Columns.AutoFit

End Sub

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!

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.

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.

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.

Works perfectly in the immediate window.

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:

Sub UpdateAll()
'
ActiveWorkbook.RefreshAll
DoEvents
Worksheets("Sheet1").Select
ActiveSheet.Cells.Columns.AutoFit

End Sub

to try to ensure you are working on the correct sheet and the update is done.

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?

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.

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.

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.

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.

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.

I'll try that. Thanks

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!

With Selection.ListObject.QueryTable
        .AdjustColumnWidth = False
End With

Thanks everyone for their suggestions!