Dynamically Changing Column Widths on Userform Listbox

[Qwest336]

Hello all,

I'm using a Listbox to display data from a table. I use a ListCount and an Offset to loop through the range and load each cell individually. In order to hide/show certain columns in the data, I am trying to dynamically change the column widths based on the value in a column associated with the row# of the item that I have selected in the Listbox. On userform Initialize, I set the Column Widths as follows:

Private Sub UserForm_Initialize()
Dim User As String 'Name
Dim User2 As String 'Email
Dim wk As Object
Dim TWB As String
Dim FTRColumns As String
Dim NonFTRColumns As String
User = Application.UserName
User2 = Environ("UserName")
TWB = ThisWorkbook.Name
Set wk = Workbooks(TWB).Worksheets("Assigned")
FTRColumns = "100 pt;160 pt;0 pt;0 pt;0 pt;0 pt;0 pt;140 pt;140 pt;120 pt;0 pt;0 pt"
NonFTRColumns = "80 pt;160 pt;160 pt;120 pt;100 pt;100 pt;100 pt;0 pt;0 pt;0 pt;0 pt;0 pt"
Me.txtActionsRemaining.Value = "Number of Actions Remaining: " & wk.Range("CB1").Value
Me.txtTodaysDate.Value = Format(Now, "Long Date")
'Adds AssignerName into textbox
Me.txtProcessorName.Value = User & " - " & User2
With usrPHLAssignment
    If Workbooks(TWB).Worksheets("Assigned").Range("BH2") = "FTRDec" Then
        lboPHLData.ColumnWidths = FTRColumns
        frmFTRDeclines.Visible = True
        frmOtherAssignments.Visible = False
    ElseIf Workbooks(TWB).Worksheets("Assigned").Range("BH2") <> "FTRDec" Then
        lboPHLData.ColumnWidths = NonFTRColumns
        frmFTRDeclines.Visible = False
        frmOtherAssignments.Visible = True
    End If
End With

wk.Activate
Dim rng As Range
 On Error Resume Next
 Set rng = Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp)).SpecialCells(xlVisible)
 On Error GoTo 0
 If Not rng Is Nothing Then
 For Each cell In rng
 lboPHLData.AddItem cell.Value
 lboPHLData.List(lboPHLData.ListCount - 1, 1) = cell.Offset(0, 1)
 lboPHLData.List(lboPHLData.ListCount - 1, 2) = cell.Offset(0, 2)
 lboPHLData.List(lboPHLData.ListCount - 1, 3) = cell.Offset(0, 3)
 lboPHLData.List(lboPHLData.ListCount - 1, 4) = cell.Offset(0, 4)
 lboPHLData.List(lboPHLData.ListCount - 1, 5) = cell.Offset(0, 5)
 lboPHLData.List(lboPHLData.ListCount - 1, 6) = cell.Offset(0, 6)
 lboPHLData.List(lboPHLData.ListCount - 1, 7) = cell.Offset(0, 7)
 lboPHLData.List(lboPHLData.ListCount - 1, 8) = cell.Offset(0, 8)
 lboPHLData.List(lboPHLData.ListCount - 1, 9) = cell.Offset(0, 9)
 'lboPHLData.List(lboPHLData.ListCount - 1, 10) = Cell.Offset(0, 10)
 Next
 End If

End Sub

On the Change Event of the listbox, I run the following sub in a macro:

Sub AssignType()
Dim FTRColumns As String
Dim NonFTRColumns As String
Dim CombinedTogether, TWB As String
Dim wk As Object

TWB = ThisWorkbook.Name
Set wk = Workbooks(TWB).Worksheets("Assigned")
FTRColumns = "100 pt;160 pt;0 pt;0 pt;0 pt;0 pt;0 pt;140 pt;140 pt;120 pt;0 pt;0 pt"
NonFTRColumns = "80 pt;160 pt;160 pt;120 pt;100 pt;100 pt;100 pt;0 pt;0 pt;0 pt;0 pt;0 pt"

CombinedTogether = usrPHLAssignment.lboPHLData.Value & " - " & usrPHLAssignment.lboPHLData.Text
Set rgFound = Range("Combined").Find(CombinedTogether, LookIn:=xlValues)
rr = rgFound.Row
For i = rr To rr
'Print name and Requisition
'Print Assignment Type
If Workbooks(TWB).Worksheets("Assigned").Range("BH" & rr) = "Crim & Drug" Then
    usrPHLAssignment.txtAssignType.Value = "Combined Criminal & Drug Screenings"
    usrPHLAssignment.lboPHLData.ColumnWidths = NonFTRColumns
    usrPHLAssignment.frmFTRDeclines.Visible = False
    usrPHLAssignment.frmOtherAssignments.Visible = True
ElseIf Workbooks(TWB).Worksheets("Assigned").Range("BH" & rr) = "FTRDec" Then
    usrPHLAssignment.txtAssignType.Value = "FTR/Declined"
    usrPHLAssignment.lboPHLData.ColumnWidths = FTRColumns
    usrPHLAssignment.frmFTRDeclines.Visible = True
    usrPHLAssignment.frmOtherAssignments.Visible = False
ElseIf Workbooks(TWB).Worksheets("Assigned").Range("BH" & rr) = "Drug Rej" Then
    usrPHLAssignment.txtAssignType.Value = "Drug Rejections"
    usrPHLAssignment.lboPHLData.ColumnWidths = NonFTRColumns
    usrPHLAssignment.frmFTRDeclines.Visible = False
    usrPHLAssignment.frmOtherAssignments.Visible = True
ElseIf Workbooks(TWB).Worksheets("Assigned").Range("BH" & rr) = "Withdrawn" Then
    usrPHLAssignment.txtAssignType.Value = "Candidates Withdrawn"
    usrPHLAssignment.lboPHLData.ColumnWidths = NonFTRColumns
    usrPHLAssignment.frmFTRDeclines.Visible = False
    usrPHLAssignment.frmOtherAssignments.Visible = True
ElseIf Workbooks(TWB).Worksheets("Assigned").Range("BH" & rr) = "MVR Pass" Then
    usrPHLAssignment.txtAssignType.Value = "MVR Pass, Send Conditional Offer"
    usrPHLAssignment.lboPHLData.ColumnWidths = NonFTRColumns
    usrPHLAssignment.frmFTRDeclines.Visible = False
    usrPHLAssignment.frmOtherAssignments.Visible = True
ElseIf Workbooks(TWB).Worksheets("Assigned").Range("BH" & rr) = "MVR Screening" Then
    usrPHLAssignment.txtAssignType.Value = "MVR Screenings"
    usrPHLAssignment.lboPHLData.ColumnWidths = NonFTRColumns
    usrPHLAssignment.frmFTRDeclines.Visible = False
    usrPHLAssignment.frmOtherAssignments.Visible = True
End If
Next

 

End Sub

The frames that hold the labels change correctly (e.g. frmFTRDeclines). However, even though the Immediate window shows that the Column Width of the listbox has changed from FTRColumns to NonFTRColumns, the Listbox is not refreshing to show/hide the different columns.

I've tried to set the Listbox visible property from false to true. I've tried to repaint the userform. I've tried to SetFocus back on the listbox. I don't know what to do!

Any and all help is greatly appreciated!

 

[Qwest336]

*BUMP*

Anyone with an idea?