Solved

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

Posted on 2011-03-09
12
378 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

730 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