ListBox ComboBox

1/24/2017 - 11:01 AM

Private Sub listbox_AfterUpdate()
    Me.[textbox1] = Me.listbox.Column(0)
    Me.[textbox2] = Me.listbox.Column(1)
End Sub

Table stuff

https://www.thespreadsheetguru.com/blog/2014/6/20/the-vba-guide-to-listobject-excel-tables



***Select

Entire Table 
ActiveSheet.ListObjects("Table1").Range.Select 

Table Header Row 
ActiveSheet.ListObjects("Table1").HeaderRowRange.Select 

Table Data 
ActiveSheet.ListObjects("Table1").DataBodyRange.Select 

Third Column 
.ListObjects("Table1").ListColumns(3).Range.Select 

Third Column (Data Only) 
ActiveSheet.ListObjects("Table1").ListColumns(3).DataBodyRange.Select

Select Row 4 of Table Data 
ActiveSheet.ListObjects("Table1").ListRows(4).Range.Select 

Select 3rd Heading 
ActiveSheet.ListObjects("Table1").HeaderRowRange(3).Select 

Select Data point in Row 3, Column 2 
ActiveSheet.ListObjects("Table1").DataBodyRange(3, 2).Select 

Subtotals 
ActiveSheet.ListObjects("Table1").TotalsRowRange.Select 



***Inserting Rows and Columns Into The Table

Insert A New Column 4 
ActiveSheet.ListObjects("Table1").ListColumns.Add Position:=4 

Insert Column at End of Table 
ActiveSheet.ListObjects("Table1").ListColumns.Add 

Insert Row Above Row 5 
ActiveSheet.ListObjects("Table1").ListRows.Add (5) 

Add Row To Bottom of Table 
ActiveSheet.ListObjects("Table1").ListRows.Add AlwaysInsert:= True 

Add Totals Row 
ActiveSheet.ListObjects("Table1").ShowTotals = True

Populate listbox

Private Sub UserForm3_Initialize() 
    With UserForm3.MultiPage1 
        ListBox1.RowSource = "pricer" 
        ListBox2.RowSource = "Novation" 
    End With 
End Sub

Loop tables ws

Sub LoopThroughAllTablesInWorksheet()

'PURPOSE: Loop through and apply a change to all Tables in the Active Excel Sheet
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault

Dim tbl As ListObject

'Loop through each sheet and table in the workbook
    For Each tbl In ActiveSheet.ListObjects
      
      'Do something to all the tables...
        tbl.ShowTotals = True
        
    Next tbl

End Sub

Loop tables wb

Sub LoopThroughAllTablesinWorkbook()

'PURPOSE: Loop through and apply a change to all Tables in the Excel Workbook
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault

Dim tbl As ListObject
Dim sht As Worksheet

'Loop through each sheet and table in the workbook
  For Each sht In ThisWorkbook.Worksheets
    For Each tbl In sht.ListObjects
      
      'Do something to all the tables...
        tbl.ShowTotals = True
        
    Next tbl
  Next sht

End Sub

ListBox mulitcolumn

'ListBox properties: ColumnCount X - ColHeads True - ColWidth 55pt;60pt...
    
If ListBoxFrontPage.ListIndex > -1 Then

Dim listObj As Excel.ListObject
Dim listRow As Excel.listRow
        
Set listObj = Worksheets("Log").ListObjects("Tabell24")
Set listRow = listObj.ListRows(ListBoxFrontPage.ListIndex + 1)
        
        listRow.Range.Columns(1) = DateValue(Me.TextBoxLogDate.Value)
        listRow.Range.Columns(2) = Me.TextBox1.Value
        listRow.Range.Columns(3) = Me.TextBox2.Value

ListBox Ex - SetRowSource

Private Sub SetRowSource()

    Dim Last_Row As Variant
    Dim ws As Worksheet
    Set ws = Worksheets("Log")
    
    ws.Activate
    
    Last_Row = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    With ListBoxFrontPage
        'RowSource = ThisWorkbook.Sheets("Log").Range("DataTabell")
        '.RowSource = "DataTabell"
        .RowSource = ws.Range("A7:N" & Last_Row).Address
    End With
    
End Sub

ListBox Ex - SaveChangesToTable

