VBA Counting a range ofhgighlighted cells - before select
 

In VBA I can count a range of cells (target.cells.count) ...but is there
a way to count the cells in a range "real-time" as I am highlighting
them & before I "select"? worksheet_selectionchange doesn't kick in
until I have finished highlighting the range.

VBA Counting a range ofhgighlighted cells - before select
 

I don't understand th equestion. What do you mean by "highlighting"v them?
I'd have thought that by this you mean you're selecting a range of cells -"
selecting" will obviously fire the selection change event after the selection
is made.
please explain.

"Doug" wrote:

In VBA I can count a range of cells (target.cells.count) ...but is there
a way to count the cells in a range "real-time" as I am highlighting
them & before I "select"? worksheet_selectionchange doesn't kick in
until I have finished highlighting the range.

VBA Counting a range ofhgighlighted cells - before select
 

Thank you for your reply.
If I use the mouse to highlight range A1:B1 I would like to capture the
value 2 (2 cells), if I then move the mouse to cover A1:D1 then 4, if I
move back to cover A1:C1 then 3. SelectionChange doesn't fire until I
release the mouse key. Is it possible to get a value returned before
releasing the mouse key?

I've been looking at MouseUp & MouseDown events but they only seem to
work for charts or forms - not worksheets.

Patrick Molloy wrote:
I don't understand th equestion. What do you mean by "highlighting"v them?
I'd have thought that by this you mean you're selecting a range of cells -"
selecting" will obviously fire the selection change event after the selection
is made.
please explain.

"Doug" wrote:


In VBA I can count a range of cells (target.cells.count) ...but is there
a way to count the cells in a range "real-time" as I am highlighting
them & before I "select"? worksheet_selectionchange doesn't kick in
until I have finished highlighting the range.

VBA Counting a range ofhgighlighted cells - before select
 

hmmm

if you select each cell one at a time, then the selection change fires for
each cell. If you select a group of cells, then the selection change only
fires after they are selected.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A1") = Target.Count
End Sub

As far as I can tell, there's no way that Excel can trap a multiple
selection until control is released back to Excel

"Doug" wrote:

Thank you for your reply.
If I use the mouse to highlight range A1:B1 I would like to capture the
value 2 (2 cells), if I then move the mouse to cover A1:D1 then 4, if I
move back to cover A1:C1 then 3. SelectionChange doesn't fire until I
release the mouse key. Is it possible to get a value returned before
releasing the mouse key?

I've been looking at MouseUp & MouseDown events but they only seem to
work for charts or forms - not worksheets.

Patrick Molloy wrote:
I don't understand th equestion. What do you mean by "highlighting"v them?
I'd have thought that by this you mean you're selecting a range of cells -"
selecting" will obviously fire the selection change event after the selection
is made.
please explain.

"Doug" wrote:


In VBA I can count a range of cells (target.cells.count) ...but is there
a way to count the cells in a range "real-time" as I am highlighting
them & before I "select"? worksheet_selectionchange doesn't kick in
until I have finished highlighting the range.

VBA Counting a range ofhgighlighted cells - before select
 

....what you could do, is have a named range, and if you select any cell in
that range, the entire range gets selected for you...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("MyRange")) Is Nothing Then
Application.EnableEvents = False
Range("MyRange").Select
Application.EnableEvents = True
End If
End Sub

"Doug" wrote:

Thank you for your reply.
If I use the mouse to highlight range A1:B1 I would like to capture the
value 2 (2 cells), if I then move the mouse to cover A1:D1 then 4, if I
move back to cover A1:C1 then 3. SelectionChange doesn't fire until I
release the mouse key. Is it possible to get a value returned before
releasing the mouse key?

I've been looking at MouseUp & MouseDown events but they only seem to
work for charts or forms - not worksheets.

Patrick Molloy wrote:
I don't understand th equestion. What do you mean by "highlighting"v them?
I'd have thought that by this you mean you're selecting a range of cells -"
selecting" will obviously fire the selection change event after the selection
is made.
please explain.

"Doug" wrote:


In VBA I can count a range of cells (target.cells.count) ...but is there
a way to count the cells in a range "real-time" as I am highlighting
them & before I "select"? worksheet_selectionchange doesn't kick in
until I have finished highlighting the range.