VBA Calendar Control without Control

  • Hi Paul and welcome to the board.


    Wow, your kind words were quite uplifting. It's nice to be recognized by others especially your peers. However, I cannot take complete credit for this work. I, like you have had nothing but troubles publishing apps using the activeX date pickers. After exhausting every avenue to make them work I decided to build my own, which has been working for me very nicely. However, it is because of others in this forum that this Calendar is what it is. Meaning others like Ivan, Dennis and others did contribute and make it more then I could have on my own.


    Anyway…thanks for the kind words.


    Take care and the very best regards from Texas.


    Phil

  • Hi Phil:


    I need to insert dates in "merged" cells. I had difficulty making it work with your example.


    Can you pl. comment.


    Thanks for an excellent example which makes you not to depend on the mscal.ocx.


    Cheers
    Gops

  • Hi Phil:


    Can your calendarFrm work on sytems without referencing the .ocx file? If so, how.


    I have imported your CalendarFrm into my custom Workbook. It works fine on my computer. However, it does not work on another computer which has no .ocx file.

  • Gops
    Ivan has answered you . There are no controls needed that are not already available .This calendar is driven by VBA.If it is not working on another computer check that Macros are enabled.Also, when designing a spreadsheet I would suggest that you avoid merged cells as this can lead to complications later.
    We can't tell you how to adapt Phil's calendar form if we don't know what you expect it to do.


  • I just tried Phil's file and merged a few cells.... the calendar worked fine and placed the selected date into the merged cell(s).... so I'm not sure what your problem might be.


    You should be able to get the calendar to work in any file by simple exporting the module & associated VBA code & subsequently importing it to your file that you want to use it in.


    Hope this helps,



    Will

  • WillR and Roy-UK:


    Thanks for the response.
    The merged cell problem is resolved now.


    RoyUK:


    Here is what I need:


    I need to import Phil's calendar and the associated code into my workbook. Then I need to send my workbook to other users. They need to be able to open My workbook and insert a date in the required cell, even if they don't have the .ocx file residnet. Yes the macros are enabled.


    All this knowledge sharing is much appreciated.

  • Phil's calendar posts the date selected by clicking on the date button to the active cell. If you have only one cell requiring a date from the calendar you have two options:
    1) Change Activecell in all the Date buttons to your cell's date address.
    2)Simply force the Active Cell to be your required cell when calling the userform like this


    Private Sub Show_Cal()
    'Edit to your required cell
    Range("A1").Select
    'puts instruction on form
    CalendarFrm.HelpLabel.Caption = "Select day to insert into form."
    'loads the form
    CalendarFrm.Show
    End Sub


    Please note the users will not need to have the calendar ocx it is independent of that.

  • Hi Roy-UK:


    The user need to insert date in three different cells by double clicking the required cell and not by clicking the date button.


    This is what I have done: Pl. tell me if it is OK.


    I have imported Phil's "CalendarFrm" only into my VBA project. Then added the following code into :this Workbook" (reason: the user needs to insert dte in any given sheet)
    Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    If Target.Address = "$F$3:$G$3" Then

    frmReminder.Show
    End If



    If Target.Address = "$J$3:$K$3" Then
    CalendarFrm.Show
    End If



    If Target.Address = "$E$72:$G$73" Then
    CalendarFrm.Show

    End If

    End Sub

  • Correction:
    Hi Roy-UK:


    The user need to insert date in three different cells by double clicking the required cell and not by clicking the date button.


    This is what I have done: Pl. tell me if it is OK.


    I have imported Phil's "CalendarFrm" only into my VBA project. Then added the following code into "this Workbook" (reason: the user needs to insert dte in any given sheet)
    Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    If Target.Address = "$F$3:$G$3" Then


    CalendarFrm.show
    End If




    If Target.Address = "$J$3:$K$3" Then
    CalendarFrm.Show
    End If




    If Target.Address = "$E$72:$G$73" Then
    CalendarFrm.Show


    End If


    End Sub


    Sorry about the mistake.

  • WillR:


    Yes, Phil's code works fine.


    Yes F3:G3 are merged. If I use "$F$3:$G$3", it works OK.


    I just looking for answers on how to adapt to my need (see below).


    Thanks anyways.

  • Well what's your need..... The calendar coupled with the code you speccified now puts a date into the merged cells you specified when those merged cells are double-clicked.


    That's all you have said you wanted to do so far, other than a rather vague "How do I adapt it to my need"....


    So explain your needs in detail & I'm sure we can help you buddy,


    :) Will

  • Hi WillR:


    I need to send a custumized workbook to several users.
    The user need to insert date in three different cells (in each sheet) by double clicking the required cell and not by clicking the date button.


    This is what I have done: Pl. tell me if it is OK.


    I have imported Phil's "CalendarFrm" into my VBA project. Then added the following code into "this Workbook" (reason: the user can insert date in any sheet)
    Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    If Target.Address = "$F$3:$G$3" Then


    CalendarFrm.show
    End If




    If Target.Address = "$J$3:$K$3" Then
    CalendarFrm.Show
    End If




    If Target.Address = "$E$72:$G$73" Then
    CalendarFrm.Show


    End If


    End Sub


    Is there anything else I need to do when sending to other computers?.


    Thanks for all the help. Sorry for the vagueness in my earlier message!!!

  • Sorry for the late response for I have been traveling and unable to participate.


    It does however appear that you all were able to resolve this particularly challenging post. Thanks to all for all the help.


    One thing that I will add is my distain for using a double click event handler to call up the calendar. My preference is to have it come up automatically when a cell that is formatted as a date is selected.


    I do this by adding the following event handler in the sheet module itself.


    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.NumberFormat = "m/d/yy" Then CalendarFrm.Show
    End Sub


    And as was pointed by our seasoned veterans…this tool was designed to work with or without the cells being merged and ocx is not an issue for this calendar is totally VBA driven and completely devoid of any connection to any activeX control or any type of ocx.


    Kindest possible regards to all and thanks again for the help.


    Phil

  • I was searching for a calendar solution and came across this thread. Looks great but the attachments have expired.


    Can anyone help me find the attachment of Phil's final version of the calendar?


    Thanks,
    Eric