Private Sub CommandButtonSaveChanges_Click()

    FUAWorkBook.Activate
    Dim selInd As Integer
    
        If ListBoxFrontPage.ListIndex > -1 Then
        selInd = ListBoxFrontPage.ListIndex
        
        Dim listObj As Excel.ListObject
        Dim listRow As Excel.listRow
        
        Set listObj = Worksheets("Log").ListObjects("Tabell24")
        Set listRow = listObj.ListRows(ListBoxFrontPage.ListIndex + 1)
        
        'listRow.Range.Columns(1) = Format(Me.TextBoxLogDate.Value, "dd/mm/yyyy")
        listRow.Range.Columns(1) = DateValue(Me.TextBoxLogDate.Value)
        listRow.Range.Columns(2) = Me.TextBox1.Value
        listRow.Range.Columns(3) = Me.TextBox2.Value
        listRow.Range.Columns(4) = Me.TextBox3.Value
        listRow.Range.Columns(5) = Me.TextBox4.Value
        listRow.Range.Columns(6) = Me.TextBox5.Value
        listRow.Range.Columns(7) = Me.ComboBox6.Value
        listRow.Range.Columns(8) = Me.ComboBox7.Value
        listRow.Range.Columns(9) = Me.TextBox8.Value
        listRow.Range.Columns(10) = Me.Combo9.Value
        listRow.Range.Columns(11) = Me.TextBox10.Value
        listRow.Range.Columns(12) = Me.TextBox11.Value
        listRow.Range.Columns(13) = Me.ComboBox1.Value
        listRow.Range.Columns(14) = Me.TextBox12.Value
        
        'Application.StatusBar = "Table updated!"
        
        'Application.Calculate
        
        Me.MultiPage1.Value = 0
        
        ListBoxFrontPage.ListIndex = selInd
    
    Else
        MsgBox ("Feil! Ingen rad i listen er valgt!")
    End If
    
    'DoEvents
    'ActiveSheet.Calculate
    

End Sub

ListBox Ex - NewRowBottom

Private Sub CommandButtonNewRowBottom_Click()

    FUAWorkBook.Activate

    'try to clear listbox before tablemodification
    ListBoxFrontPage.RowSource = ""


    Dim listObj As Excel.ListObject
    Dim listRow As Excel.listRow
    
    Set listObj = Worksheets("Log").ListObjects("Tabell24")

    listObj.ListRows.Add AlwaysInsert:=False
    
    Call SetRowSource
    
    ListBoxFrontPage.ListIndex = ListBoxFrontPage.ListCount - 1
    
    'TextBoxLogDate.Value = Format(Now() + 1, "Short Date")
    

End Sub

ListBox Ex - NewRowBelow

Private Sub CommandButtonNewRowBelow_Click()
    
    FUAWorkBook.Activate
    
    Dim selectedIndex As Integer
    selectedIndex = ListBoxFrontPage.ListIndex
    'Debug.Print selectedIndex
    
    If selectedIndex > -1 Then
        
        ListBoxFrontPage.RowSource = ""
    
        Dim listObj As Excel.ListObject
        Dim listRow As Excel.listRow
        
        Set listObj = Worksheets("Log").ListObjects("Tabell24")
    
        listObj.ListRows.Add (selectedIndex + 2)
        
        Call SetRowSource
    
    Else
        MsgBox ("Feil! Du må velge en rad først!")
    End If
    
End Sub

ListBox Ex - NewRowAbove

Private Sub CommandButtonNewRowAbove_Click()
    
        FUAWorkBook.Activate
    
    Dim selectedIndex As Integer
    selectedIndex = ListBoxFrontPage.ListIndex
    'Debug.Print selectedIndex
    
    If selectedIndex > -1 Then
        
        ListBoxFrontPage.RowSource = ""
    
        Dim listObj As Excel.ListObject
        Dim listRow As Excel.listRow
        
        Set listObj = Worksheets("Log").ListObjects("Tabell24")
    
        listObj.ListRows.Add (selectedIndex + 1)
        
        Call SetRowSource
    
    Else
        MsgBox ("Feil! Du må velge en rad først!")
    End If
    
End Sub

ListBox Ex - EmptyRowSource

