magic cell
 

Hi, Im a newbie. I encountered a code in this site and I tweaked it to
attain my goal but there is a limitation to this code and I want to modify it
further.

This is written in Sheet1:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = Range("H4").Address Then
DisplayFarCell_AA
End If
If Target.Address = Range("I4").Address Then
DisplayFarCell_AB
End If
End Sub

This is written in Module1:
Sub DisplayFarCell_AA()
Range("AA4").Select
Range("H4").Select
End Sub
Sub DisplayFarCell_AB()
Range("AB4").Select
Range("I4").Select
End Sub
The sheet has its €œfreeze pane€� at cell O7. When you select cell H4, cell
AA4 will automatically display at the right side of the screen, then the
cursor goes back to H4.
As you know, this is only limited to cell H4. My goal is to watch an active
cell, column per column. Instead of just watching €œH4€� to be selected, I want
to watch H4 to H555 and I4 to I555 and so on.
Quick explanation of the goal:
Watch columns, ranges H4:H555, I4:I555, J4:J555, K4:H555
If one cell in range H4:H555 is selected,
then display AA4 (column AA),
then go back to selected cell.
If one cell in range I4:I555 is selected,
then display AB4 (column AB),
then go back to selected cell.
If one cell in range J4:J555 is selected,
then display AC4 (column AC),
then go back to selected cell.
If one cell in range K4:H555 is selected,
then display AD4 (column AD),
then go back to selected cell.

Note: Instead of displaying a cell, I may want to display a chart. It would
then be like this:
If one cell in range H4:H555 is selected,
then display CHART1
then go back to selected cell.
If another cell is but still in range H4:H555
then still display CHART1
then go back to selected cell.
If another cell is but in another range -- I4:I555
then hide CHART1 then display CHART2
then go back to selected cell.

Thank you so much for your help. I am using excel 2007.

magic cell
 

To monitor a column use something like

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 8 Then DisplayFarCell_AA
End Sub



--

Regards,
Nigel




"omega" wrote in message
...
Hi, Im a newbie. I encountered a code in this site and I tweaked it to
attain my goal but there is a limitation to this code and I want to modify
it
further.

This is written in Sheet1:
If Target.Address = Range("H4").Address Then
DisplayFarCell_AA
End If
If Target.Address = Range("I4").Address Then
DisplayFarCell_AB
End If
End Sub

This is written in Module1:
Sub DisplayFarCell_AA()
Range("AA4").Select
Range("H4").Select
End Sub
Sub DisplayFarCell_AB()
Range("AB4").Select
Range("I4").Select
End Sub
The sheet has its €œfreeze pane€� at cell O7. When you select cell H4, cell
AA4 will automatically display at the right side of the screen, then the
cursor goes back to H4.
As you know, this is only limited to cell H4. My goal is to watch an
active
cell, column per column. Instead of just watching €œH4€� to be selected, I
want
to watch H4 to H555 and I4 to I555 and so on.
Quick explanation of the goal:
Watch columns, ranges H4:H555, I4:I555, J4:J555, K4:H555
If one cell in range H4:H555 is selected,
then display AA4 (column AA),
then go back to selected cell.
If one cell in range I4:I555 is selected,
then display AB4 (column AB),
then go back to selected cell.
If one cell in range J4:J555 is selected,
then display AC4 (column AC),
then go back to selected cell.
If one cell in range K4:H555 is selected,
then display AD4 (column AD),
then go back to selected cell.

Note: Instead of displaying a cell, I may want to display a chart. It
would
then be like this:
If one cell in range H4:H555 is selected,
then display CHART1
then go back to selected cell.
If another cell is but still in range H4:H555
then still display CHART1
then go back to selected cell.
If another cell is but in another range -- I4:I555
then hide CHART1 then display CHART2
then go back to selected cell.

Thank you so much for your help. I am using excel 2007.

magic cell
 

Nigel,

It works! Thank you very much for your help!

Omega

"Nigel" wrote:

To monitor a column use something like

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 8 Then DisplayFarCell_AA
End Sub



--

Regards,
Nigel

magic cell
 

I can't quite figure out what you want but if you select row 4 in any column
it will select that column offset 21 from row 4 to row 555. So, selecting
h4 will select ac4:ac555. If you want this to happen from any cell in col H,
just comment out or delete the if row line

Private Sub Worksheet_SelectionChange _
(ByVal Target As Range)
If Target.Row < 4 Then Exit Sub
Application.EnableEvents = False
Cells(Target.Row, Target.Column). _
Offset(, 21).Resize(552).Select

MsgBox "Click OK to go back to " & Target.Address
Target.Select

