davesexcel.com

Excel information

Select a Column to Sum from a Combo Box in a UserForm

You have three columns of data, you want to display the sum of a column by what you select in a ComboBox from a UserForm

If you do not know how to create a UserForm Check out this page.

Create a UserForm

Add a ComboBox and a TextBox to the UserForm

We can use VBA to name each Column of Data.

Paste this code into the UserForm  Module

 

Private Sub UserForm_Initialize()

    Dim Rws As Long, Rng As Range
    Dim cr As Range, R As Range

    Set cr = Worksheets("Sheet1").Range("A1", Range("IV1").End(xlToLeft))
    Set R = Range("A1")   
    Rws = Cells.Find(What:="*", After:=R, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Set Rng = Range(Cells(1, 1), Cells(Rws, 3))
    
    Application.DisplayAlerts = False
    Rng.CreateNames Top:=True, Left:=False, Bottom:=False, Right:= _
                    False
    Me.ComboBox1.List = Application.WorksheetFunction.Transpose(cr)
    
End Sub

What is this Code doing?

 This part of the code shown below finds the last used row and sets the range A1 to C(Last Row), this determines what  Rng should be.

  Set R = Range("A1")   
    Rws = Cells.Find(What:="*", After:=R, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Set Rng = Range(Cells(1, 1), Cells(Rws, 3))

This part of the code shown below will then name the used columns in the Rng and  name each of those columns as the column header.

    Application.DisplayAlerts = False
    Rng.CreateNames Top:=True, Left:=False, Bottom:=False, Right:= _
                    False

Note: Application.DisplayAlerts = False, is used because when you add or delete rows to the columns excel will ask you if you want to change the named range.

The part of the code below will populate the ComboBox in the UserForm with the Column Headings.

    Me.ComboBox1.List = Application.WorksheetFunction.Transpose(cr)

Paste this code into the UserForm module.

Private Sub ComboBox1_Change()

    TextBox1 = Application.WorksheetFunction.Sum(Range(ComboBox1))
    
End Sub

This will send the results to the TextBox.

Check out the Example Workbook.

Select a Column to Sum.xlsm

 

 Check Out Range Selection Codes for more info.

Range Selection Codes