Results 1 to 4 of 4

Excel 2013 VBA ActiveSheet

This is a discussion on Excel 2013 VBA ActiveSheet within the Excel Questions forums, part of the Question Forums category; I am having an issue with the following code block. Works fine in Excel 2010 but will not work in ...

  1. #1
    Board Regular ndjustin20's Avatar
    Join Date
    May 2011
    Posts
    66

    Default Excel 2013 VBA ActiveSheet

    I am having an issue with the following code block. Works fine in Excel 2010 but will not work in excel 2013. Is there an issue with OLE Control Objects in 2013 VBA or am I missing something. The code stops at "ActiveSheet.monthOneBudTxt.Value = Format(Range("JanBud").Value, "$###,###")" and returns an error of:
    Run-Time error 438 Object doesn't support this property or method

    Code:
    Sub firstQtrByMonth()
    
        'MsgBox "The name of the active sheet is " & ActiveSheet.Name
          
    
        ActiveSheet.monthOneBudTxt.Value = Format(Range("JanBud").Value, "$###,###")
        ActiveSheet.monthOneLabel.Value = "Jan"
        ActiveSheet.monthTwoBudTxt.Value = Format(Range("FebBud").Value, "$###,###")
        ActiveSheet.monthTwoLabel.Value = "Feb"
        ActiveSheet.monthThreeBudTxt.Value = Format(Range("MarBud").Value, "$###,###")
        ActiveSheet.monthThreeLabel.Value = "Mar"
        
        ActiveSheet.monthOneActTxt.Value = Format(Range("JanAct").Value, "$###,###")
        ActiveSheet.monthTwoActTxt.Value = Format(Range("FebAct").Value, "$###,###")
        ActiveSheet.monthThreeActTxt.Value = Format(Range("MarAct").Value, "$###,###")
        
        
        ActiveSheet.monthOneIncDecTxt.Value = Format((ActiveSheet.monthOneActTxt.Value / ActiveSheet.monthOneBudTxt.Value), "#%")
        ActiveSheet.monthTwoIncDecTxt.Value = Format((ActiveSheet.monthTwoActTxt.Value / ActiveSheet.monthTwoBudTxt.Value), "#%")
        ActiveSheet.monthThreeIncDecTxt.Value = Format((ActiveSheet.monthThreeActTxt.Value / ActiveSheet.monthThreeBudTxt.Value), "#%")
        
        'ActiveSheet.monthlyBreakDownHeader.Value = "1st Qtr Monthly Breakdown"
        
        'ActiveSheet.quarterlyBreakdownHeader.Value = "1st Qtr Analysis"
        
        ActiveSheet.QtrBudTotal.Value = Format(ActiveSheet.monthOneBudTxt.Value + ActiveSheet.monthOneBudTxt.Value + _
        ActiveSheet.monthOneBudTxt.Value, "$###,###")
        
        ActiveSheet.QtrActTotal.Value = Format(ActiveSheet.monthOneActTxt.Value + ActiveSheet.monthOneActTxt.Value + _
        ActiveSheet.monthOneActTxt.Value, "$###,###")
        
        ActiveSheet.monthOneVariance.Value = Format(ActiveSheet.monthOneActTxt.Value - ActiveSheet.monthOneBudTxt.Value, "$###,###")
        ActiveSheet.monthTwoVariance.Value = Format(ActiveSheet.monthTwoActTxt.Value - ActiveSheet.monthTwoBudTxt.Value, "$###,###")
        ActiveSheet.monthThreeVariance.Value = Format(ActiveSheet.monthThreeActTxt.Value - ActiveSheet.monthThreeBudTxt.Value, "$###,###")
        
        
        'ActiveSheet.cisCompBreakDownHeader.Value = "First Quarter CIS Comparison"
    
    
    End Sub

  2. #2
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Redmond, WA
    Posts
    29,403

    Default Re: Excel 2013 VBA ActiveSheet

    Smitty

    Every once in a while, there's a sudden gust of gravity...

    Check out my new book at the Mr. Excel Bookstore!

    Mr. Excel HTML Maker - Post a shot of your sheet

  3. #3
    Board Regular ndjustin20's Avatar
    Join Date
    May 2011
    Posts
    66

    Default Re: Excel 2013 VBA ActiveSheet

    Smitty!!!!!!! Thank you for the help!!!!! I've been trying and retrying so much to try and figure this out. I know the code did work but just couldn't for the life of me figure out why it suddenly stopped working. Very odd for sure.


    Thanks Again!!!

  4. #4
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Redmond, WA
    Posts
    29,403

    Default Re: Excel 2013 VBA ActiveSheet

    Believe me, you're not the only one who got a nasty surprise!
    Smitty

    Every once in a while, there's a sudden gust of gravity...

    Check out my new book at the Mr. Excel Bookstore!

    Mr. Excel HTML Maker - Post a shot of your sheet

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com