Refresh Table With VBA Without Changing Column Widths

excelbytes

Board Regular
Joined
Dec 11, 2014
Messages
69
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?
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,703
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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
 

excelbytes

Board Regular
Joined
Dec 11, 2014
Messages
69
Office Version
  1. 365
Platform
  1. Windows
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?
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,703
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Do you have any Worksheet_change evnts running or are triggered by your codes?
They usually reside in the Sheet modules
 

excelbytes

Board Regular
Joined
Dec 11, 2014
Messages
69
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,703
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
I'd combine Refresh_All and ColumnWidths
Put the lines from columnwidth UNDER the refresh line in the RefreshAll code
 

excelbytes

Board Regular
Joined
Dec 11, 2014
Messages
69
Office Version
  1. 365
Platform
  1. Windows
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...
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,703
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,454
Messages
5,596,228
Members
414,046
Latest member
mbeutler1203

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
Top