CODEWITHSUNDEEP
vbaexcel
user746461
user746461

Reputation:

Select multiple rows in Excel and read by VBA

I'm trying to implement the following Java program in Excel VBA.

enter image description here

The Java program has a data list with checkbox. I can select any rows by checking the checkboxes, then I click Show button, then the program opens a new window showing the detail statistics of the selected players.

Here is my Excel. I need some way to select rows or player names. I can select while pressing Ctrl key but it is not as convenient as checkbox, at least my left hand has to touch keyboard.

enter image description here

I may add checkbox to each row. As the data have hundreds to thousands rows, vba runs very slow.

Do you have any ideas that how can I make an interface that allows user to select multiple rows conveniently?

Upvotes: 0

Views: 1275

Answers (1)

hnk
hnk

Reputation: 2214

This will work as follows. For e.g. you need to toggle True/False values in a range A1:A10 on a sheet. Then open the VBE window (Alt-F11) and go to the Sheet, right click, select the event for SelectionChange() and write the following code. It's fairly self explanatory:

Private Sub Worksheet_SelectionChange(ByVal Target as Range)
    Dim Intersection as Range
    Set Intersection = Application.Intersect(Target, Range("A1:A10")

    If Intersection Is Nothing Then
        ' Do nothing
    Else
        ActiveCell.Value = Not (ActiveCell.Value)
    End If
End Sub

That way in a single click, you will toggle the true-false value of a given cell. The rest of the code can then take over. The Range A1:A10 for a Table in Excel can be dynamically captured by using

TestBoundsRange = ListObject.ListColumns("ColumnName").DataBodyRange

Where you replace ListObject by the ListObject variable pointing to the correct Table.

Upvotes: 0

Related Questions