Forum Discussion
Changing already made macro from Excel 2010 to 2013
Hi, I am not very good with VBA. I am trying to change already made macro in excel 2010 to 2013. I am getting "Run-time error '438': Object doesn't support this property or method.
Private Sub Worksheet_Activate()
Sheets("Daily Cover Page Data").Calendar1.Height = 124.5
Sheets("Daily Cover Page Data").Calendar1.Left = 195
Sheets("Daily Cover Page Data").Calendar1.Top = 42
Sheets("Daily Cover Page Data").Calendar1.Width = 174.75
End Sub
TIA
- Matt MickleBronze Contributor
If the Calendar is a shape on the worksheet you may try using something like this instead (See attached example):
Private Sub Worksheet_Activate() With Me.Shapes.Range("Calendar1") .Height = 124.5 .Left = 195 .Top = 45 .Width = 174.75 End With End Sub
Note: You will have to paste this code into the workbook yourself, since macro enabled files are not permitted on the forum due to security risks.
- Muhammad AliCopper Contributor
Thank you very much for response. I am wondering where I should put sheet name "Daily Cover Page Data" in the code you mentioned.
Regards
Matt Mickle wrote:If the Calendar is a shape on the worksheet you may try using something like this instead (See attached example):
Private Sub Worksheet_Activate() With Me.Shapes.Range("Calendar1") .Height = 124.5 .Left = 195 .Top = 45 .Width = 174.75 End With End Sub
Note: You will have to paste this code into the workbook yourself, since macro enabled files are not permitted on the forum due to security risks.
- Matt MickleBronze Contributor
Hey Muhammad-
It is not necessary to add the worksheet name into the code. Me refers to the worksheet. Please note this is a Worksheet Event and the VBA code needs to be put in the "Daily Cover Page Data" worksheet module.
Similarly, if you were in a Userform code module Me would refer to the userform. So rather than typing
UserForm1.TextBox1
you can just use:
Me.TextBox1
If you want you can add in though... it would look like this:
Private Sub Worksheet_Activate() With Sheets("Daily Cover Page Data").Shapes.Range("Calendar1") .Height = 124.5 .Left = 195 .Top = 45 .Width = 174.75 End With End Sub