Application.EnableEvents = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"omega" wrote in message
...
Hi, Im a newbie. I encountered a code in this site and I tweaked it to
attain my goal but there is a limitation to this code and I want to modify
it
further.

This is written in Sheet1:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = Range("H4").Address Then
DisplayFarCell_AA
End If
If Target.Address = Range("I4").Address Then
DisplayFarCell_AB
End If
End Sub

This is written in Module1:
Sub DisplayFarCell_AA()
Range("AA4").Select
Range("H4").Select
End Sub
Sub DisplayFarCell_AB()
Range("AB4").Select
Range("I4").Select
End Sub
The sheet has its €œfreeze pane€� at cell O7. When you select cell H4, cell
AA4 will automatically display at the right side of the screen, then the
cursor goes back to H4.
As you know, this is only limited to cell H4. My goal is to watch an
active
cell, column per column. Instead of just watching €œH4€� to be selected, I
want
to watch H4 to H555 and I4 to I555 and so on.
Quick explanation of the goal:
Watch columns, ranges H4:H555, I4:I555, J4:J555, K4:H555
If one cell in range H4:H555 is selected,
then display AA4 (column AA),
then go back to selected cell.
If one cell in range I4:I555 is selected,
then display AB4 (column AB),
then go back to selected cell.
If one cell in range J4:J555 is selected,
then display AC4 (column AC),
then go back to selected cell.
If one cell in range K4:H555 is selected,
then display AD4 (column AD),
then go back to selected cell.

Note: Instead of displaying a cell, I may want to display a chart. It
would
then be like this:
If one cell in range H4:H555 is selected,
then display CHART1
then go back to selected cell.
If another cell is but still in range H4:H555
then still display CHART1
then go back to selected cell.
If another cell is but in another range -- I4:I555
then hide CHART1 then display CHART2
then go back to selected cell.

Thank you so much for your help. I am using excel 2007.

magic cell
 

Don Guillett,

Wow! As per your advice, I edit out some lines and with some trial and
error, I have reached the desired result. Amazingly, just five lines of the
code you gave replaced my bulky codes before.

Below is the edited version of the code that works perfectly for my project:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
Cells(Target.Row, Target.Column).Offset(, 26).Select
Target.Select
Application.EnableEvents = True
End Sub

Thank you very much!

Omega

================================================== ====

"Don Guillett" wrote:

I can't quite figure out what you want but if you select row 4 in any column
it will select that column offset 21 from row 4 to row 555. So, selecting
h4 will select ac4:ac555. If you want this to happen from any cell in col H,
just comment out or delete the if row line

Private Sub Worksheet_SelectionChange _
(ByVal Target As Range)
If Target.Row < 4 Then Exit Sub
Application.EnableEvents = False
Cells(Target.Row, Target.Column). _
Offset(, 21).Resize(552).Select

MsgBox "Click OK to go back to " & Target.Address
Target.Select

Application.EnableEvents = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"omega" wrote in message
...

magic cell
 

Glad to help. I like to keep it simple

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"omega" wrote in message
...
Don Guillett,

Wow! As per your advice, I edit out some lines and with some trial and
error, I have reached the desired result. Amazingly, just five lines of
the
code you gave replaced my bulky codes before.

Below is the edited version of the code that works perfectly for my
project:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
Cells(Target.Row, Target.Column).Offset(, 26).Select
Target.Select
Application.EnableEvents = True
End Sub

Thank you very much!

Omega

================================================== ====

"Don Guillett" wrote:

I can't quite figure out what you want but if you select row 4 in any
column
it will select that column offset 21 from row 4 to row 555. So,
selecting
h4 will select ac4:ac555. If you want this to happen from any cell in col
H,
just comment out or delete the if row line

Private Sub Worksheet_SelectionChange _
(ByVal Target As Range)
If Target.Row < 4 Then Exit Sub
Application.EnableEvents = False
Cells(Target.Row, Target.Column). _
Offset(, 21).Resize(552).Select

MsgBox "Click OK to go back to " & Target.Address
Target.Select

Application.EnableEvents = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"omega" wrote in message
...

magic cell
 

What is it you think these 5 lines of code (actually, I only see 4 lines of
code) you posted are doing??? All I see them doing is re-selecting the cell
you just selected.

--
Rick (MVP - Excel)


"omega" wrote in message
...
Don Guillett,

Wow! As per your advice, I edit out some lines and with some trial and
error, I have reached the desired result. Amazingly, just five lines of
the
code you gave replaced my bulky codes before.

Below is the edited version of the code that works perfectly for my
project:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
Cells(Target.Row, Target.Column).Offset(, 26).Select
Target.Select
Application.EnableEvents = True
End Sub

Thank you very much!

Omega

================================================== ====

"Don Guillett" wrote:

