CODEWITHSUNDEEP
vbaexcel
Sam
Sam

Reputation: 497

How do I set the column width when using ListObjects(1).ListColumns?

Here's the offending code...

Sub DoStuff()

  Dim objSheet As Worksheet
  Dim objStr As String

  Set objSheet = ActiveSheet
  objStr = objSheet.ListObjects(1).Name
  If objStr = "TaskTable" Then
     With objSheet.ListObjects(1)   ' Format for Tasks
        .ListColumns.Add Position:=3
        .HeaderRowRange(3) = "Status Image"  
        'Resize here

The problem is that I can not figure out the property/method/other to set the column width for the specific column in the list object.

Upvotes: 0

Views: 7234

Answers (2)

The Dude
The Dude

Reputation: 314

Use the AutoFit property.

With ActiveSheet.ListObjects("Table Name")
   .ListColumns.Add(3).Name = "Status Image"
   .ListColumns("Status Image").Range.Columns.AutoFit
End With

Upvotes: 2

Aaron Thomas
Aaron Thomas

Reputation: 5281

A good way to find that property you're looking for is to set a breakpoint in the code (left click in the left-most grey column in the IDE), and use the locals window during debugging.

For instance, if a breakpoint is set at the line .HeaderRowRange(3) = "Status Image", and you run the code, the code will stop at that line. Open up the locals window (View, then Locals). You'll have to drill down a bit, but under objSheet you'll see all the properties being used, including ListObjects. Drill into ListObjects, Item1 (the TaskTable in your code), ListColumns, Item3 (the added column in your code), Range, and ColumnWidth, you'll see the property (as a Variant or Double) that you can use.

To insert in your code, add the following in place of your 'Resize here comment:

.ListColumns(3).Range.ColumnWidth = x

where "x" is the size of the column. See here for more info about the ColumnWidth property.

Upvotes: 2

Related Questions