Identify which columns in a datasheet are selected

Dale Fye
Dale Fye used Ask the Experts™
I'm exporting a datasheet to Excel via vba thanks to Nick67 and Capricorn1.  In that code, I exempt those columns from the datasheet that have been hidden by the user.  Now I would like to take that one step further and only export the columns that have selected by the user.  For example, if the user selects several columns that are next to each other and only want to export them.  

Is there a way to identify which columns are selected?  I assume so, since the Find functionality works on "selected" columns.  I just have not been able to identify the specific property of the datasheet or of the controls collection to identify.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2014
Hi @fyed,

I've never played much with the datasheet.
Are the columns selected always required to be contiguous?
I can't make it do anything but that.

I think this is what you need
It's from the Access 2010 help

Public Sub SetHeightWidth(ByRef frm As Form)

    Dim lngNumRows As Long

    Dim lngNumColumns As Long

    Dim lngTopRow As Long

    Dim lngLeftColumn As Long

    Dim strMsg As String

    ' Form is in Datasheet view.

    If frm.CurrentView = 2 Then


        ' Number of rows selected.

        lngNumRows = frm.SelHeight


        ' Number of columns selected.

        lngNumColumns = frm.SelWidth


        ' Topmost row selected.

        lngTopRow = frm.SelTop


        ' Leftmost column selected.

        lngLeftColumn = frm.SelLeft


        ' Display message.

        strMsg = "Number of rows: " & lngNumRows & vbCrLf

        strMsg = strMsg & "Number of columns: " _

             & lngNumColumns & vbCrLf

        strMsg = strMsg & "Top row: " & lngTopRow & vbCrLf

        strMsg = strMsg & "Left column: " & lngLeftColumn

        MsgBox strMsg, vbInformation

    End If


End Sub

Open in new window

We can already determine order, so if this spits out, like I think it will, where the selection starts and how many columns it is, it should give you what you need
Hi Dale

You can use the form's SelLeft and SelWidth properties to determine the left-most column and the number of columns selected.  You might need to play with it a bit, because in my brief experimentation I discovered that when the first column was selected, SelLeft was 2 (!!!)

Assuming your users are able to drag columns to change their order, the next problem will be identifying which controls are in the selected columns.  As far as I can tell, there is no easy way to convert a column index to a control name or field name.  For this you can use each control's ColumnOrder property.  Loop through all the eligible controls and populate an array with the control names using ColumnOrder as an index.  Then you can simply loop from SelLeft to SelLeft+SelWidth-1 to determine the fields to be exported.

You will probably also want to omit any hidden columns.  For this you can check each control's ColumnHidden property.

Good luck!
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010



We resolved the issue of which columns are visible and the sequence in my previous question.  But it looks like SelLeft and SelWidth will do what I'm looking for.  I'll play around with it a bit later

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial