Refresh Table With VBA Without Changing Column Widths

excelbytes

Board Regular
Joined
Dec 11, 2014
Messages
245
Office Version
  1. 365
Platform
  1. Windows
I have a table called "Data" on a worksheet called "Goals For The Week" and I want the code to refresh the table. I am using:

Sub Workbook_RefreshAll()

Worksheets("Goals For The Week").ListObjects("Data").Refresh

End Sub

The only problem I have is that one of the columns has wrapped text, and when I run this code it adjusts the column width to fit the text and not wrap it, so that column becomes quite wide.

I was using this code:

Sub Workbook_RefreshAll()

ActiveWorkbook.RefreshAll

End Sub

But had the same result. Any suggestions to refresh the table without altering the column Width? Or do I just add a line of code after the refresh to adjust the column width back to what it was? If so, what is that code?
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Maybe something like
VBA Code:
Sub Workbook_RefreshAll()
Worksheets("Goals For The Week").ListObjects("Data").Refresh 
with Columns("A:A") 'change column to suit
.ColumnWidth = 42 'change width to suit
.WrapText = True
End With
end sub
 
Upvote 0
Here is the code I entered based on your recommendation:

Sub Workbook_RefreshAll()

Worksheets("Goals For The Week").ListObjects("Data").Refresh
With Columns("H:H")
.ColumnWidth = 40
.WrapText = True
End With

End Sub

It works when I run it manually with F8 step by step, but not when I have it assigned to a button. When I click on the button, it widens the column to fit the text. If I click on it again, I see it go to 40 wide, then go right back. What is happening?
 
Upvote 0
Do you have any Worksheet_change evnts running or are triggered by your codes?
They usually reside in the Sheet modules
 
Upvote 0
Do you have any Worksheet_change evnts running or are triggered by your codes?
They usually reside in the Sheet modules

See the attached. These are all the codes I have for this workbook. Independently they each run fine. But when I combine them or "Call" them, the don't react properly. Any thoughts? How would you combine them?
 

Attachments

  • VBA Codes.png
    VBA Codes.png
    52.5 KB · Views: 27
Upvote 0
I'd combine Refresh_All and ColumnWidths
Put the lines from columnwidth UNDER the refresh line in the RefreshAll code
 
Upvote 0
I'd combine Refresh_All and ColumnWidths
Put the lines from columnwidth UNDER the refresh line in the RefreshAll code
So, I've done that. However, when I assign it to a button, it doesn't change the column width. When I open the VBA editor and click on the Play button, it doesn't change the column width, but, when I F8 step through the code, it works fine. I don't get it...
 
Upvote 0
IS the button on the same worksheet as the table ?
Does this work ?
VBA Code:
Sub Workbook_RefreshAll()
Worksheets("Goals For The Week").ListObjects("Data").Refresh
With Worksheets("Goals For The Week").Columns("H:H")
.ColumnWidth = 40
.WrapText = True
End With 
end sub
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top