Calendar by BlueCactus not works

[xman2000]

Hello people!

His Calendar by Bluecactus not working never.

(If who have a Calendar by Bluecatus working, please me a copy in the forum topic. Thanks)


when I launch the Form, the VBA crashes, “Error 13”, “Incompatible types” (Portuguese Language traduction)

The editor VBE goes to tihis line: Call DatePick.FillVars (....)

(If who have a Calendar by Bluecatus working, please me a copy in the forum topic. Thanks)

(Im using Excel 2013 32bit Windows version.)

below are part of the the functions in crash:
error line is Blue.
‘----------------------------------------------------------------------

Function getUserDate(Optional defaultDate As Date = Empty, Optional dpMode As Integer = 0, _
    Optional displayComments As Boolean = False, Optional eL As Variant = "") As Variant
' This code does the actual call to the calendar form.
Dim i As Integer, addText As String, dateResults As Variant, eventList As Variant
' customUserDate() not called - set some defaults
    If Not useCustomExtras Then
        baseYear = DatePart("yyyy", Date) - 10
        endYear = DatePart("yyyy", Date) + 10
        optionFindToday = False
        widthDay = 30
        heightDay = 20
        forceCancel = False
        bkColor = vbWindowBackground
        selColor = vbHighlight
        highColor = 255
    End If
If IsArray(eL) Then eventList = eL
Load DatePick
' Custom form position specified
    If useCustomExtras And formX <> -1 And formY <> -1 Then
        DatePick.startupposition = 3
        DatePick.Left = formX
        DatePick.Top = formY
    End If
useCustomExtras = False
‘ERROR-HERE IS THE PROBLEM: ‘Call DatePick.FillVars(…)
    Call DatePick.FillVars(defaultDate, dpMode, forceCancel, displayComments, eventList, optionFindToday, widthDay, heightDay, baseYear, endYear, bkColor, selColor, highColor)
DatePick.Show
' Events returned to this function - repackage them.
    If IsArray(eventCodes) Then
        ReDim dateResults(0 To UBound(eventCodes))
        dateResults(0) = displayDate
        For i = 1 To UBound(eventCodes)
            dateResults(i) = eventCodes(i)
        Next i
    Else
        If displayDate = Empty Then
            dateResults = ""
        Else
            dateResults = displayDate
        End If
    End If
getUserDate = dateResults
End Function

Sub FillVars(Optional sD As Date = Empty, Optional dpM As Integer = 0, Optional fC As Boolean = True, _
    Optional dC As Boolean = False, Optional eL As Variant = "", Optional oFT As Boolean = False, _
    Optional widthDay As Double = 30, Optional heightDay As Double = 20, Optional bY As Integer = 0, _
    Optional endYear As Integer = 0, Optional bC As Long = vbWindowBackground, Optional sC As Long = vbHighlight, _
    Optional hC As Long = 255)
'   This sub takes the place of Userform_Initialize and is used to fetch parameters from the calling code.
'   You may wish to change some of the defaults above.
'continues....

[xman2000]

hello! Help me please!

new sample file embeded here.
Screens pictures of errors into file.

when Iam clik on de button in sheet to launch the Form, theVBA crashes, “
“Error 13”, “Incompatible types”
The editor VBE goes to tihis line: Call DatePick.FillVars (....)

-if iam delete comments and eventlist of the line:
“compitation" "error 13”, “Incompatible types” "argument" "ByRef"
or "not" "properties" "Listindex"


"Call DatePick.FillVars(defaultDate, dpMode, forceCancel, displayComments, optionFindToday, heightDay, baseYear, endYear, bkColor, selColor, highColor)"

[serj1980]

Guys. Answer already published by me. Simple change DateValue to DateSerial as on screen.
And please debug. In current case error displayed in wrong place (see screenshot).vba.jpg

DatePick (code)

Sub SetDays(ByVal tempDate As Date)'   This code switches the calendar view to the required month and year
    Dim i As Integer, numDay As Integer, numEvents As Integer
    
'   Use the 1st of whatever month was passed
'THIS WORK DEPEND ON SYSTEM LANGUAGE
    'tempDate = DateValue(monthNames(DatePart("m", tempDate) - 1) & " 1, " & DatePart("yyyy", tempDate))
