On Apr 29, 11:15 am, msinclai...@gmail.com wrote:
> On Apr 29, 5:11 am, john <j...@discussions.microsoft.com> wrote:
>
>
>
> > 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
> > --
> > jb
>
> > "msinclai...@gmail.com" wrote:
> > > Hi, I am having some trouble getting the date from thepop-upcalendar
> > > to populate into my worksheet.
>
> > > Sheet1 contains this code:
>
> > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > > 'This subroutine willpop-upthecalendarwhen 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
> > > thecalendarform)
>
> > > Module1 contains this code:
>
> > > Sub OpenCalendar()
> > > frmCalendar.Show
> > > End Sub
>
> > > Any help getting thecalendardate into my worksheet would be
> > > appreciated.
>
> > > Also I would like to be able to control where thecalendarpop-ups on
> > > the worksheet. Can that be done?
>
> > > Thanks,
> > > Mike
>
> 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
Similar Threads