I can't quite figure out what you want but if you select row 4 in any
column
it will select that column offset 21 from row 4 to row 555. So,
selecting
h4 will select ac4:ac555. If you want this to happen from any cell in col
H,
just comment out or delete the if row line

Private Sub Worksheet_SelectionChange _
(ByVal Target As Range)
If Target.Row < 4 Then Exit Sub
Application.EnableEvents = False
Cells(Target.Row, Target.Column). _
Offset(, 21).Resize(552).Select

MsgBox "Click OK to go back to " & Target.Address
Target.Select

Application.EnableEvents = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"omega" wrote in message
...

magic cell
 

Rick, I couldn't figure it out either. That's why I posted the way I did
Private Sub Worksheet_SelectionChange _
(ByVal Target As Range)
If Target.Row < 4 Then Exit Sub
Application.EnableEvents = False
Cells(Target.Row, Target.Column). _
Offset(, 21).Resize(552).Select

MsgBox "Click OK to go back to " & Target.Address
Target.Select

Application.EnableEvents = True
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rick Rothstein" wrote in message
...
What is it you think these 5 lines of code (actually, I only see 4 lines
of code) you posted are doing??? All I see them doing is re-selecting the
cell you just selected.

--
Rick (MVP - Excel)


"omega" wrote in message
...
Don Guillett,

Wow! As per your advice, I edit out some lines and with some trial and
error, I have reached the desired result. Amazingly, just five lines of
the
code you gave replaced my bulky codes before.

Below is the edited version of the code that works perfectly for my
project:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
Cells(Target.Row, Target.Column).Offset(, 26).Select
Target.Select
Application.EnableEvents = True
End Sub

Thank you very much!

Omega

================================================== ====

"Don Guillett" wrote:

I can't quite figure out what you want but if you select row 4 in any
column
it will select that column offset 21 from row 4 to row 555. So,
selecting
h4 will select ac4:ac555. If you want this to happen from any cell in
col H,
just comment out or delete the if row line

Private Sub Worksheet_SelectionChange _
(ByVal Target As Range)
If Target.Row < 4 Then Exit Sub
Application.EnableEvents = False
Cells(Target.Row, Target.Column). _
Offset(, 21).Resize(552).Select

MsgBox "Click OK to go back to " & Target.Address
Target.Select

Application.EnableEvents = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"omega" wrote in message
...

magic cell
 

Rick,

Its okay. The edited version of Dons code works well in my worksheet
because it has a €œfreeze pane€� at AE6. My goal is to display a group of
charts contained in the right side of the pane, column AE, when a cell in
column E (left side of the pane) is selected. The chart serves as a visual
guide to the user depending on the cell/column selected. Without the €œfreeze
pane€�, the code will not achieve anything. Furthermore, when the user selects
a cell in column F, at the left side of the pane, the code automatically
scrolls the right side of the pane and displays column AF where another group
of charts is present, then goes back to the previously selected cell at the
left side of the €œfreeze pane€�€¦. And so on€¦

This primitive way of displaying a guide chart for the user works fine with
me as of the meantime, until I will learn a way to pop-up a €œgroup of charts€�
when a cell in a column is selected.

By the way, is this possible? €¦popping up a group of charts when a cell is
selected?

Thank you,

Omega

===============================

"Rick Rothstein" wrote:

What is it you think these 5 lines of code (actually, I only see 4 lines of
code) you posted are doing??? All I see them doing is re-selecting the cell
you just selected.

--
Rick (MVP - Excel)

magic cell
 

ActiveSheet.Shapes("Chart 2").Visible = False

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"omega" wrote in message
...

Rick,

Its okay. The edited version of Dons code works well in my worksheet
because it has a €œfreeze pane€� at AE6. My goal is to display a group of
charts contained in the right side of the pane, column AE, when a cell in
column E (left side of the pane) is selected. The chart serves as a visual
guide to the user depending on the cell/column selected. Without the
€œfreeze
pane€�, the code will not achieve anything. Furthermore, when the user
selects
a cell in column F, at the left side of the pane, the code automatically
scrolls the right side of the pane and displays column AF where another
group
of charts is present, then goes back to the previously selected cell at
the
left side of the €œfreeze pane€�€¦. And so on€¦

This primitive way of displaying a guide chart for the user works fine
with
me as of the meantime, until I will learn a way to pop-up a €œgroup of
charts€�
when a cell in a column is selected.

By the way, is this possible? €¦popping up a group of charts when a cell is
selected?

Thank you,

Omega

===============================

"Rick Rothstein" wrote:

What is it you think these 5 lines of code (actually, I only see 4 lines
of
code) you posted are doing??? All I see them doing is re-selecting the
cell
you just selected.

--
Rick (MVP - Excel)