Hi,

I have VBA code that is actived with the Worksheet_BeforeDoubleClick event
and I want to
find out if the cell that was double-clicked is within a named range. For
example, if I have a
range named Headers that is defined as A1:H3 and cell B2 is double-clicked I
want to run
the code otherwise I exit the sub.

Any ideas on how to do this?

Thanks.

if not intersect(Range("Headers"),Target) is nothing then
' Target is in the range

--
Regards,
Tom Ogilvy

"Seamus Conlon" wrote in message
...

Hi,

I have VBA code that is actived with the Worksheet_BeforeDoubleClick event
and I want to
find out if the cell that was double-clicked is within a named range. For
example, if I have a
range named Headers that is defined as A1:H3 and cell B2 is double-clicked
I
want to run
the code otherwise I exit the sub.

Any ideas on how to do this?

Thanks.

Thanks for that, Tom

One further query. After the double-click I want to process cell values
starting in the row below
the last row in the Headers range and on the same column as the cell that
was double-clicked,
(activecell.column). In the example that I gave, this would be cell B4.

Regards,
Seamus


"Tom Ogilvy" wrote in message
...
if not intersect(Range("Headers"),Target) is nothing then
' Target is in the range

--
Regards,
Tom Ogilvy

"Seamus Conlon" wrote in message
...

Hi,

I have VBA code that is actived with the Worksheet_BeforeDoubleClick
event
and I want to
find out if the cell that was double-clicked is within a named range.
For
example, if I have a
range named Headers that is defined as A1:H3 and cell B2 is
double-clicked
I
want to run
the code otherwise I exit the sub.

Any ideas on how to do this?

Thanks.

Ok, but I should have stated that the user may have clicked any one of the
cells
in the header, i.e. B1, B2 or B3, so the one below the target will not
always be
where I want to process from. That's why I wanted to find the last row in
the range.

Thanks,
Seamus

"Tom Ogilvy" wrote in message
...
a reference to the cell double clicked is Target

Target.Offset(1,0)
is the cell below.

--
Regards,
Tom Ogilvy

"Seamus Conlon" wrote in message
...
Thanks for that, Tom

One further query. After the double-click I want to process cell values
starting in the row below
the last row in the Headers range and on the same column as the cell
that
was double-clicked,
(activecell.column). In the example that I gave, this would be cell B4.

Regards,
Seamus


"Tom Ogilvy" wrote in message
...
if not intersect(Range("Headers"),Target) is nothing then
' Target is in the range

--
Regards,
Tom Ogilvy

"Seamus Conlon" wrote in message
...

Hi,

I have VBA code that is actived with the Worksheet_BeforeDoubleClick
event
and I want to
find out if the cell that was double-clicked is within a named
range.
For
example, if I have a
range named Headers that is defined as A1:H3 and cell B2 is
double-clicked
I
want to run
the code otherwise I exit the sub.

Any ideas on how to do this?

Thanks.

Target.column gives you the column of the cell clicked. I am not sure what
the header range is or how to refer to it. If i want the last filled row in
the subject column I would use


set rng = cells(rows.count,target.column).End(xlup)



--
Regards,
Tom Ogilvy

"Seamus Conlon" wrote in message
...
Ok, but I should have stated that the user may have clicked any one of the
cells
in the header, i.e. B1, B2 or B3, so the one below the target will not
always be
where I want to process from. That's why I wanted to find the last row in
the range.

Thanks,
Seamus

"Tom Ogilvy" wrote in message
...
a reference to the cell double clicked is Target

Target.Offset(1,0)
is the cell below.

--
Regards,
Tom Ogilvy

"Seamus Conlon" wrote in message
...
Thanks for that, Tom

One further query. After the double-click I want to process cell
values
starting in the row below
the last row in the Headers range and on the same column as the cell
that
was double-clicked,
(activecell.column). In the example that I gave, this would be cell
B4.

Regards,
Seamus


"Tom Ogilvy" wrote in message
...
if not intersect(Range("Headers"),Target) is nothing then
' Target is in the range

--
Regards,
Tom Ogilvy

"Seamus Conlon" wrote in message
...

Hi,

I have VBA code that is actived with the
Worksheet_BeforeDoubleClick
event
and I want to
find out if the cell that was double-clicked is within a named
range.
For
example, if I have a
range named Headers that is defined as A1:H3 and cell B2 is
double-clicked
I
want to run
the code otherwise I exit the sub.

Any ideas on how to do this?

Thanks.

Maybe I should explain a bit better?

Let's suppose that Header is a named range of A1: U4 and has labels and
info about the data, which starts at row 5 and currently occupies range
A5: U35. The user clicks on any cell in the header and I want to do various
calculations on that column of data. I don't want to hard code any row
and column numbers so I thought that by using range names I could allow
for insertions and deletions of rows and columns in both header and data.
That's why I want to programmatically find the row at which the data
starts, which I guess would be the starting row of the range named Data
Maybe this is easier to find than the row after the last row in the header
range.

Thanks again,
Seamus



