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 Works...
Macros & VBA
Need Help
Muhammad Ali's avatar
Muhammad Ali
Copper 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 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

 

 

  • Muhammad Ali's avatar
    Muhammad Ali
    Copper Contributor
    Jun 25, 2018

    Thank you Sir, It worked.

     

    Now I am getting Compile Error: "Can't Find project or library" on below code:

     

    daCurrentMonthSheet = Format(VBA.Date - 1, "mmm")


    Thanks

    • Matt Mickle's avatar
      Matt Mickle
      Bronze Contributor
      Jun 25, 2018

      Try this:

       

      daCurrentMonthSheet = Format(Date - 1, "mmm")

       

       

      • Muhammad Ali's avatar
        Muhammad Ali
        Copper Contributor
        Jun 28, 2018

        Thank you, it fixed it now its stuck on below with same error:

         

        daMonthSheet = Format(Sheets("Daily Cover Page Data").Calendar1, "mmm")

        iCol = Format(Sheets("Daily Cover Page Data").Calendar1, "dd") + 6

Resources