asked on

Excel 2007 Protected worksheet with a Calendar object linked to a cell

Hello experts!

Thanking you in advance for your help and thanking everyone again for all their help in the past!!!!!!!

I have an Excel 2007 worksheet that is protected that I have inserted a Calendar object (v12.0) and that calendar is linked via the properties to a cel (A10). That cell is UNlocked so that when the calendar updates by the clerk the cell changes.

HOWEVER,
The date that the calendar is defaulting to is the date that I created and protected the sheet. After a month or so this is a pain to have to pick the right month and eventually year so that the date is right.

Is there a way in either the Calendar properties or via VB to have that Calendar object default to Now() or Today(). I tried typing (Now) and Today() in the Calendar's properties but it just reverts back to the originally created date.

SiddharthRout

You may use these two events

Private Sub Worksheet_Activate()
    Calendar1.Value = Date
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Calendar1.Value = Date
End Sub

Open in new window

Sid

wlwebb

ASKER

Sid,
Added that to the "This Workbook" General Worksheet_Activate
Didn't work. Will I have to change any properties settings in the existing Calendar object?

Also I have a
Private Sub Workbook_Open() that performs some instruction you helped me with the other day. That Workbook_Open copies data values from a second wb when the workbooks open. Could that be preventing this Worksheet_Activate from doing its job?

SiddharthRout

>>>Added that to the "This Workbook" General

No it has to go into the respective sheet code area and not the workbook area. See snapshot.

Sid
Untitled.jpg

wlwebb

ASKER

Deleted old setting. Copied and pasted that exactly as shown to the VB for that specific worksheet. Still doesn't work

So I went to the Calendar object properties and deleted the date that shows up there. Now when the workbook opens (which opens to that worksheet) the calendar is there but no date is selected but the default month showing is the original one when I first created this project and inserted the calendar object (2 months ago)

SiddharthRout

Can you upload your workbook?

Sid

wlwebb

ASKER

It is an Excel 2007 file. Will that be ok?

SiddharthRout

Yes.

Sid

wlwebb

ASKER

Here it is. I stripped this from a larger project and it is only the one sheet so all the other controls etc won't work test-calendar.xlsm

ASKER CERTIFIED SOLUTION

SiddharthRout