VBA Access use Highlighted fields

Question

In a current access DB users want to highlight a certain range of cells in a column with their mouse, and use this selection in a vba script. (ex. they select a range -> vba script fills this range with a certain value)

Our problem is: How do we define this selected range in VBA? There are plenty of examples where you use VBA to HIGHLIGHT cells, but not how to use a highlighted range in a VB script inside Access.

Is this possible? If so, how do we address this range?

Thank you for your time.

Answer 1

A form in datasheet view (or a table/query) has properties to get the selected range. The following is mostly copied from online help:

Dim frm As Form
Dim lngNumRows As Long
Dim lngNumColumns As Long
Dim lngTopRow As Long
Dim lngLeftColumn As Long
Dim strMsg As String

' Datasheet that has the focus
Set frm = Screen.ActiveDatasheet

' Number of rows selected.
lngNumRows = frm.SelHeight

' Number of columns selected.
lngNumColumns = frm.SelWidth

' Topmost row selected.
lngTopRow = frm.SelTop

' Leftmost column selected.
'** Note: for me, this is always the actual left column + 1 **
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

In my Access 2010, .SelLeft is always too high by 1. So if my selection starts in the first column, it returns 2. Not sure if it is supposed to do that.

Note: .SelTop and .SelHeight work also for a continuous form in Form View, when selecting records with the record selectors.