Solved

Excel VBA Updating existing text label

Posted on 2007-03-26
3
180 Views
Last Modified: 2010-04-30
Hi all

I'm attempting to update a text label with a subtotal formula.  I'd like to do it without relying on VBA, but I'll take what I can get.  Subtotal will update anytime an autofilter criteria is changed, so I'd like to rely on that to change my label, but I can figure out how to do it outside a procedure, which follows:

Sub Calc_Subtotals()
'
    Application.ScreenUpdating = False

    With Sheet12.Subtotal_lbl
    .Caption = Range("D6").Text
    End With

    Application.ScreenUpdating = True
'
End Sub

Thanks for any suggestions!

-Scott
0
Comment
Question by:bfreescott
  • 2
3 Comments
 
LVL 81

Accepted Solution

by:
byundt earned 250 total points
ID: 18797721
Scott,
I assume that you want to change the caption on a command button or the like. If so, you can take advantage of the fact that the Calculate event sub runs whenever you use the AutoFilter and thereby change the value in a SUBTOTAL formula. Try the following sub in the codepane of the worksheet containing your AutoFilter.

Private Sub Worksheet_Calculate()
With Sheet12.Subtotal_lbl
      .Caption = Range("D6").Text
End With
End Sub

Brad
0
 

Author Comment

by:bfreescott
ID: 18798247
That did the trick!  Thanks Brad!
0
 
LVL 81

Expert Comment

by:byundt
ID: 18799249
Scott,
Thanks for the grade!
Brad
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question