"Tom Ogilvy" wrote in message
...
Target.column gives you the column of the cell clicked. I am not sure what
the header range is or how to refer to it. If i want the last filled row
in
the subject column I would use


set rng = cells(rows.count,target.column).End(xlup)



--
Regards,
Tom Ogilvy

"Seamus Conlon" wrote in message
...
Ok, but I should have stated that the user may have clicked any one of
the
cells
in the header, i.e. B1, B2 or B3, so the one below the target will not
always be
where I want to process from. That's why I wanted to find the last row
in
the range.

Thanks,
Seamus

"Tom Ogilvy" wrote in message
...
a reference to the cell double clicked is Target

Target.Offset(1,0)
is the cell below.

--
Regards,
Tom Ogilvy

"Seamus Conlon" wrote in message
...
Thanks for that, Tom

One further query. After the double-click I want to process cell
values
starting in the row below
the last row in the Headers range and on the same column as the cell
that
was double-clicked,
(activecell.column). In the example that I gave, this would be cell
B4.

Regards,
Seamus


"Tom Ogilvy" wrote in message
...
if not intersect(Range("Headers"),Target) is nothing then
' Target is in the range

--
Regards,
Tom Ogilvy

"Seamus Conlon" wrote in message
...

Hi,

I have VBA code that is actived with the
Worksheet_BeforeDoubleClick
event
and I want to
find out if the cell that was double-clicked is within a named
range.
For
example, if I have a
range named Headers that is defined as A1:H3 and cell B2 is
double-clicked
I
want to run
the code otherwise I exit the sub.

Any ideas on how to do this?

Thanks.

It wouldn't be difficult either way

set rng = Range("Header")
rowbelow = rng.rows(rng.rows.count).row + 1


set rng = Range("Data")
rowbelow = rng.rows(1).row


set cell = cells(rowbelow, Target.column)

set col_Data = intersect(cell.EntireColumn, Range("Data")).Cells
mysum = Application.Sum(col_data)

--
Regards
Tom Ogilvy

"Seamus Conlon" wrote in message
...
Maybe I should explain a bit better?

Let's suppose that Header is a named range of A1: U4 and has labels and
info about the data, which starts at row 5 and currently occupies range
A5: U35. The user clicks on any cell in the header and I want to do
various
calculations on that column of data. I don't want to hard code any row
and column numbers so I thought that by using range names I could allow
for insertions and deletions of rows and columns in both header and data.
That's why I want to programmatically find the row at which the data
starts, which I guess would be the starting row of the range named Data
Maybe this is easier to find than the row after the last row in the header
range.

Thanks again,
Seamus



"Tom Ogilvy" wrote in message
...
Target.column gives you the column of the cell clicked. I am not sure
what
the header range is or how to refer to it. If i want the last filled
row
in
the subject column I would use


set rng = cells(rows.count,target.column).End(xlup)



--
Regards,
Tom Ogilvy

"Seamus Conlon" wrote in message
...
Ok, but I should have stated that the user may have clicked any one of
the
cells
in the header, i.e. B1, B2 or B3, so the one below the target will not
always be
where I want to process from. That's why I wanted to find the last row
in
the range.

Thanks,
Seamus

"Tom Ogilvy" wrote in message
...
a reference to the cell double clicked is Target

Target.Offset(1,0)
is the cell below.

--
Regards,
Tom Ogilvy

"Seamus Conlon" wrote in message
...
Thanks for that, Tom

One further query. After the double-click I want to process cell
values
starting in the row below
the last row in the Headers range and on the same column as the
cell
that
was double-clicked,
(activecell.column). In the example that I gave, this would be
cell
B4.

Regards,
Seamus


"Tom Ogilvy" wrote in message
...
if not intersect(Range("Headers"),Target) is nothing then
' Target is in the range

--
Regards,
Tom Ogilvy

"Seamus Conlon" wrote in message
...

Hi,

I have VBA code that is actived with the
Worksheet_BeforeDoubleClick
event
and I want to
find out if the cell that was double-clicked is within a named
range.
For
example, if I have a
range named Headers that is defined as A1:H3 and cell B2 is
double-clicked
I
want to run
the code otherwise I exit the sub.

Any ideas on how to do this?

Thanks.

Thanks Tom, that worked great.

And one final question - I swear. How do I hide/unhide all the rows and/or
columns in the
range named Data?

Thanks,
Seamus


"Tom Ogilvy" wrote in message
...
It wouldn't be difficult either way

set rng = Range("Header")
rowbelow = rng.rows(rng.rows.count).row + 1


set rng = Range("Data")
rowbelow = rng.rows(1).row


set cell = cells(rowbelow, Target.column)

set col_Data = intersect(cell.EntireColumn, Range("Data")).Cells
mysum = Application.Sum(col_data)

--
Regards
Tom Ogilvy

"Seamus Conlon" wrote in message
...
Maybe I should explain a bit better?

