show/hide calendar control

Try this.

Private Sub UserForm_Activate()
Me.Calendar1.Visible = False
End Sub

Replace "Calendar1" with the name of your calendar control.

Jerid

nope, that just makes the calendar invisible. this control is not in a userform it is in a spreadsheet. i need the calendar to pop-up when the "linked cell" is activated .

Here is an example of the Worksheet_SelectionChange Event. This code should go in the Spreadsheet Module.

I hope it helps.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Select Case Target.Address
Case "$A$1"
'Shows your calendar
CalendarForm.Show
Case "$B$1"
'Displays a message box
MsgBox "You selected cell B1"
Case "$C$1"
'Changes the sheet selection
Application.Sheets("Sheet2").Activate
Case "$D$1"
'Put a date stamp in the cell
Target.Value = Date
End Select
End Sub

Jerid

I am not quite sure what you mean. can you explain?

First you would set the calendar controls visible property to False, then place this code in that sheets code module.

This code will unhide your calendar control when the user clicks a certain cell. (A1 in this case)

Isn’t this what your original question was asking -

"i have created a form with calendar controls. i would like to have them hidden when the cells to which they are linked are inactive. is this possible? "

(Simplified Version)
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'If the user clicks cell A1 then show the calendar.
If Target.Address = "$A$1" Then
'Shows your calendar
CalendarForm.Show
End if
End Sub

Jerid

That's not exactly what I need though. The calendar is not a calendar form its a control in a worksheet. If i put in the code you suggest the excel looks for a calendar form which I do not use. Do i have to create it? Or is there a way to modify the control?

Well if it's a control then you should be able to use the controls name .visible = True or False

Example
Calendar1.Visible = True
Calendar1.Visible = False

What version of Excel are you using? I use 97 here and it doesn't let me put a calendar control on a sheet, that's why I assumed that you were using a form.

Jerid

That's not exactly what I need though. The calendar is not a calendar form its a control in a worksheet. If i put in the code you suggest the excel looks for a calendar form which I do not use. Do i have to create it? Or is there a way to modify the control? : First you would set the calendar controls visible property to False, then place this code in that sheets code module. : This code will unhide your calendar control when the user clicks a certain cell. (A1 in this case) : Isn’t this what your original question was asking - :

I am using excel 2000 and you can put calendar control onto the sheet, however, when i select visible - false the calendar disappeares alltogether. logically, it should pop-up when the linked cell is selected. but for some reason that does not happen...

Well if it's a control then you should be able to use the controls name .visible = True or False Example

Thats the funny thing about computers, if you don't tell them to do something they wont. The code I sent before will help with that part, You need to program that when the user click on the cell change the visible property to true.

Good Luck. I am using excel 2000 and you can put calendar control onto the sheet, however, when i select visible - false the calendar disappeares alltogether. logically, it should pop-up when the linked cell is selected. but for some reason that does not happen... Well if it's a control then you should be able to use the controls name .visible = True or False : Example

Jerid,
ok, that does half the job; i need to hide the calendar after the user is done selecting the date... what am I doing wrong? (I am sorry I am being so dense... I've never used MVB before...)

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'If the user clicks cell A1 then show the calendar.
If Target.Address = "$A$1" Then
'Shows your calendar
Calendar1.Visible = True
'Put a date stamp in the cell
Target.Value = Date
'Hides your calendar
Calendar1.Visible = False
End If
End Sub

The calendar blinks now... Thats the funny thing about computers, if you don't tell them to do something they wont. The code I sent before will help with that part, You need to program that when the user click on the cell change the visible property to true. Good Luck. : I am using excel 2000 and you can put calendar control onto the sheet, however, when i select visible - false the calendar disappeares alltogether. logically, it should pop-up when the linked cell is selected. but for some reason that does not happen... : Well if it's a control then you should be able to use the controls name .visible = True or False

Jerid,
ok, that does half the job; i need to hide the calendar after the user is done selecting the date... what am I doing wrong? (I am sorry I am being so dense... I've never used MVB before...)

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'If the user clicks cell A1 then show the calendar.
If Target.Address = "$A$1" Then
'Shows your calendar
Calendar1.Visible = True
'Put a date stamp in the cell
Target.Value = Date
'Hides your calendar
Calendar1.Visible = False
End If
End Sub

The calendar blinks now... Thats the funny thing about computers, if you don't tell them to do something they wont. The code I sent before will help with that part, You need to program that when the user click on the cell change the visible property to true. Good Luck. : I am using excel 2000 and you can put calendar control onto the sheet, however, when i select visible - false the calendar disappeares alltogether. logically, it should pop-up when the linked cell is selected. but for some reason that does not happen... : Well if it's a control then you should be able to use the controls name .visible = True or False

OK, what you have so far is an Event that shows the calendar (Calendar1.visible = true) when the user selects a cell.

Now what you need is an event that hides the calendar after the user selects a date.

Something like this
Private Sub Calendar1_AfterUpdate()
Calendar1.Visible = False
End Sub

What do you think of this?
(it requires an additional object, but works.. i think)

Private Sub Calendar1_Click()
Calendar1.Visible = True
End Sub

Private Sub Calendar1_LostFocus()
Calendar1.Visible = False
End Sub

Private Sub CommandButton1_Click()
Calendar1.Visible = True
End Sub

Well that's all that matters is that it works.

Can I ask what part of the world you’re in.

I'm in Pennsylvania, USA.

Good Luck.

Jersey Girl in the HOUSE:) Work in Manhattan/Live in Central NJ:)