Solved

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

Posted on 2011-03-09
12
375 Views
Last Modified: 2012-05-11
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.

0
Comment
Question by:wlwebb
  • 6
  • 6
12 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35092641
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
0
 

Author Comment

by:wlwebb
ID: 35092750
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?
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35092762
>>>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
0
 

Author Comment

by:wlwebb
ID: 35092830
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)
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35092849
Can you upload your workbook?

Sid
0
 

Author Comment

by:wlwebb
ID: 35092889
It is an Excel 2007 file.  Will that be ok?
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35092895
Yes.

Sid
0
 

Author Comment

by:wlwebb
ID: 35093007
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
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 500 total points
ID: 35093025
Try this

Sid
Test-calendar.xlsm
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35093197
Did it work? I tested it and it works :)

Sid
0
 

Author Comment

by:wlwebb
ID: 35093210
Kept getting Down for Maintainenance so I left office.  I'll try in a few hours when I get back
0
 

Author Closing Comment

by:wlwebb
ID: 35094971
Worked!!!  Had to add it to the Workbook_Open() that you helped me with a few days ago.

Thanks again Sid!!!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now