Private Sub EmptyRowSource()

    With ListBoxFrontPage
        .RowSource = ""
    End With
    

End Sub

ListBox Ex - CopyToNew

Private Sub CommandButtonCopyAndNew_Click()

    FUAWorkBook.Activate
    
    Dim listObj As Excel.ListObject
    Dim listRow As Excel.listRow
    Dim selInd As Integer
    Dim lr As Range
    Dim dstRow As Excel.listRow
    selInd = ListBoxFrontPage.ListIndex

    'check that something is selected
    If selInd > -1 Then

    'prepare
    ListBoxFrontPage.RowSource = ""
    Set listObj = Worksheets("Log").ListObjects("Tabell24")
    Set lr = listObj.ListRows(selInd + 1).Range
        
    'yes
    'make a new row at the bottom
        Set dstRow = listObj.ListRows.Add
    
        'copy selected row in some way
        lr.Copy
        'paste that into the new row
        dstRow.Range.PasteSpecial xlPasteValues
        'delete the unwanted stuff(current info)
        For i = 10 To 14
            dstRow.Range.Cells(1, i).Value = ""
        Next i
        
        'reset the stuff
        Call SetRowSource
    
        ListBoxFrontPage.ListIndex = ListBoxFrontPage.ListCount - 1
    Else
    'no
    'show error message
        MsgBox ("Feil! Du må velge en linje i lista først!")
    
    End If

End Sub

ListBox Ex - ContUpdateTextBoxes

Private Sub ListBoxFrontPage_AfterUpdate()
    
    Debug.Print "Listbox selectedindex: " & ListBoxFrontPage.ListIndex
    
    'Debug.Print ListBoxFrontPage.ListIndex
    'Update all textboxes after click
    
    If ListBoxFrontPage.ListIndex <> -1 Then
    
        Dim strDate As String
        'strDate = InputBox("Insert date in format dd/mm/yy", "User date", Format(Now(), "dd/mm/yy"))
        strDate = Format(Me.ListBoxFrontPage.Column(0), "dd/mm/yyyy")
        If IsDate(strDate) Then
            'strDate = Format(CDate(strDate), "dd/mm/yy")
            Me.TextBoxLogDate = Format(Me.ListBoxFrontPage.Column(0), "dd/mm/yyyy")
            'MsgBox strDate
        ElseIf strDate = "" Then
            Me.TextBoxLogDate.Value = Format(DateAdd("d", 1, Now()), "Short Date")
        Else
            Me.TextBoxLogDate = "##FEIL## Sjekk tabell!"
        End If
    
        Me.TextBox1 = Me.ListBoxFrontPage.Column(1)
        '..
        '..
        '..
        Me.TextBoxUsedComments = Me.ListBoxFrontPage.Column(13)
    
    End If
    
End Sub

ListBox Ex - Update

ListBoxFrontPage.RowSource = ""

'Modify list...

SetRowSource() -> ListBoxAbc.RowSource = range.... ex: ws.Range("A7:N" & Last_Row).Address

ListBox Ex - DelSelRow

Private Sub CommandButtonDeleteSelRow_Click()

    Dim answer As Integer
    
    FUAWorkBook.Activate

    Dim selectedIndex As Integer
    selectedIndex = ListBoxFrontPage.ListIndex
    'Debug.Print selectedIndex
    
    If selectedIndex > -1 Then
    
        answer = MsgBox("Helt sikker? Du kan ikke angre..", vbYesNo + vbQuestion, "Slette rad?")
        
        If answer = vbYes Then
            'delete it
            ListBoxFrontPage.RowSource = ""
    
            Dim listObj As Excel.ListObject
            Dim listRow As Excel.listRow
            
            Set listObj = Worksheets("Log").ListObjects("Tabell24")
        
            listObj.ListRows(selectedIndex + 1).Delete
            
            Call SetRowSource
        Else
            'do nothing
        End If

    Else
        MsgBox ("Feil! Du må velge en rad først!")
    End If
    
End Sub

List errors and crap

If you get into trouble, debug the stuff. Use watches, browse to the info you need and check what excel "sees".....

Cacher is the code snippet organizer for pro developers

We empower you and your team to get more done, faster

ListBox ComboBox