Solved

Excel VBA - How to Control Calendar Object from Module

Posted on 2011-02-25
5
378 Views
Last Modified: 2012-05-11
How can I control a calendar from a module (vs. worksheet code)?

I want to toggle a calender property     .visible = true/false from a module.

script:        Calendar1.visible=true        

works fine in the worksheet, but bugs out at the module level.
0
Comment
Question by:AndresHernando
[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
  • 2
5 Comments
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 34993675
An excel Calendar object is a Shape.

The below code provides two routines - one to make visible, and one to make invisible.  The assumption is there's a calendar called "Calendar1" in Sheet1.  This can be placed in a public module.

Sub CalendarVisible()

    Sheets("Sheet1").Shapes("Calendar1").Visible = True

End Sub
Sub CalendarInvisible()

    Sheets("Sheet1").Shapes("Calendar1").Visible = False
    
End Sub

Open in new window


Also, see attached file.

Enjoy!

Dave
CalendarVisible-r1.xlsm
0
 

Author Closing Comment

by:AndresHernando
ID: 34993716
Ah...  I needed to reference the worksheet where the object resides.

Thanks!!!!   --Andres
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34993723
Andres, when in doubt, turn on your macro recorder and then change properties of a shape (e.g., Visible to true/false) and the recorder will show you the way!

Dave
0

Featured Post

Technology Partners: 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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

739 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