VBA Code for Calendar Date Button

[Kaos_King]

Hi All

Could anyone help please I have created a calendar on sheet1 using ActiveX controls. I need code that when clicking on date button takes you to a cell location on another sheet.

Thanks for any help

[GTO]

Attach the file as you have it so far; preferably in .xls format.

[Kaos_King]

Hi GTO sorry at the moment I only have the calendar on Sheet 1. Sheet 2 is blank at the moment apart from a column labeled "Date". I need the code to basically read the date button clicked in the calendar and navigate to the cell location associated to that date on Sheet 2.

[Tinbendr]

[VBA]Private Sub Calendar1_Click()
Dim LastRow As Long
Dim Rng As Range
'Rename as required
With Worksheets(2)

LastRow = .Cells(.Rows.Count, ("A")).End(xlUp).Row

'Rename range as required.
Set Rng = .Range("A2:A" & LastRow).Find(Calendar1.Value)
If Not Rng Is Nothing Then Rng.Select

End With

End Sub[/VBA]

[GTO]

Hi GTO sorry at the moment I only have the calendar on Sheet 1. Sheet 2 is blank at the moment apart from a column labeled "Date". I need the code to basically read the date button clicked in the calendar and navigate to the cell location associated to that date on Sheet 2.

Sorry, I read your first post too quickly. I am in 2010 currently, so do not have access to a calendar control (this control is no longer included, so you may wish to think of an alternative)

Anyways, with the calendar on another sheet than the range (the cell) we want to jump to, you may want to try a slight amend to David's suggestion.

[VBA] 'Rename range as required.
Set Rng = .Range("A2:A" & LastRow).Find(25)
If Not Rng Is Nothing Then Application.Goto Rng
[/VBA]

Hope that helps,

Mark

[Kaos_King]

Thank you both for your replies. However may I ask either one of you the please explain this code to so as I can work out what to change to make it work in my spread sheet.

Thanks

[GTO]

Hi GTO sorry at the moment I only have the calendar on Sheet 1. Sheet 2 is blank at the moment apart from a column labeled "Date". I need the code to basically read the date button clicked in the calendar and navigate to the cell location associated to that date on Sheet 2.

Hi Kaos,

I think we are back to "attach the file as you have it...", as finding dates can be a PITA. If your file has sensitive data, you can scrub it, but we need to see the calendar and the "dates" being looked for - as to whether these are actually dates or strings, placed by entry or formula... stuff like that.

Mark

[Kaos_King]

Hi GTO thanks for the reply. Attached is a simplified version of the spread sheet. Basically what I want to di is when clicking on a calendar date on shee1 it takes me/ jumps me to a location on sheet2. So in the sample I sent if you click june 1st in with take you to sheet2 H13.


Thanks

[GTO]

As the calendar control is kaput in 2010 and you attached a .xlsx file, I am makin a fairly blind stab at your having 2007? I would need to wait until a can open the file at work (2010) to answer. No worries, I must hit the rack anyways and you'll probably have a better answer in the meantime :-)

[snb]

[VBA]Private Sub Calendar1_Click()
Sheet2.Cells.Find(Day(Calendar1.Value), , xlFormulas, xlWhole).Select
End Sub[/VBA]

[Kaos_King]

Hi SNB thanks for the reply however I have no idea how that works. Could I ask you to explain it please so as I can get it to work in my spread sheet

[Tinbendr]

Interesting puzzle you have there.

Can you explain what's going on with the calendar on sheet 2? Seems like you're recreating the calendar? Will you have more than one month on sheet2?

[Kaos_King]

Tinbendr the calendar on sheet2 which is actually called 2012 has data and information recorded against the date. The calandar on sheet1 "Date" is just a fast way for the user to jump to the correct location on sheet2.

Yes there is 12 months per sheet and 5 years so 5 sheets.

[Tinbendr]

Yes there is 12 months per sheet and 5 years so 5 sheets.

OIC. That complicates things. We can work through it, but it's gonna take a few more steps.

I'm going to assume that the other months will follow the same format? You will have Jan through Dec on 2012. The same for other years. Will the months be static? You're not going to delete months off the sheet?

Latter years? 2007-2011, or future years? 2012-2017? Or combination of both? 2010-2015?

[CodeNinja]

Kaos King,
This code might need a little work... doesn't account for year and for missing weekend numbers etc... but it is a good start for you. If you need it more complete, I can do that for you, but I always think you learn more doing a little bit of it yourself....


[vba]
Private Sub Calendar1_Click()
Dim dt As Date
Dim iDay As Integer
Dim iWeekday As Integer
Dim iMonth As Integer
Dim rng As Range
Dim iRow As Integer
Dim rng2 As Range

dt = Calendar1.Value


iDay = Day(dt)
iWeekday = Weekday(dt)
iMonth = Month(dt)
Sheet2.Activate
Set rng = Sheet2.Cells
Set rng2 = rng.Find(What:=MonthName(iMonth), after:=rng.Cells(1, 1), Lookat:=xlWhole)
iRow = rng2.Row
Set rng = Sheet2.Range("A" & iRow & ":G" & iRow + 40)
Set rng2 = rng.Find(What:=iDay, after:=rng.Cells(rng.Cells.Count), Lookat:=xlWhole)

rng2.Select

End Sub

[/vba]

[Kaos_King]

Tinbendr to answer your question no there will be no months deleated. The moths and dates for each year will be the same as the recognised official calendar. So meaning that June 1st 2012 is a Friday however June 1st 2013 is a Saturday. The workbook at this stage will be from 2012 - 2017.

Is my understanding correct that this will not work in Excel 2010?

[CodeNinja]

Date picker tool is not supported in excel 2010. As a result, the picker will not show up in versions later than 2007 and your program will not work. I posted a user form that will do a very similar job here:

http://www.vbaexpress.com/forum/showthread.php?t=42558

I am hoping to make this into an activeX control, but I have no access to vb.net and cannot find a way at the moment to do it purely in excel, so its the best I can do for the moment.

Good luck.