I have a datasheet, created through VBA, and I am trying to automate setting the column widths to Best Fit. Currently I loop through the controls using the code below
Expand|Select|Wrap|Line Numbers
- 'Set width of columns to best fit data
- Dim ctl As Control
- For Each ctl In Forms![Form1].Controls
- If (ctl.ControlType = acTextBox) Then
- ctl.SetFocus
- Call TBBestFit
- End If
- Next ctl
Expand|Select|Wrap|Line Numbers
- Public Function TBBestFit()
- On Error GoTo ErrHandler
- SendKeys "%B", False
- DoCmd.RunCommand acCmdColumnWidth
- Exit Function
- ErrHandler:
- Select Case Err
- Case 2046
- 'Command not available
- MsgBox "Column width is not available at this time.", vbCritical, "Not Available"
- Case 2501
- 'Cancel selected in dialog box - do nothing
- Case Else
- MsgBox Err.Number & ":-" & vbCrLf & Err.Description
- End Select
- End Function
Thanks in advance for any help.