Forum Discussion

Muhammad Ali's avatar
Muhammad Ali
Copper Contributor
Jun 19, 2018

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

Macros & VBA
Need Help
  • Matt Mickle's avatar
    Matt Mickle
    Bronze Contributor
    Jun 19, 2018

    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 Ali's avatar
      Muhammad Ali
      Copper Contributor
      Jun 22, 2018

      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 Mickle's avatar
        Matt Mickle
        Bronze Contributor
        Jun 22, 2018

        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

         

         

Resources