[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2011-03-09
12
Medium Priority
?
381 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

649 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