Can't get pop-up calendar date passed into worksheet

[msinclair99]

Hi, I am having some trouble getting the date from the pop-up calendar
to populate into my worksheet.

Sheet1 contains this code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'This subroutine will pop-up the calendar when the cell is selected by
mouse, tab, enter or arrow keys
If Target.Address = "$B$9" Then
Call OpenCalendar
Range("B9").Value = frmCalendar.Value
ElseIf Target.Address = "$B$12" Then
Call OpenCalendar
Range("B12").Value = frmCalendar.Value
End If
End Sub

I get "Method or data member not found" on the line above starting
with Range ("B9")

The forms module contains a user form named frmCalendar (no code, just
the calendar form)

Module1 contains this code:

Sub OpenCalendar()
frmCalendar.Show
End Sub

Any help getting the calendar date into my worksheet would be
appreciated.

Also I would like to be able to control where the calendar pop-ups on
the worksheet. Can that be done?

Thanks,
Mike

 

[john]

you could just use the calendar control to add dates to your worksheet.
Have a look at Ron de Bruin's site for more info.
http://www.rondebruin.nl/calendar.htm

 

[msinclair99]

you could just use thecalendarcontrol to add dates to your worksheet.
Have a look at Ron de Bruin's site for more info.http://www.rondebruin.nl/calendar.htm

Thanks for your reply. I have tried using the control and for some
reason it is unreadable. I tried changing the fonts,etc but the day
#'s are too small to read. Plus it takes up a lot of space on the
form. So what I am trying to get to work is when the user clicks on a
cell, the calendar opens.

Any help would be appreciated. Mike

 

[msinclair99]

Thanks for your reply. I have tried using the control and for some
reason it is unreadable. I tried changing the fonts,etc but the day
#'s are too small to read. Plus it takes up a lot of space on the
form. So what I am trying to get to work is when the user clicks on a
cell, thecalendaropens.

Any help would be appreciated. Mike

After digging around the posts for the 100th time I finally figured it
out. My problem was a combination of the naming of my variables and
having the code in the correct places. Here is what is working for me
now:

Sheet1 contains this code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'This subroutine will pop-up the calendar when the cell is selected by
mouse, tab, enter or arrow keys
If Target.Address = "$B$9" Or Target.Address = "$B$12" Then
Call OpenCalendar
End If
End Sub

Sub OpenCalendar()
' Displays the UserForm and calendar
' Shortcuts should be made to this procedure
frmCalendar.Show
End Sub

The userform code contains this code:

Private Sub cmdClose_Click()
'This subroutine closes the calendar when the user presses ESC
Unload Me
End Sub

Private Sub frmCalendar_Click()
'This subroutine places the date in the active cell then closes the
calendar
ActiveCell.Value = frmCalendar.Value
Unload Me
End Sub

Private Sub UserForm_Initialize()
'This subroutine checks if there is a date in the active cell first
'If there is a date, that date is used by the calendar, otherwise it
uses the current date
If IsDate(ActiveCell.Value) Then
frmCalendar.Value = DateValue(ActiveCell.Value)
Else
frmCalendar.Value = Date
End If
End Sub

There is no code needed in Module1.

Thanks all. Mike