Sub Worksheet_BeforeDoubleClick from elsewhere?
 

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)).sel ect
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

Sub Worksheet_BeforeDoubleClick from elsewhere?
 

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
"Jack Sons" wrote in message
...
| 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)).sel ect
| 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
|
|

Sub Worksheet_BeforeDoubleClick from elsewhere?
 

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.

"Jim Rech" schreef in bericht
...
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
"Jack Sons" wrote in message
...
| 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)).sel ect
| 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
|
|