'THIS WORK IN ANY LANGUAGE
    tempDate = DateSerial(DatePart("yyyy", tempDate), DatePart("m", tempDate), 1)

[GTO]

Just in case one wishes to download BlueCactus' original from the KB, it is at: http://www.vbaexpress.com/kb/getarticle.php?kb_id=791

[Paul_Hossler]

I think that there's still a few issues after I downloaded the KB version from GTO's link

Even after the fix in #3, there's still issues with (at least) the "5-Advanced [Try Me]".

Might not be an issue in real use, but the KB version could use some lookin' at

I get a Type mismatch error in this line

            dateResults = getUserDate(.Cells(74, 5), dpMode, .Cells(76, 5).Value, eventList)

The first parameter should be the default date

Function getUserDate(Optional defaultDate As Date = Empty, Optional dpMode As Integer = 0, _
    Optional displayComments As Boolean = False, Optional eL As Variant = "") As Variant

but Cells(74,5) is 'Selection Color' = -2147483635.

It looks like some or all of the row numbers in the macro need to be increased by 2 to get something like this

            dateResults = getUserDate(.Cells(76, 5), dpMode, .Cells(78, 5).Value, eventList)

Capture.JPG

[xman2000]

Just in case one wishes to download BlueCactus' original from the KB 791

hello GTO user!

this kb original not , Not works.
is this file that iam have and not not works in Excel 2013 32bit win7.

i need a copy that works.
---------------------------------------
hello Serj1980

iam will test you code.
thanks!
-----------------------------------

Hello people,

i need help in this threat

thanks

[xman2000]

Hello Paul_Hossler !

Thank you by your ccolaboration!
You is in the right way!

Iam poor in VBA and English!

I think problem maybe in EventList (listbox), displayComments, colors values, and date settings values examples DisplayDate, Datevalue, etc.
Iam error im Fillvars line code too.

In the line I thin problem be not colors, but EventList or Date settings:
dateResults = getUserDate(.Cells(74, 5), dpMode, .Cells(76, 5).Value, eventList)

I have look the date of default of Calendar is thru Time value 00:00:00 and not Date value.
I think 1o. step is change date settings and color settings of the workbook,
And delete listbox and put new listbox object.

Exists 2 different macros 2 different results in workbook call calendar in the button on sheets
1 macro call TestCalMacro, is the alternative code.
Need fix this all 2 macro codes, the default macro and alternative macro.

PS: The Date settings the Calendar are settings to 20 years previous and 20 years after current date WindowsSystem.
Calendar created in year 2005 and current year is 2016.
Date setting (regional) is different in the countries of the users.
Serj1980 speak have changed this configurations in the other threat this forum.

Thanks.

[GTO]

I think that there's still a few issues after I downloaded the KB version from GTO's link

Even after the fix in #3, there's still issues with (at least) the "5-Advanced [Try Me]"...

YIKES! Yeh, I wonder what happened? I have to run, but it should get looked at. I wonder if BlueCactus posts any more?

Have a great weekend Paul.

Mark

[xman2000]

BlueCactus is alive?
some user of the forum have the workbook fixed!

not not have a great weekend friends!
weekend with me in this thread of hell!

im working in this issue along months!

[serj1980]

...

I get a Type mismatch error in this line

            dateResults = getUserDate(.Cells(74, 5), dpMode, .Cells(76, 5).Value, eventList)

Please use by step debug (F8) to found real place of error. I don't know reason, but debuger show wrong place. Error inside getUserDate function (or subfunction).
I point only one place for change, but in code DateValue used several times.

[Paul_Hossler]

That is the line throwing the error because the parameters being passed are the wrong type

In the function definition

Function getUserDate(Optional defaultDate As Date = Empty, Optional dpMode As Integer = 0, _ 
    Optional displayComments As Boolean = False, Optional eL As Variant = "") As Variant

The first parameter is typed as a Date.


However, in the call where the error occurs

dateResults = getUserDate(.Cells(74, 5), dpMode, .Cells(76, 5).Value, eventList)

the first parameter is .Cells(74,5) which is a Long and not convertible to a Date ( .Cells(74,5) = 'Selection Color' = -2147483635)

