VBA Challenge: 2 user forms, "saving" columns via a global collection, hiding columns dynamically...

[Steve_K]

Hi all,

Here's my current challenge:

I have a table with 53+ columns. These columns are broken down into 4 large groups: General, Leads, Pipeline, Backlog, and Premiums.

In order to make the table easier to edit, I've created a user form which has a drop down containing each of the four options and an OK button. When a user clicks OK, the columns are hidden so as to show only the relevant columns per group. For example, General may have A:D, Leads may have H:K, Pipeline may have S:U and Premiums may have V:BA.

As of now, this first userform functions fine, however all of the columns and their hiding actions are hard-coded which makes the code less than ideal.

I am now working on a second user form through which a user can configure the columns he or she wants to have shown per group. To do this, I created a table on another sheet in which I have all 53 possible columns names in one column. I pull these column names into a user form which has 5 multi-select list boxes. The idea is that the user can choose any combination of all fields per group via these list boxes.

Ideally, what would happen on a high level is this:

1. User configures the columns he or she wants per group using userform 2.
2. Column configuration saved into something (I was thinking 5 "global" collection objects, one for each group of columns)
3. User then opens userform 1 and selects which of the five groups to view in the table.
4. Based on the collection(s) stored, the proper columns are hidden based on the users configuration choices in step 1.

I have discrete pieces of this constructed - the large table with its column-hiding userform works well, and the user form with the column configurations is also built. At this point, I need some advice on how to store the column configurations so they can be dynamically referenced by the click of another userform button.

I hope this makes sense! Please let me know if any clarification would help and I'll be happy to share what I can. Here's the code for each separate piece. It's messy because I'm a beginner at VBA, so I apologize in advance:

Column Configuration User Form

Private Sub UserForm_Initialize()
    Dim Ws As Worksheet
    Set Ws = ActiveSheet
   
    ' I wish I knew how to do this more effectively, i.e. in a loop...

    lbGeneral.RowSource = Ws.Range("ColumnsTable[General]").Address
    lbLeads.RowSource = Ws.Range("ColumnsTable[Leads]").Address
    lbPipeline.RowSource = Ws.Range("ColumnsTable[Pipeline]").Address
    lbBacklog.RowSource = Ws.Range("ColumnsTable[Backlog]").Address
    lbPremiums.RowSource = Ws.Range("ColumnsTable[Premiums]").Address
    
End Sub

Private Sub cmdClose_Click()
    Unload Me
End Sub
Private Sub cmdSave_Click()
    Dim columns As Collection
    
    With configForm.lbGeneral
        For i = 0 To .ListCount - 1
            If Not .Selected(i) Then
                ' This bit doesn't work at all...
                columns.Add (.List(i))
            End If
        Next
        MsgBox columns
    End With
    
End Sub

Column Hiding and Filtering

Private Sub UserForm_Initialize()
' Builds the Dropdown with the 5 groups and an "All" option
    With Me.cmbOpps
        .AddItem "All"
        .AddItem "Leads"
        .AddItem "Pipeline"
        .AddItem "Backlog"
        .AddItem "Premiums"
        .ListIndex = 0
    End With
    Me.optEdit.Value = True
End Sub


Private Sub btnClose_Click()
    Unload Me
End Sub


Private Sub btnReset_Click()
    ' Runs resetCols which clears any filters, unhides all columns and selects the first non-empty cell
    resetCols
    Me.cmbOpps.Value = "All"
    showFirstBlankCell
End Sub


Private Sub freezePanes(val As Range)
    ' Since columns will be hidden, we need to be able to specify what cell to use to freeze the top and leftmost cells so that the cells in the General group always show to the left no matter what.
    ActiveWindow.freezePanes = False
    val.Select
    ActiveWindow.freezePanes = True
End Sub


