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 :)

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 :)

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 :)

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 :)