Error with Calendar object and CommandButton

[Morris]

Hi, I have never worked with calendar object yet, so here's my
question:

Private Sub Workbook_Open()
CommandButton1.Caption = "Import the list of matches from " &
Calendar1.Value
End Sub

Run-time error '424':
Object required.


Why...?

 

[Guest]

i think u have not inserted calendar...

 

[Norman Jones]

Hi Morris,

Try something like:

'=============>>
Private Sub Workbook_Open()
Dim SH As Worksheet
Dim myCB As OLEObject
Dim myCal As OLEObject

Set SH = Me.Sheets("Sheet2") '<<==== CHANGE

Set myCB = SH.OLEObjects("CommandButton1")
Set myCal = SH.OLEObjects("Calendar1")

myCB.Object.Caption = "Import the list of matches from " _
& myCal.Object.Value
End Sub
'<<=============

 

[Morris]

Hi Morris,

Try something like:

'=============>>
Private Sub Workbook_Open()
Dim SH As Worksheet
Dim myCB As OLEObject
Dim myCal As OLEObject

Set SH = Me.Sheets("Sheet2") '<<==== CHANGE

Set myCB = SH.OLEObjects("CommandButton1")
Set myCal = SH.OLEObjects("Calendar1")

myCB.Object.Caption = "Import the list of matches from " _
& myCal.Object.Value
End Sub
'<<=============

Thanks Norman, it is working now

Muhameed: funny guess