Solved Determine Column Width in Datasheet View (1 Viewer)

[JonXL]

Hello!

I am trying to determine the width of my columns in datasheet view. My code first SETS the columns to auto-width (.ColumnWidth = -2). I then have need to go and grab their widths (ie, need to auto-width them and then figure out what that auto-width is). When I go back to read the ColumnWidth property, however, it just tells me it is -2 (that they are auto-width). I want the actual twip value of their width.

What can I put to pull out the actual width value of the column after setting it to auto?

Thank you so much for any help!
Jon

 

[June7]

Review https://stackoverflow.com/questions/21634017/access-determine-column-width-after-auto-resize

 

[theDBguy]

@June7 That's a good find. I would probably just adjust all the columns to a width that will trigger the change in value, so I can skip the variable and checking the width after setting the autofit. In my quick test, the value 10 seems to be enough. Cheers!

 

[conception_native_0123]

Im not sure junes link gives u what u need. This says u can specify twips or -2:

TextBox.ColumnWidth property (Access)

Office VBA reference topic

docs.microsoft.com

 

[JonXL]

Review ...

Thank you.

Unfortunately that doesn't work. Here's code based on that principal and the resulting Debug.Print values. In every case, the returned value of .ColumnWidth is still -2 and not the width of the column. From your link: "I should note that I did try assigning a temporary dummy ColumnWidth (e.g., MyControl.ColumnWidth = 1) prior to assigning the -2 autofit value. That did not seem to work, though."


That seems to be my experience with this as well.

For Each ctl In frm.Controls
    With ctl
        .ColumnWidth = 2
        Debug.Print .ColumnWidth
        .ColumnWidth = -2
        Debug.Print .ColumnWidth
    End With
Next ctl
----------------------------------------
 2
-2
 2
-2
 2
-2
 2
-2
 2
-2

 

[theDBguy]

Thank you.

Unfortunately that doesn't work. Here's code based on that principal and the resulting Debug.Print values. In every case, the returned value of .ColumnWidth is still -2 and not the width of the column. From your link: "I should note that I did try assigning a temporary dummy ColumnWidth (e.g., MyControl.ColumnWidth = 1) prior to assigning the -2 autofit value. That did not seem to work, though."


That seems to be my experience with this as well.

For Each ctl In frm.Controls
    With ctl
        .ColumnWidth = 2
        Debug.Print .ColumnWidth
        .ColumnWidth = -2
        Debug.Print .ColumnWidth
    End With
Next ctl
----------------------------------------
2
-2
2
-2
2
-2
2
-2
2
-2

Hi. Just curious, did you see my post. In my quick tests, I was able to make it work using the value of 10. Could you give that a try and let us know what happens? Thanks.

 

[arnelgp]

ColumnWidth does not refer to the "column width"
it set whether the column will "auto-adjust" or not"
to get the the column width use the Width property.
even if you adjust the width of the column, it will
always return the same with.

 

[GK in the UK]

This works for me in a datasheet:

Public Function cm2twips(cm As Variant) As Integer
  ' returns the twips for moving and resizing a form from a metric input
  Const TwipsPerCm = 567
  cm2twips = cm * TwipsPerCm
End Function

Testing:

Me.txtDescription.ColumnWidth = cm2twips(6.5)
 
  Debug.Print "Me.txtDescription.ColumnWidth = " & Me.txtDescription.ColumnWidth
 
  Me.txtDescription.ColumnWidth = -2
 
  Debug.Print "Me.txtDescription.ColumnWidth = " & Me.txtDescription.ColumnWidth

Debug.Print result:

Me.txtDescription.ColumnWidth = 3686 < 6.5 x 567 so what we expect
Me.txtDescription.ColumnWidth = 2715 < auto size width, Access has reduced the column width

So I'm getting the column width after Access has auto-sized it. The text is in the form when I call the function.



Note to self: cm2twips is one of my first-ever functions. Why on earth did I specify Variant?

 

[CJ_London]

to get the the column width use the Width property.

correct me if I'm wrong (I'm sure you will ) but the width property refers to the width of the underlying control, not the column when in datasheet view

@GK - interesting solution - but why 6.5? and why need the function anyway? Setting the columnwidth to 7 or more still works

 

[JonXL]

Hi. Just curious, did you see my post. In my quick tests, I was able to make it work using the value of 10. Could you give that a try and let us know what happens? Thanks.

Thank you for insisting... That worked!

I didn't try that at first 'cause I didn't think it would make a difference what number I used (really it shouldn't make a difference, right?). But this program is a strange one. Here's the finished code that auto-sizes all the columns with the option to set one column to expand so as to fill the form up (or shrink so as to avoid it spilling over in the case I wanted it for):

Function AutoSizeColumns(frm As Form, Optional expansionColumn As Variant, Optional overallWidth As Variant)
    Dim ctl As Control
    On Error Resume Next
    For Each ctl In frm.Controls
        With ctl
            .ColumnWidth = 10
            .ColumnWidth = -2
        End With
    Next ctl
    If Not IsMissing(expansionColumn) Then
        Dim ctlExp As Control, totalWidth As Long, subWidth As Long
        Set ctlExp = expansionColumn
        subWidth = 0
        If IsMissing(overallWidth) Then totalWidth = frm.Width Else totalWidth = overallWidth
        For Each ctl In frm.Controls
            If Not ctl.Name = ctlExp.Name Then
                Select Case ctl.ControlType
                    Case acTextBox, acComboBox
                        subWidth = subWidth + ctl.ColumnWidth
                    Case Else
                        'do nothing
                End Select
            End If
        Next ctl
        ctlExp.ColumnWidth = (totalWidth - subWidth) - 255
    End If
End Function

 

[CJ_London]

just in case anyone is wondering why 7 or more, I think it is to do with the twips to pixel ratio. there are 15 twips to a pixel, anything less than a pixel effective means column is hidden which probably messes up the calculation. I suspect 7 will round up to 1 pixel while 6 or less rounds down to 0 pixels

 

[theDBguy]

Thank you for insisting... That worked!

I didn't try that at first 'cause I didn't think it would make a difference what number I used (really it shouldn't make a difference, right?). But this program is a strange one. Here's the finished code that auto-sizes all the columns with the option to set one column to expand so as to fill the form up (or shrink so as to avoid it spilling over in the case I wanted it for):

Function AutoSizeColumns(frm As Form, Optional expansionColumn As Variant, Optional overallWidth As Variant)
    Dim ctl As Control
    On Error Resume Next
    For Each ctl In frm.Controls
        With ctl
            .ColumnWidth = 10
            .ColumnWidth = -2
        End With
    Next ctl
    If Not IsMissing(expansionColumn) Then
        Dim ctlExp As Control, totalWidth As Long, subWidth As Long
        Set ctlExp = expansionColumn
        subWidth = 0
        If IsMissing(overallWidth) Then totalWidth = frm.Width Else totalWidth = overallWidth
        For Each ctl In frm.Controls
            If Not ctl.Name = ctlExp.Name Then
                Select Case ctl.ControlType
                    Case acTextBox, acComboBox
                        subWidth = subWidth + ctl.ColumnWidth
                    Case Else
                        'do nothing
                End Select
            End If
        Next ctl
        ctlExp.ColumnWidth = (totalWidth - subWidth) - 255
    End If
End Function

Hi. Glad to hear you got it sorted out. I ended up using 10, because I was testing it with the increments of 10. The way I understand the article, the columnwidth will return -2, if the size of the column, when autofit is used, is basically the same as the original width. So, I figure the safest approach is to shrink the column, and 10 was the next smallest one I tried that worked (I tried 0, 1, and 2, but they didn't work).