Solved

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

Posted on 2011-03-09
12
374 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

760 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

24 Experts available now in Live!

Get 1:1 Help Now