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:
On the Change Event of the listbox, I run the following sub in a macro:
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!
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!