Dynamically Changing Column Widths on Userform Listbox

Qwest336

Board Regular
Joined
Jun 24, 2015
Messages
53
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:

Code:
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:
Code:
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!
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Forum statistics

Threads
1,148,294
Messages
5,745,944
Members
423,985
Latest member
sayed manzar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top