Results 1 to 3 of 3

Automatically get sum value of cells selected by user for User Form's Label ?

Threaded View

  1. #1
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Automatically get sum value of cells selected by user for User Form's Label ?

    Assuming in column A from row 1 to 5 the values are:
    10
    20
    30
    40
    50

    When user select cells A1 and A2,
    on the bottom right of Excel app it show:
    Average:15 Count:2 Sum:30

    When user select cells A1, A3 and A5,
    on the bottom right of Excel app it show:
    Average:30 Count:3 Sum:90

    So that information change automatically
    each time the user add a cell to the selection.

    I wonder if it's possible to write a code,
    so that kind of "automation" for the Sum
    can be seen in a user form's label.

    So far I've tried a code :
    Private Sub UserForm_Initialize()
    Me.Label1.Caption = Application.Sum(Selection)
    End Sub
    But with the code above, I need to provide a button for the user to click to calculate the sum of his selection in order the Label1.Caption show the Sum result.

    I've also tried a code in worksheet like this:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:A5")) Is Nothing Then
    Range("B1").Value = Application.Sum(Selection)
    End If
    End Sub
    Cell B1 did automatically show the sum result each time a cell in range A1 to A5 is selected.
    But then I don't know how to implement it to the User Form label, and actually it doesn't work as my expectation
    because with this code, cell B1 will show the sum result only after I release the left click of the mouse,
    while I'm expecting it show the sum as long as the user drag a selection (without releasing the left click mouse) in the range just like the "average/counts/sum automation" at the bottom right of the Excel App.

    Any kind of respond would be greatly appreciated.
    Thank you in advanced.
    Last edited by karmapala; 12-14-2019 at 03:16 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Excel 2013 user form unable to set label text color in VBA code
    By silveredge8181 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-09-2016, 04:31 PM
  2. [SOLVED] User Form to execute search and return all values to the user form for editing
    By allwrighty in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-11-2013, 10:40 PM
  3. user form label real time update
    By samz93 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-05-2013, 11:38 AM
  4. How do I use scroll bar to view very LONG label in User Form?
    By sauerj in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-17-2012, 12:52 AM
  5. VBA User Form Label won't show
    By ker9 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-29-2011, 09:57 AM
  6. User Form Label not filling correctly
    By duckboy1981 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-10-2008, 05:34 AM
  7. In need to format a label in an user form
    By jobro in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-05-2008, 08:07 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1