Macro to start when cell selected

[Guest]

I have the following macro that I want to open when the user clicks cell b2,
which launches the calendar, then after they have selected the date and move
out of that cell the calendar closes.

Any help would be great

Here is the macro I want to start,

Sub OpenCalendar()

End Sub

 

[Ron de Bruin]

Hi JackR

You can use the Worksheet_SelectionChange event

If Not Application.Intersect(Range("B2"), Target) Is Nothing Then

Tips for a calendar
http://www.rondebruin.nl/calendar.htm

 

[Guest]

Where would I place this line? I am very new to this.

 

[Ron de Bruin]

Hi

Copy this code in the Worksheet module:
Right click on the sheet tab and choose view code.
Paste the code in there and press Alt-Q to go back to Excel.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Range("B2"), Target) Is Nothing Then
Call OpenCalendar
End If
End Sub

When you select B2 the macro OpenCalendar will run in the normal module

 

[Guest]

Thank you so very much, your help is greatly appreciated

 

[Guest]

Everything works except that when the calendar pops up and I click on date
it will not enter the date. How can I e-mail you the actual spreadsheet so
you can look at it and see what I have done wrong??

 

[Ron de Bruin]

Don't know what you use, userform I think?

Have you try my code example Jack
http://www.rondebruin.nl/calendar.htm

 

[Guest]

I tried your code example, but I have 2 problems with it,

1. I need it to work with a sheet protected, which it would not do I got an
error message, is there a way to do this?

2. I need it to be a little smaller and not pop up right under the selected
cell, is there a way to move it?

If you can resolve these 2 issues your code is great.

 

[Ron de Bruin]

Unlock the Date cells before you protect the sheet

Or protect you sheet with code
Copy this in the thisworkbook module and save /close/reopen the workbook

Private Sub Workbook_Open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
End With
End Sub

2. I need it to be a little smaller

Yes
Right click the control and look in Format object

and not pop up right under the selected cell, is there a way to move it?

Change the code

Calendar1.Left = Target.Left + Target.Width - Calendar1.Width
Calendar1.Top = Target.Top + Target.Height