Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Jump to cell based on cell results created by calendar control too
I have a calendar tool linked to cell A1. I have a table in B1:C255, where
column B lists the dates that can be chosen by the calendar and column C lists the cells I would like them to be directed. In A2 I have a vlookup to find the cell destination based on their calendar choice. So far, I can get excel to do the vlookup and find the cell I want them to go to, it's just a matter of getting the code to have excel automatically "jump" to the cell that results from that vlookup. Thanks for any help you can give :) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Jump to cell based on cell results created by calendar control too
Hi JB,
It appears that the populating of cell A1 by the calendar does not trigger a change event, so you should attach the code to the calendar control. In the following example you may need to change the calendar control's name. I am assuming that the vlookup is in cell A2 and the value that is returned is a cell address. Private Sub Calendar1_Click() Range(Range("A2")).Select End Sub You can add this code you switching to design mode and then right clicking the calendar and choosing View Code. -- Cheers, Shane Devenshire "JB" wrote: I have a calendar tool linked to cell A1. I have a table in B1:C255, where column B lists the dates that can be chosen by the calendar and column C lists the cells I would like them to be directed. In A2 I have a vlookup to find the cell destination based on their calendar choice. So far, I can get excel to do the vlookup and find the cell I want them to go to, it's just a matter of getting the code to have excel automatically "jump" to the cell that results from that vlookup. Thanks for any help you can give :) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Jump to cell based on cell results created by calendar control
Thanks Shane, I tried it, but it didn't work for me :(
The code I found below would work for me if i could just update it to allow A2 to be the result of a formula (right now it only works if someone enters the data directly into A2). Sorry to be a pain, but do you think you could help me update this code? Private Sub Worksheet_Change(ByVal Target As Range) places = Array("J3","n3","r3","v3","z3","ad3","ah3","al3"," ap3","at3","ax3","bb3","bf3","bj3","bn3","br3","bv 3","bz3","cd3","ch3","cl3","cp3","ct3","cx3","db3" ,"df3","dj3","dn3","dr3","dv3","dz3","ed3","eh3"," el3","ep3","et3","ex3","fb3","ff3","fj3","fn3","fr 3","fv3","fz3","gd3","gh3","gl3","gp3","gt3","gx3" ,"hb3","hf3","hj3") If Intersect(Target, Range("A2")) Is Nothing Then Exit Sub End If i = Range("A2").Value Range(places(i - 1)).Select End Sub Eternally grateful, Julia "ShaneDevenshire" wrote: Hi JB, It appears that the populating of cell A1 by the calendar does not trigger a change event, so you should attach the code to the calendar control. In the following example you may need to change the calendar control's name. I am assuming that the vlookup is in cell A2 and the value that is returned is a cell address. Private Sub Calendar1_Click() Range(Range("A2")).Select End Sub You can add this code you switching to design mode and then right clicking the calendar and choosing View Code. -- Cheers, Shane Devenshire "JB" wrote: I have a calendar tool linked to cell A1. I have a table in B1:C255, where column B lists the dates that can be chosen by the calendar and column C lists the cells I would like them to be directed. In A2 I have a vlookup to find the cell destination based on their calendar choice. So far, I can get excel to do the vlookup and find the cell I want them to go to, it's just a matter of getting the code to have excel automatically "jump" to the cell that results from that vlookup. Thanks for any help you can give :) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Jump to cell based on cell results created by calendar control
Hi JB,
I'm having trouble understanding the line that reads i = Range("A2").Value You said A2 was the result of a VLOOKUP which returned a cell address? Then Value of A2 would always be 0. What exactly is appearing in cell A2? -- Cheers, Shane Devenshire "JB" wrote: Thanks Shane, I tried it, but it didn't work for me :( The code I found below would work for me if i could just update it to allow A2 to be the result of a formula (right now it only works if someone enters the data directly into A2). Sorry to be a pain, but do you think you could help me update this code? Private Sub Worksheet_Change(ByVal Target As Range) places = Array("J3","n3","r3","v3","z3","ad3","ah3","al3"," ap3","at3","ax3","bb3","bf3","bj3","bn3","br3","bv 3","bz3","cd3","ch3","cl3","cp3","ct3","cx3","db3" ,"df3","dj3","dn3","dr3","dv3","dz3","ed3","eh3"," el3","ep3","et3","ex3","fb3","ff3","fj3","fn3","fr 3","fv3","fz3","gd3","gh3","gl3","gp3","gt3","gx3" ,"hb3","hf3","hj3") If Intersect(Target, Range("A2")) Is Nothing Then Exit Sub End If i = Range("A2").Value Range(places(i - 1)).Select End Sub Eternally grateful, Julia "ShaneDevenshire" wrote: Hi JB, It appears that the populating of cell A1 by the calendar does not trigger a change event, so you should attach the code to the calendar control. In the following example you may need to change the calendar control's name. I am assuming that the vlookup is in cell A2 and the value that is returned is a cell address. Private Sub Calendar1_Click() Range(Range("A2")).Select End Sub You can add this code you switching to design mode and then right clicking the calendar and choosing View Code. -- Cheers, Shane Devenshire "JB" wrote: I have a calendar tool linked to cell A1. I have a table in B1:C255, where column B lists the dates that can be chosen by the calendar and column C lists the cells I would like them to be directed. In A2 I have a vlookup to find the cell destination based on their calendar choice. So far, I can get excel to do the vlookup and find the cell I want them to go to, it's just a matter of getting the code to have excel automatically "jump" to the cell that results from that vlookup. Thanks for any help you can give :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Making a Cell turn color based on results in another cell | Excel Discussion (Misc queries) | |||
How to insert date using a pop up calendar control in a cell i | Excel Discussion (Misc queries) | |||
Calendar control on cell enter? | Excel Worksheet Functions | |||
Formatting one cell based on results of another cell | Excel Discussion (Misc queries) | |||
How to insert date using a pop up calendar control in a cell i | Excel Discussion (Misc queries) |