Can't get value from DatePicker

Question

I have a DatePicker control on a worksheet. When I'm in the embedded code for the worksheet, I can access the control's value as follows:

Public Sub showValue()

Debug.Print Me.DTPicker21.value

End Sub

I would like to get the value from a module. Code here:

Sub getDate()

Dim obj As Object
Dim sht As Worksheet

Set sht = ThisWorkbook.Sheets("Tool interface")
For Each obj In sht.OLEObjects
    If obj.Name = "DTPicker21" Then
        Debug.Print obj.value
    End If
Next

End Sub

When I run this, the obj.value triggers this error:

Object doesn't support this property or method

I checked the list of properties for obj using this procedure, and there is no value property. How can I get the date value that's been set in the DatePicker?

basodre · Accepted Answer · 2016-05-16T18:51:56.343

I don't know all of the details, but some of the OLEObjects require that you first access their Object property, then you can access other properties. I think the OLEObject serves as a container, then the "sub-object" is the actual object with which you want to interact. For example, if you run the following two lines of code, you will see that the first returns OleObject and the second returns DTPicker:

Debug.Print "Obj: " & TypeName(obj)
Debug.Print "Obj.Object: " & TypeName(obj.Object)

In your case, try the following code change to remove the error(note the Debug line):

Sub getDate()

Dim obj As Object
Dim sht As Worksheet

Set sht = ThisWorkbook.Sheets("Tool interface")
For Each obj In sht.OLEObjects
    If obj.Name = "DTPicker21" Then
        Debug.Print obj.Object.Value
    End If
Next

End Sub

Can't get value from DatePicker

1 Answers1