Private Sub btnOk_Click()
    Dim Edit As Boolean
    Dim View As Boolean
    Dim OppType As String
    Edit = Me.optEdit.Value
    View = Me.optView.Value
    OppType = Me.cmbOpps.Value
    
    Application.ScreenUpdating = False
    
    ' Function to hide columns in the General group

    hideGeneralCols


    ' The form has two radio buttons "Edit" and "View".  If "Edit" is selected, only columns are hidden.  If "View" is selected, columns are hidden like Edit, and additional filters are applied.

    If Edit = True Or View = True Then
    
        If OppType = "All" Then
            resetCols
        ElseIf OppType = "Leads" Then
            hideLeadCols
        ElseIf OppType = "Pipeline" Then
            hidePipelineCols
        ElseIf OppType = "Backlog" Then
            hideBacklogCols
        ElseIf OppType = "Premiums" Then
            hidePremiumCols
        End If
        
        showFirstBlankCell
        
        If View = True Then
        
            If OppType = "All" Then
                resetCols
            ElseIf OppType = "Leads" Then
                hideLeadCols
                ActiveSheet.ListObjects("Opportunities").Range.AutoFilter Field:=2, _
                Criteria1:="Leads"
            ElseIf OppType = "Pipeline" Then
                hidePipelineCols
                ActiveSheet.ListObjects("Opportunities").Range.AutoFilter Field:=2, _
                Criteria1:="Pipeline"
            ElseIf OppType = "Backlog" Then
                hideBacklogCols
                ActiveSheet.ListObjects("Opportunities").Range.AutoFilter Field:=2, _
                Criteria1:="Backlog"
                ActiveSheet.ListObjects("Opportunities").Range.AutoFilter Field:=34, _
                Criteria1:=Array("Delivered", "In Progress", "Not Started", "On Hold"), Operator:= _
                xlFilterValues
            ElseIf OppType = "Premiums" Then
                hidePremiumCols
                ActiveSheet.ListObjects("Opportunities").Range.AutoFilter Field:=2, _
                Criteria1:="Premiums"
            End If
        
            showFirstFilledCell
        
        End If
    End If
End Sub


Function showFirstBlankCell()
' Shows the first blank cell and scrolls the screen while ScreenUpdating is set to False

    Range("A65536").End(xlUp).Activate
    ActiveWindow.ScrollRow = 5
End Function


Function showFirstFilledCell()
' Shows the first filled cell after a filter has been applied in "View" mode...

    Sheet1.Cells(GetFilteredRangeTopRow, "A").Select
End Function


Function showAllRecords()
' Removes all filters, used in resetCols...
    If ActiveSheet.FilterMode Then
        ActiveSheet.ShowAllData
    End If
End Function


Function GetFilteredRangeTopRow() As Long
' Used in showFirstBlankCell...
  Dim HeaderRow As Long, LastFilterRow As Long
  With ActiveSheet
    HeaderRow = .AutoFilter.Range(1).Row
    LastFilterRow = .Range(Split(.AutoFilter.Range.Address, ":")(1)).Row
    GetFilteredRangeTopRow = .Range(.Rows(HeaderRow + 1), .Rows(Rows.Count)).SpecialCells(xlCellTypeVisible)(1).Row
    If GetFilteredRangeTopRow = LastFilterRow + 1 Then GetFilteredRangeTopRow = 0
  End With
End Function


Function resetCols()
    Application.ScreenUpdating = False
    Range("A:BA").EntireColumn.Hidden = False
    showAllRecords
    Call freezePanes(Range("A4"))
End Function


Function hideGeneralCols()
' Hides all cols in the General group -- these ranges would be dynamic from the column configuration settings.
    resetCols
    Range("B:B").EntireColumn.Hidden = True
    Range("D:H").EntireColumn.Hidden = True
End Function


Function hideLeadCols()
' Hides all cols in the Leads group -- these ranges would be dynamic from the column configuration settings.
    Range("V:BA").EntireColumn.Hidden = True
    Call freezePanes(Range("J4"))
End Function


Function hidePipelineCols()
' Hides all cols in the Pipeline group -- these ranges would be dynamic from the column configuration settings.
    Range("J:U").EntireColumn.Hidden = True
    Range("AG:BA").EntireColumn.Hidden = True
    Call freezePanes(Range("V4"))
End Function


Function hideBacklogCols()
' Hides all cols in the Backlog group -- these ranges would be dynamic from the column configuration settings.
    Range("J:AG").EntireColumn.Hidden = True
    Range("AO:BA").EntireColumn.Hidden = True
    Call freezePanes(Range("AH4"))
End Function


Function hidePremiumCols()
' Hides all cols in the Premium group -- these ranges would be dynamic from the column configuration settings.
    Range("J:AO").EntireColumn.Hidden = True
    Call freezePanes(Range("AP4"))
End Function

I realize this is pretty long and may well be overly ambitious for a VBA-newbie. I also realize my code is probably extremely horrible and any advice for making it more efficient and better structured (along with helping it to work) is very much appreciated.

Above all, thanks a ton for reading all of this.

Steve

 

[sujon]

Bump