0

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?

Improve this question
1
  • Hi, did the answer below help? Commented Jul 2, 2014 at 17:50

1 Answer 1

Reset to default
0

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

This is a good idea. If I add checkbox, checkboxes cannot be filtered. Your method only requires single click. I would like to set font to Wingdings so that a character can present as a tick.
Yup, It's quite efficient (in terms of mouse movement) and can be used via just selecting using the keyboard too (moving in and out of the target zone for toggling).
Would appreciate an upvote if you think it'll work for you. Thanks :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.