Let's suppose that Header is a named range of A1: U4 and has labels and
info about the data, which starts at row 5 and currently occupies range
A5: U35. The user clicks on any cell in the header and I want to do
various
calculations on that column of data. I don't want to hard code any row
and column numbers so I thought that by using range names I could allow
for insertions and deletions of rows and columns in both header and
data.
That's why I want to programmatically find the row at which the data
starts, which I guess would be the starting row of the range named Data
Maybe this is easier to find than the row after the last row in the
header
range.

Thanks again,
Seamus



"Tom Ogilvy" wrote in message
...
Target.column gives you the column of the cell clicked. I am not sure
what
the header range is or how to refer to it. If i want the last filled
row
in
the subject column I would use


set rng = cells(rows.count,target.column).End(xlup)



--
Regards,
Tom Ogilvy

"Seamus Conlon" wrote in message
...
Ok, but I should have stated that the user may have clicked any one
of
the
cells
in the header, i.e. B1, B2 or B3, so the one below the target will
not
always be
where I want to process from. That's why I wanted to find the last
row
in
the range.

Thanks,
Seamus

"Tom Ogilvy" wrote in message
...
a reference to the cell double clicked is Target

Target.Offset(1,0)
is the cell below.

--
Regards,
Tom Ogilvy

"Seamus Conlon" wrote in message
...
Thanks for that, Tom

One further query. After the double-click I want to process
cell
values
starting in the row below
the last row in the Headers range and on the same column as the
cell
that
was double-clicked,
(activecell.column). In the example that I gave, this would be
cell
B4.

Regards,
Seamus


"Tom Ogilvy" wrote in message
...
if not intersect(Range("Headers"),Target) is nothing then
' Target is in the range

--
Regards,
Tom Ogilvy

"Seamus Conlon" wrote in message
...

Hi,

I have VBA code that is actived with the
Worksheet_BeforeDoubleClick
event
and I want to
find out if the cell that was double-clicked is within a
named
range.
For
example, if I have a
range named Headers that is defined as A1:H3 and cell B2 is
double-clicked
I
want to run
the code otherwise I exit the sub.

Any ideas on how to do this?

Thanks.

One way:

Dim rng As Range
Set rng = ActiveSheet.Range("data")
rng.EntireColumn.Hidden = True 'False
rng.EntireRow.Hidden = True 'False



Seamus Conlon wrote:

Thanks Tom, that worked great.

And one final question - I swear. How do I hide/unhide all the rows and/or
columns in the
range named Data?

Thanks,
Seamus

"Tom Ogilvy" wrote in message
...
It wouldn't be difficult either way

set rng = Range("Header")
rowbelow = rng.rows(rng.rows.count).row + 1


set rng = Range("Data")
rowbelow = rng.rows(1).row


set cell = cells(rowbelow, Target.column)

set col_Data = intersect(cell.EntireColumn, Range("Data")).Cells
mysum = Application.Sum(col_data)

--
Regards
Tom Ogilvy

"Seamus Conlon" wrote in message
...
Maybe I should explain a bit better?

Let's suppose that Header is a named range of A1: U4 and has labels and
info about the data, which starts at row 5 and currently occupies range
A5: U35. The user clicks on any cell in the header and I want to do
various
calculations on that column of data. I don't want to hard code any row
and column numbers so I thought that by using range names I could allow
for insertions and deletions of rows and columns in both header and
data.
That's why I want to programmatically find the row at which the data
starts, which I guess would be the starting row of the range named Data
Maybe this is easier to find than the row after the last row in the
header
range.

Thanks again,
Seamus



"Tom Ogilvy" wrote in message
...
Target.column gives you the column of the cell clicked. I am not sure
what
the header range is or how to refer to it. If i want the last filled
row
in
the subject column I would use


set rng = cells(rows.count,target.column).End(xlup)



--
Regards,
Tom Ogilvy

"Seamus Conlon" wrote in message
...
Ok, but I should have stated that the user may have clicked any one
of
the
cells
in the header, i.e. B1, B2 or B3, so the one below the target will
not
always be
where I want to process from. That's why I wanted to find the last
row
in
the range.

Thanks,
Seamus

"Tom Ogilvy" wrote in message
...
a reference to the cell double clicked is Target

Target.Offset(1,0)
is the cell below.

--
Regards,
Tom Ogilvy

"Seamus Conlon" wrote in message
...
Thanks for that, Tom

One further query. After the double-click I want to process
cell
values
starting in the row below
the last row in the Headers range and on the same column as the
cell
that
was double-clicked,
(activecell.column). In the example that I gave, this would be
cell
B4.

Regards,
Seamus


"Tom Ogilvy" wrote in message
...
if not intersect(Range("Headers"),Target) is nothing then
' Target is in the range

--
Regards,
Tom Ogilvy

"Seamus Conlon" wrote in message
...

Hi,

I have VBA code that is actived with the
Worksheet_BeforeDoubleClick
event
and I want to
find out if the cell that was double-clicked is within a
named
range.
For
example, if I have a
range named Headers that is defined as A1:H3 and cell B2 is
double-clicked
I
want to run
the code otherwise I exit the sub.

Any ideas on how to do this?

Thanks.


















--

Dave Peterson