+ Reply to Thread
Results 1 to 3 of 3

Sub Worksheet_BeforeDoubleClick from elsewhere?

  1. #1
    Jack Sons
    Guest

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



  2. #2
    Jim Rech
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    | 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
    |
    |



  3. #3
    Jack Sons
    Guest

    Re: 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" <[email protected]> schreef in bericht
    news:%[email protected]...
    >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" <[email protected]> wrote in message
    > news:[email protected]...
    > | 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
    > |
    > |
    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1