Dynamic pivot table column width

[#1]

Dear Sirs

I have a pivot table with DATE as the horizontal dimension like a calender.

I wish to develop a macro which dynamically change the column width according to the number of dates that the user selects.

The goal is that the table always has the same total width of 2000 pixels (if more dates are selected => columnwidth narrows and vice versa)

E.g.:

-if 91 days are selected => columnwidth = 2000/91

-if 31 days are selected => columnwidth = 2000/31

-minimum colum width = 20

-maximum column width = 200

Can anyone help with some macro skills? I believe I also have to define a variable like [count(distinct Date)] and somehow apply that variable in the macro.

Merry xmas to all!

Jesper

[Gysbert_Wassenaar]

This does what you asked for, but you'll probably end up with unreadable headers. Vertical headers seem to need about the same space as horizontal headers.

Sub

set val=ActiveDocument.Fields("Date").GetSelectedValues

ColCount = val.count

ColWidth = 2000/ColCount

if ColWidth > 200 then ColWidth = 200

if ColWidth < 20 then ColWidth = 20

set chart = ActiveDocument.GetSheetObject( "CH01" )

chart.SetPixWidth val.count,ColWidth

end sub

[#3]

Thanks, I cant get it to work I think this line in the macro is the problem:

ColWidth = 2000/ColCount

Do I need () og [] or somthing?

[#4]

Or: is the problem that the selections are not made in the DATE field but in the YEAR and MONTH field? (with underlying date values)

[#5]

It was - i nailed it 🙂

Thanks mr. W

🙂

[#6]

In addition:

Is it possible to develop a macro, that reads the total width of the pivot table (e.g. 2.200 pixels)

- deducts the column width of columns 1 and 2 (e.g. 150 pixels)

- uses the remaining 2.050 pixels in the above stated macro formula

?

[Gysbert_Wassenaar]

Try:

Sub  MySub

set val=ActiveDocument.Fields("Date").GetSelectedValues

ColCount = val.count

set chart = ActiveDocument.GetSheetObject( "CH01" )

pwidths = chart.GetPixWidths(0,2).PixWidths

set fr = chart.GetFrameDef

set pos = fr.Rect

restwidth = pos.Width - pwidths(0) - pwidths(1)

ColWidth = restwidth/ColCount

if ColWidth > 200 then ColWidth = 200

if ColWidth < 20 then ColWidth = 20

chart.SetPixWidth val.count,ColWidth

end sub

[#8]

For some reason i need to convert doc units to pixels so this one works.

Thanks.

set val=ActiveDocument.Fields("RessourceStartdato").GetPossibleValues
ColCount = val.count

set chart = ActiveDocument.GetSheetObject("CH71")

pwidths = chart.GetPixWidths(0,2).PixWidths
set fr = chart.GetFrameDef
set pos = fr.Rect

restwidth = pos.Width/3.125 - pwidths(0) - pwidths(1) - 50

ColWidth = restwidth/ColCount
if ColWidth > 200 then ColWidth = 200
if ColWidth < 20 then ColWidth = 20
chart.SetPixWidth val.count,ColWidth

[orital81]

Hi Gysbert, this is an old discussion but relevant for me right now.

Im using your Macro suggestion, it's working great except when I clear selection.

When clearing selection the macro fails (I guess since ColCount= 0 and we devide by 0)

Im trying to overcome this by using GetPossibleCount in a variable and sending it to the function

but I cannot get it working..

I will appreciate your help here. See attached file.

CAdjust1 is using the variable while CAdjust is without (doesn't work when I clear all)

Thanks in advance

Ori