So I think the debugger is correctly showing the line causing the Err 13

[serj1980]

Paul, i'm sorry for mistake. You right.

dateResults = getUserDate(.Cells(74, 5), dpMode, .Cells(76, 5).Value, eventList) 

should be changed to

dateResults = getUserDate(.Cells(76, 5), dpMode, .Cells(78, 5).Value, eventList) 

I don't know reason of this shift down on two cell. Better to check other parameters

but next error with datevalue

Private Sub SetNewDate(setDay As String)'   Change the selected date
    If canUpdate = 0 Then
        If dpMode = 0 Then
'           Quits form with selected date for dpMode 0
            Call CalendarModule.returnDate(DateValue(ComboBox1.Text & " " & setDay & ", " & ComboBox2.Text), "no events")

I used only simple scenario.

[xman2000]

i need check, but in Load DatePic FillVars the date is show as time value 00:00:00
yes Paull, the Debug Error show data type incompatibles.
im not sure about i am speak.

[xman2000]

hello people!

I'm thinking many things about the tests I'm doing but I do not know if they are important.


I am writing a report about it, but I'm afraid to pollute the thread.

which errors you guys already solved and how ??


soon I post about my ideas.

[xman2000]

hello partners!

please answer to me


I wonder if you want to issue your results first or if you want that i issue now.
I'm doing several tests and researching errors.
I have several ideas, and have perception about various things.
but I do not have enough knowledge about vba and for this reason I am avoiding post for now until I have more certainty.

cheers!

[xman2000]

Hi partners !

someone wants to work with me to solve this case?

i think origin of problem of Calendar and Error 13 Type Mismatch maybe :
(I have also other suspicious)

- The origin of error 13 maybe Textbox format or
- Textbox empty, null, or blank values on start Userform, on start Macros
- The error maybe of declarations of variables
- The format of Dates is wrong, example as time value
- The controls activex older, incompatibles
- declarations value types Long, Interger, Date, Variant, etc

tell me please!
Cheers!

[xman2000]

Hello!


here is a link of Calendar case that Textbox empty generate Error 13 Type Mismatch:


"http://www.mrexcel.com/forum/excel-questions/648867-empty-textbox-type-mismatch-error-calendar-date-picker.html"


----------------------------------------------------------
PROBLEM


My calendar was created using Toolbox Controls.. I just pasted it in my Userform2.


here is the code behind the Calendar.


Private Sub Calendar1_Click()
UserForm1.JoinDateTxt.Value = Format(Calendar1.Value, "dd/mmm/yy")
'ActiveCell.NumberFormat = "dd/mm/yy"
End Sub
----------------------------------------------------------
SOLUTION


Private Sub JoinDateTxt_Enter()
FrmCalendar.Show
If Len(JoinDateTxt.Text) > 0 Then
JoinDateNum = DatePart("d", JoinDateTxt) - 1
End If
End Sub
----------------------------------------------------------

[Paul_Hossler]

1. I deleted 2 empty lines from the WS "5-Advanced Options" to line up the cells on the WS with the code in the

ElseIf ActiveSheet.Name = "5-Advanced Options" Then

branch of testCal so that the [Try Me] buttons work



2. When getUserDate was called, a blank default returned "12:00:00 AM" so I made a quick fix (Button on worksheet "Test" to demo)

unction getUserDate(Optional defaultDate As Date = Empty, Optional dpMode As Integer = 0, _
    Optional displayComments As Boolean = False, Optional eL As Variant = "") As Variant
    
' This code does the actual call to the calendar form.
    Dim i As Integer, addText As String, dateResults As Variant, eventList As Variant
    
    'phh 4/12/2016
    If TimeSerial(0, 0, 0) = defaultDate Then defaultDate = Date

[xman2000]

Hi Paul and friends!
I'm happy to see you

this showing even before Errors, did not work.

on your computer work?

It could be some VBE activex as the form?
I put a screen of my activex for you guys to see.
tomorrow I'll be back to play with the calendar.

[Paul_Hossler]

Works for me

On the worksheet 'Test' if you click the button, it shows the userform and the date you select goes into the active cell formatted as a date

I have Office 2016, but that should not make any difference


Capture.JPG


I have no further ideas, so maybe someone else can help