Sub Worksheet_BeforeDoubleClick from elsewhere?

[Jack Sons]

Hi all,

I have this sub:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)

It works nicely when I double click in any row beneath row 1 on a cell in
any of the columns 5 or 45 or 72. It updates the rather complicated cell
comments of those three cells, it takes approximately 4 seconds.
I have some 600 rows. I use XP with Office 2k.

I want to execute what this sub does automatically from row 2 to row 600.
Automatically, because it is tedious and time consuming to double click 600
cells. Modifying this sub to execute it with a do loop is very difficult
(for me) because of all the "target stuff".

Is it possible to have this work one out of another sub like below (or
better, not like that because that won't work):

Sub easy()
rownumber=2
do until range(cells(rownumber,2),cells(rownumber,2))=""
range(cells(rownumber,45),cells(rownumber,45)).select
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean) 'obviously this line won't work
rownumber=rownumber+1
loop
end sub

Jack Sons
The Netherlands

 

[Jim Rech]

I didn't try to follow exactly what you're trying to do but I'd suggest to
put the guts of what's in sub Worksheet_BeforeDoubleClick and put it in a
new sub in a standard module. Then you can call it from both
Worksheet_BeforeDoubleClick and elsewhere. Naturally this sub should be
designed to accept a range argument.

--
Jim
| Hi all,
|
| I have this sub:
|
| Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
| Boolean)
|
| It works nicely when I double click in any row beneath row 1 on a cell in
| any of the columns 5 or 45 or 72. It updates the rather complicated cell
| comments of those three cells, it takes approximately 4 seconds.
| I have some 600 rows. I use XP with Office 2k.
|
| I want to execute what this sub does automatically from row 2 to row 600.
| Automatically, because it is tedious and time consuming to double click
600
| cells. Modifying this sub to execute it with a do loop is very difficult
| (for me) because of all the "target stuff".
|
| Is it possible to have this work one out of another sub like below (or
| better, not like that because that won't work):
|
| Sub easy()
| rownumber=2
| do until range(cells(rownumber,2),cells(rownumber,2))=""
| range(cells(rownumber,45),cells(rownumber,45)).select
| Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
| Boolean) 'obviously this line won't work
| rownumber=rownumber+1
| loop
| end sub
|
| Jack Sons
| The Netherlands
|
|

 

[Jack Sons]

Jim,

I followed your advice. Replacing the target stuff with range arguments was
not so difficult as I thought. It now works like charm. Thank you.

Jack.