Problems when assigning value to MonthView control RRS feed

  • Question

  • Hello and Happy New Year everyone

    I need to develop an userform to add new data to or load existing data from an Excel worksheet, show it to the users, let them modify it and update the worksheet.

    I'm working with Excel 2010 but the users might be using either Excel 2010 or Excel 2007.

    Every not empty line of this worksheet has a column which contains a date (in dd/mm/yyyy format) the user is able to enter/modify through a MonthView control. Cells format is set accordingly. Date is mandatory, so no row exists without date.

    I'm encountering some problems when assigning a value to the MonthView control.

    In this specific case, I need to load in the userform data from the selected row.

    I've tried two possible ways:

    1) MonthView.Value = ActiveSheet.Range("C" & ActiveCell.Row).Value

    which leads to "Runtime Error '35778': Error during call to Windows MonthView control." This happens even if I add a Format call Format(ActiveSheet.Range("C" & ActiveCell.Row).Value, "dd/mm/yyyy")

    2) MonthView.MultiSelect = False
        MonthView.Day = Left(ActiveSheet.Range("C" & ActiveCell.Row).Value, 2)
        MonthView.Month = Mid(ActiveSheet.Range("C" & ActiveCell.Row).Value, 4, 2)
        MonthView.Year = Right(ActiveSheet.Range("C" & ActiveCell.Row).Value, 4)

    which leads to an error message on the second line saying I can't set the Day when MultiSelect is equal to true but the line right above it actually sets it to False.

    This issue looks to be happening in a random way, independently from the cell value. Usually if I save the workbook, close it and reopen it I'm able to go on working, even using the same line with same date value, but it might occur again after some time, sometimes it doesn't happen at all. I'd like to remove this randomic behaviour.

    Any hint about how to solve this issue will be highly appreciated.

    Thank you very much and happy new year again.

    Thanks and Regards,

    Tigre Bianca

    Tuesday, January 1, 2013 4:49 PM

All replies

  • The following works for me:

    MonthView1.Value = CDate("15/1/2013")

    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    • Proposed as answer by VBAToolsMVP, Editor Tuesday, January 1, 2013 10:09 PM
    • Marked as answer by Tigre Bianca Tuesday, January 1, 2013 11:08 PM
    • Unmarked as answer by Tigre Bianca Wednesday, January 2, 2013 2:17 AM
    • Unproposed as answer by Tigre Bianca Wednesday, January 2, 2013 2:29 AM
    Tuesday, January 1, 2013 9:11 PM
  • If you haven't date, you can convert or str as Rod wrote

    or direct assign value to control

    MonthView1.Value = Cells(ActiveCell.Row, "C")

    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Tuesday, January 1, 2013 10:09 PM
  • I have a date (the cell number format is Date category) and the direct assignment

    MonthView.Value = ActiveSheet.Range("C" & ActiveCell.Row).Value

    is working fine since I made this thread but it was sometimes working sometimes throwing runtime error 35778 when I started typing the post.

    I put CDate as suggested by Rod as additional safety, thanks for it.

    Tuesday, January 1, 2013 11:08 PM
  • Newest update

    My current code is:

    MonthView.Value = CDate(ActiveSheet.Range("C" & ActiveCell.Row).Value)

    and I just got the same "Runtime Error '35778': Error during call to Windows MonthView control."

    I stopped the sub execution and restarted it and it went ok.

    Before stopping the execution, I hovered my mouse on that line and the tooltip was correctly showing

    MonthView.Value = '28/12/2012'

    so I think it's not a format issue but something else. Any idea?

    Wednesday, January 2, 2013 2:29 AM
  • Try:

    MonthView.Value = CDate(ActiveSheet.Range("C" & ActiveCell.Row).TEXT)

    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Thursday, January 3, 2013 10:40 PM
  • Your example:
    MonthView.Value = CDate(ActiveSheet.Range("C" & ActiveCell.Row).Value)

    Should to work. You can have wrote data as text - not as data.

    For instance: if you wrote 28.12.2012 so this date is not date but string because cell text format.

    You can check it in worksheet by adding +1 to date cell like =C1+1.

    You should have Next day date. If you have err #ARG! that proof my assumptions.

    Dim StrAsDate$
    StrAsDate = ActiveSheet.Range("C" & ActiveCell.Row) '28.12.2012

    With MonthView .Month = Mid(StrAsDate, 4, 2) .Day = Mid(StrAsDate, 1, 2) .Year = Mid(StrAsDate, 7, 4) End With

    So, so EOT?

    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Friday, January 4, 2013 9:42 AM
  • @Rod Gill

    Using Range.Text or Range.Value gives the same result

    @Oskar Shon

    As I said in my first post, cells are formatted as Date. In fact if use +1 formula it shows the following day and not #ARG! or whatever else.

    I've also tried similar code (second way described in first post) but I get runtime error 35779 "Impossible to set property Day if Multiselect = True." even if the line right above it is actualy MonthView.MultiSelect = False, which leads me to think about a bug in this monthview control.

    I'll rewrite the code of this form and see if I can get around this issue

    Saturday, January 5, 2013 1:12 AM
  • Tigre would you add link to this file (skydrive or another)

    Maybe that your local problem, maybe not. When wee see this file you be sure solution

    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Saturday, January 5, 2013 3:35 PM
  • Unfortunately it contains sensitive data, I can't share the document as is.

    In the meanwhile, the userform has become more complex.

    I created an additional sub, invoked at the end of the activate sub, which contains the MonthView value direct assignment (MonthView.Value = ....) and haven't encountered the issue anymore since then.

    Can it be that the component was not loaded (even if the whole userform is) or not focused yet?

    I'm still unable to understand why this happened and why it happened only some times.

    Friday, January 11, 2013 7:39 PM
  • Just for the record.

    Today I got the same issue.

    The conclussion is that you try to programtically set a Value to the MonthView that is higher than the value in MonthView.MaxDate

    Thursday, March 2, 2017 9:22 PM