Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
magic cell
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
magic cell
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ... |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ... |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ... |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ... |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
magic cell
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
More XL magic | Excel Programming | |||
need worksheet tab magic | Excel Programming | |||
IDE add-on VB Magic? | Excel Discussion (Misc queries) | |||
Dates and VBA Magic! | Excel Programming | |||
Magic Cells | Excel Discussion (Misc queries) |