Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Declare range of cells in Macro (2003 SP2)

    Good morning all

    As part of a Macro I have the following

    rowNbr = Target.Row
    Select Case rowNbr
    Case 6

    Case 6 selects cells in row 6 when double clicked, however I would now like to be more specific and have tried Case D12:N54 but It keeps on making an error

    An ideas please

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Declare range of cells in Macro (2003 SP2)

    D12:N54 is obviously not a row number. You can do the following instead:

    If Not Intersect(Range("D12:N54"), Target) Is Nothing Then
    ...
    End If

    Intersect(Range("D12:N54"), Target) is the range of cells that the range D12:N54 and the Target (the cell or cells double clicked by the user) have in common.

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Declare range of cells in Macro (2003 SP2)

    Fantastic

    Thanks Hans, I have been trying all sorts since about 04:00 to do that

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Declare range of cells in Macro (2003 SP2)

    Hi Hans

    I thought that this was working but I have now found that if double click in any cell from A1 to IV65536 it still invokes the macro/combo where as I thought that by naming as suggested it would only work within those cells, any other suggestions please

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Declare range of cells in Macro (2003 SP2)

    I have no idea what your code looks like, so <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Declare range of cells in Macro (2003 SP2)

    Hi Hans

    My apologies, I thought that the advice you gave

    If Not Intersect(Range("D12:N54"), Target) Is Nothing Then

    End If

    Meant that only that range (D12:N54) could be selected. My misinterpretation.

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Declare range of cells in Macro (2003 SP2)

    Any code between those two lines will be executed only if the user double-clicked a cell in the range D12:N54. Code in the Worksheet_BeforeDoubleClick procedure that is *not* between those two lines will obviously not be limited to that range.

    In the code that you posted in another thread (Post: 735,902]) you have these two lines but as noted they don't have any effect because there is no code between them.

Posting Permissions

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