Excel VBA Runtime Error -2147024809 (80070057)'

excel7777

New Member
Joined
Jan 25, 2017
Messages
3
Hi,

I am working on a listbox that I tried to expand from 15 columns to 29 When I enter information in the form, it will populate the information, but the listbox still only shows 15 columns. I also entered list boxes and radio fields and it doesn't appear to be picking up the information.

I am getting the Run-Time Error -2147024809 (80070057) after every entry. It says it could not get the column property. Invalid argument. I'm using Excel 2016. I have searched through the forum trying to resolve this but have had no luck so far. Will someone please provide me with ideas on what I am doing wrong? I'm not sure how to get it running smoothly without errors fully populate all the information in my spreadsheet. I truly appreciate all of your help.

Code:
Private Sub CheckBox1_Click()
Dim r As Long
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With
ListBox1.ListIndex = -1
If CheckBox1.Value = True Then
ListBox1.MultiSelect = fmMultiSelectMulti
    For r = 0 To ListBox1.ListCount - 1
        ListBox1.Selected(r) = True
    Next r
   Else
ListBox1.MultiSelect = fmMultiSelectSingle
      For r = 0 To ListBox1.ListCount - 1
        ListBox1.Selected(r) = False
    Next r
    End If
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
Private Sub Cmdbutton1_Click()
If TextBox51 = 0 Then
Exit Sub
End If
If ListBox1.ListIndex = 0 Then
MsgBox "First Record", vbCritical, ""
Exit Sub
Else
On Error Resume Next
TextBox51 = TextBox51 - 1
With Me.ListBox1
        .ListIndex = .ListIndex - 1
End With
End If
End Sub
 
Private Sub Cmdbutton2_Click()
If ListBox1.ListIndex = ListBox1.ListCount - 1 Then
MsgBox "Last Record", vbCritical, ""
Exit Sub
Else
On Error Resume Next
TextBox51 = TextBox51 + 1
With Me.ListBox1
        .ListIndex = .ListIndex + 1
End With
End If
End Sub
 
Private Sub Cmdbutton3_Click() 'FIRST RECORD BUTTON
On Error Resume Next
Application.ScreenUpdating = False
ListBox1.ListIndex = 0
Application.ScreenUpdating = True
End Sub
 
Private Sub Cmdbutton4_Click() 'LAST RECORD BUTTON
On Error Resume Next
Application.ScreenUpdating = False
ListBox1.ListIndex = ListBox1.ListCount - 1
Application.ScreenUpdating = True
End Sub
 
Private Sub ComboBox1_Change()
If ComboBox1.ListIndex = 3 Then
ComboBox2.Visible = True
ComboBox2.ListIndex = 0
Else
ComboBox2.Visible = False
End If
End Sub
 
Private Sub ComboBox1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal y As Single)
    Me.ComboBox1.DropDown
End Sub
 
Private Sub ComboBox3_Change()
 
End Sub
 
Private Sub ComboBox2_Change()
 
End Sub
 
Private Sub CommandButton1_Click() 'Saving Button
Dim sonsat, ver As Long
 
If TextBox1.Value = "" Then
        MsgBox "Please enter the Grantee.", vbExclamation, ""
        TextBox1.SetFocus
        Exit Sub
    End If
    If TextBox5.Value = "" Then
        MsgBox "Please enter the Document #.", vbExclamation, ""
        TextBox5.SetFocus
        Exit Sub
    End If
    If TextBox4.Value = "" Then
        MsgBox "Please enter the Finding #.", vbExclamation, ""
        TextBox4.SetFocus
        Exit Sub
    End If
    If TextBox28.Value = "" Then
        MsgBox "Please enter the Finding Type.", vbExclamation, ""
        TextBox28.SetFocus
        Exit Sub
    End If
If TextBox11.Value = "" Then
        MsgBox "Please enter the SharePoint Link.", vbExclamation, ""
        TextBox11.SetFocus
        Exit Sub
        End If
        If ListBox4.Text = "" Then
        MsgBox "Please indicate if this finding is a repeat.", vbExclamation, ""
        ListBox4.SetFocus
        Exit Sub
    End If
    For ver = 2 To Cells(Rows.Count, 1).End(xlUp).Row
 
If Cells(ver, "A") = TextBox4 Then
MsgBox "This Finding # has already been recorded.  If the number is not incorrectly typed, please cancel and update the recorded entry.", vbInformation, ""
TextBox1 = Empty
Exit Sub: End If: Next
   
sonsat = Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row + 1
Call Main 'Progress Bar
 
Cells(sonsat, 1) = TextBox1.Text
Cells(sonsat, 2) = TextBox2.Text
Cells(sonsat, 3) = ListBox2.Text
Cells(sonsat, 4) = TextBox23.Text
Cells(sonsat, 5) = TextBox4.Text
Cells(sonsat, 6) = TextBox27.Text
Cells(sonsat, 7) = TextBox28.Text
Cells(sonsat, 8) = TextBox6.Text
Cells(sonsat, 9) = TextBox9.Text
Cells(sonsat, 10) = TextBox10.Text
Cells(sonsat, 11) = TextBox11.Text
Cells(sonsat, 12) = TextBox16.Value
Cells(sonsat, 13) = TextBox29.Value
Cells(sonsat, 15) = TextBox30.Value
Cells(sonsat, 16) = TextBox31.Value
Cells(sonsat, 18) = TextBox33.Value
Cells(sonsat, 19) = TextBox32.Value
Cells(sonsat, 21) = TextBox35.Value
Cells(sonsat, 22) = TextBox34.Value
Cells(sonsat, 24) = TextBox36.Text
Cells(sonsat, 25) = ListBox3.Text
Cells(sonsat, 26) = TextBox5.Value
Cells(sonsat, 27) = TextBox8.Value
Cells(sonsat, 28) = TextBox7.Value
Cells(sonsat, 29) = ListBox4.Text
 
If OptionButton4.Value = True Then
    Cells(sonsat, 14).Value = "Yes"
Else
    Cells(sonsat, 14).Value = "No"
End If
 
If OptionButton6.Value = True Then
    Cells(sonsat, 17).Value = "Yes"
Else
    Cells(sonsat, 17).Value = "No"
End If
 
If OptionButton9.Value = True Then
    Cells(sonsat, 20).Value = "Yes"
Else
    Cells(sonsat, 20).Value = "No"
End If
 
If OptionButton11.Value = True Then
    Cells(sonsat, 23).Value = "Yes"
Else
    Cells(sonsat, 23).Value = "No"
End If
 
MsgBox "The new finding has been successfully submitted.", vbApplicationModal, ""
ListBox1.List = Sheets("Data").Range("A2:AA" & Sheets("data").Cells(Rows.Count, 1).End(xlUp).Row).Value
ListBox1.ListIndex = ListBox1.ListCount - 1
TextBox50.Value = ListBox1.ListCount
End Sub
 
Private Sub CommandButton13_Click()
 
End Sub
 
Private Sub CommandButton14_Click()
 
End Sub
 
Private Sub CommandButton2_Click() 'Update Button
Dim sonsat As Long
 
If ListBox1.ListIndex = -1 Then
MsgBox "Choose an item", vbExclamation, ""
Exit Sub
End If
lastrow = Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row
Sheets("Data").Range("A2:A" & lastrow).Find(What:=ListBox1.Text, LookIn:=xlValues, LookAt:=xlWhole).Activate
sonsat = ActiveCell.Row
Cells(sonsat, 1) = TextBox1.Text
Cells(sonsat, 2) = TextBox2.Text
Cells(sonsat, 3) = ListBox2.Text
Cells(sonsat, 4) = TextBox23.Text
Cells(sonsat, 5) = TextBox4.Text
Cells(sonsat, 6) = TextBox27.Text
Cells(sonsat, 7) = TextBox28.Text
Cells(sonsat, 8) = TextBox6.Text
Cells(sonsat, 9) = TextBox9.Text
Cells(sonsat, 10) = TextBox10.Text
Cells(sonsat, 11) = TextBox11.Text
Cells(sonsat, 12) = TextBox16.Value
Cells(sonsat, 13) = TextBox29.Value
Cells(sonsat, 15) = TextBox30.Value
Cells(sonsat, 16) = TextBox31.Value
Cells(sonsat, 18) = TextBox33.Value
Cells(sonsat, 19) = TextBox32.Value
Cells(sonsat, 21) = TextBox35.Value
Cells(sonsat, 22) = TextBox34.Value
Cells(sonsat, 24) = TextBox36.Text
Cells(sonsat, 25) = ListBox3.Text
Cells(sonsat, 26) = TextBox5.Value
Cells(sonsat, 27) = TextBox8.Value
Cells(sonsat, 28) = TextBox7.Value
Cells(sonsat, 29) = ListBox4.Text
 
If OptionButton4.Value = True Then
    Cells(sonsat, 14).Value = "Yes"
Else
    Cells(sonsat, 14).Value = "No"
End If
 
If OptionButton6.Value = True Then
    Cells(sonsat, 17).Value = "Yes"
Else
    Cells(sonsat, 17).Value = "No"
End If
 
If OptionButton9.Value = True Then
    Cells(sonsat, 20).Value = "Yes"
Else
    Cells(sonsat, 20).Value = "No"
End If
 
If OptionButton11.Value = True Then
    Cells(sonsat, 23).Value = "Yes"
Else
    Cells(sonsat, 23).Value = "No"
End If
 
Call Main 'Progress Bar
MsgBox "Item has been updated", vbApplicationModal, ""
ListBox1.List = Sheets("Data").Range("A2:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).Value
End Sub
 
Private Sub CommandButton3_Click() ' Delete Button
   Dim sil As Long
   If ListBox1.ListIndex = -1 Then
   MsgBox "Choose an entry", vbExclamation, ""
   Exit Sub
   End If
   If ListBox1.ListIndex >= 0 Then
    cevap = MsgBox("Entry will be deleted. ... Are you sure ?", vbYesNo, "")
If cevap = vbYes Then
   Sheets("Data").Range("A:A").Find(ListBox1.Text).Activate
sil = ActiveCell.Row
   Sheets("Data").Rows(sil).Delete
                    
        End If
        End If
 Call Main 'Progress Bar
      
For a = 1 To 49
Controls("textbox" & a) = ""
 
 
ListBox1.List = Sheets("Data").Range("A2:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).Value
TextBox50.Value = ListBox1.ListCount
End Sub
 
Private Sub CommandButton4_Click() 'CLEAR BUTTON
Dim del As Control
Call Main 'Progress Bar
    For Each del In UserForm1.Controls
        If TypeName(del) = "TextBox" Or TypeName(del) = "ComboBox" Then
            del.Text = Empty
        End If
    Next del
   
ListBox1.Clear
Label15.Caption = ""
 
End Sub
 
Private Sub CommandButton5_Click() 'Search Button
Dim sat, s As Long
 
Sheets("Data").Activate
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With
OptionButton1.Value = True
TextBox51.Value = Empty
If TextBox52.Value = "" Then
MsgBox "Please enter a value", vbExclamation, ""
TextBox52.SetFocus
Exit Sub: End If
 
If ComboBox1.Value = "" Or ComboBox1.Value = "-" Then
MsgBox "Choose a Filter Field", vbExclamation, ""
ComboBox1.SetFocus
Exit Sub: End If
 
 
Call Main 'Progress Bar
 
Select Case ComboBox1.Value
Case "Grantee"
ActiveSheet.AutoFilterMode = False
ListBox1.Clear
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=1, Criteria1:=TextBox52.Value & "*", Operator:=xlAnd
Sheets("FilteredData").Cells.Clear
 
If ActiveSheet.Range("A1").CurrentRegion.Columns(1).SpecialCells(xlCellTypeVisible).Count <= 1 Then
GoTo here:
Else
ActiveSheet.Range("A2:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
        Destination:=Sheets("FilteredData").Range("A2")
End If
Sheets("FilteredData").Columns.AutoFit
ListBox1.List = Sheets("FilteredData").Range("A2:O" & Sheets("FilteredData").Cells(Rows.Count, 1).End(xlUp).Row).Value
here:
ActiveSheet.AutoFilterMode = False
Call Clear
 
 
Case "Report Number"
ActiveSheet.AutoFilterMode = False
ListBox1.Clear
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=2, Criteria1:=TextBox52.Value & "*", Operator:=xlAnd
Sheets("FilteredData").Cells.Clear
 
If ActiveSheet.Range("A1").CurrentRegion.Columns(1).SpecialCells(xlCellTypeVisible).Count <= 1 Then
GoTo here2:
Else
ActiveSheet.Range("A2:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
        Destination:=Sheets("FilteredData").Range("A2")
End If
Sheets("FilteredData").Columns.AutoFit
ListBox1.List = Sheets("FilteredData").Range("A2:O" & Sheets("FilteredData").Cells(Rows.Count, 1).End(xlUp).Row).Value
here2:
ActiveSheet.AutoFilterMode = False
Call Clear
 
Case "Audit Type"
ActiveSheet.AutoFilterMode = False
ListBox1.Clear
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=4, Criteria1:=TextBox52.Value & "*", Operator:=xlAnd
Sheets("FilteredData").Cells.Clear
 
If ActiveSheet.Range("A1").CurrentRegion.Columns(1).SpecialCells(xlCellTypeVisible).Count <= 1 Then
GoTo here3:
Else
ActiveSheet.Range("A2:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
        Destination:=Sheets("FilteredData").Range("A2")
End If
Sheets("FilteredData").Columns.AutoFit
ListBox1.List = Sheets("FilteredData").Range("A2:O" & Sheets("FilteredData").Cells(Rows.Count, 1).End(xlUp).Row).Value
here3:
ActiveSheet.AutoFilterMode = False
Call Clear
 
Case "Repeat Finding"
ActiveSheet.AutoFilterMode = False
ListBox1.Clear
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=29, Criteria1:=TextBox52.Value & "*", Operator:=xlAnd
Sheets("FilteredData").Cells.Clear
 
If ActiveSheet.Range("A1").CurrentRegion.Columns(1).SpecialCells(xlCellTypeVisible).Count <= 1 Then
GoTo here23:
Else
ActiveSheet.Range("A2:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
        Destination:=Sheets("FilteredData").Range("A2")
End If
Sheets("FilteredData").Columns.AutoFit
ListBox1.List = Sheets("FilteredData").Range("A2:O" & Sheets("FilteredData").Cells(Rows.Count, 1).End(xlUp).Row).Value
here23:
ActiveSheet.AutoFilterMode = False
Call Clear
 
Case "Finding #"
ActiveSheet.AutoFilterMode = False
ListBox1.Clear
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=5, Criteria1:=TextBox52.Value & "*", Operator:=xlAnd
Sheets("FilteredData").Cells.Clear
 
If ActiveSheet.Range("A1").CurrentRegion.Columns(1).SpecialCells(xlCellTypeVisible).Count <= 1 Then
GoTo here4:
Else
ActiveSheet.Range("A2:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
        Destination:=Sheets("FilteredData").Range("A2")
End If
Sheets("FilteredData").Columns.AutoFit
ListBox1.List = Sheets("FilteredData").Range("A2:O" & Sheets("FilteredData").Cells(Rows.Count, 1).End(xlUp).Row).Value
here4:
ActiveSheet.AutoFilterMode = False
Call Clear
 
Case "Finding Type"
ActiveSheet.AutoFilterMode = False
ListBox1.Clear
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=7, Criteria1:=TextBox52.Value & "*", Operator:=xlAnd
Sheets("FilteredData").Cells.Clear
 
If ActiveSheet.Range("A1").CurrentRegion.Columns(1).SpecialCells(xlCellTypeVisible).Count <= 1 Then
GoTo here5:
Else
ActiveSheet.Range("A2:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
        Destination:=Sheets("FilteredData").Range("A2")
End If
Sheets("FilteredData").Columns.AutoFit
ListBox1.List = Sheets("FilteredData").Range("A2:O" & Sheets("FilteredData").Cells(Rows.Count, 1).End(xlUp).Row).Value
here5:
ActiveSheet.AutoFilterMode = False
Call Clear
 
Case "Finding Details"
ActiveSheet.AutoFilterMode = False
ListBox1.Clear
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=8, Criteria1:=TextBox52.Value & "*", Operator:=xlAnd
Sheets("FilteredData").Cells.Clear
 
If ActiveSheet.Range("A1").CurrentRegion.Columns(1).SpecialCells(xlCellTypeVisible).Count <= 1 Then
GoTo here6:
Else
ActiveSheet.Range("A2:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
        Destination:=Sheets("FilteredData").Range("A2")
End If
Sheets("FilteredData").Columns.AutoFit
ListBox1.List = Sheets("FilteredData").Range("A2:O" & Sheets("FilteredData").Cells(Rows.Count, 1).End(xlUp).Row).Value
here6:
ActiveSheet.AutoFilterMode = False
Call Clear
 
Case "Recommendation"
ActiveSheet.AutoFilterMode = False
ListBox1.Clear
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=9, Criteria1:=TextBox52.Value & "*", Operator:=xlAnd
Sheets("FilteredData").Cells.Clear
 
If ActiveSheet.Range("A1").CurrentRegion.Columns(1).SpecialCells(xlCellTypeVisible).Count <= 1 Then
GoTo here7:
Else
ActiveSheet.Range("A2:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
        Destination:=Sheets("FilteredData").Range("A2")
End If
Sheets("FilteredData").Columns.AutoFit
ListBox1.List = Sheets("FilteredData").Range("A2:O" & Sheets("FilteredData").Cells(Rows.Count, 1).End(xlUp).Row).Value
here7:
ActiveSheet.AutoFilterMode = False
Call Clear
 
Case "Financial Analyst"
ActiveSheet.AutoFilterMode = False
ListBox1.Clear
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=10, Criteria1:=TextBox52.Value & "*", Operator:=xlAnd
Sheets("FilteredData").Cells.Clear
 
If ActiveSheet.Range("A1").CurrentRegion.Columns(1).SpecialCells(xlCellTypeVisible).Count <= 1 Then
GoTo here8:
Else
ActiveSheet.Range("A2:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
        Destination:=Sheets("FilteredData").Range("A2")
End If
Sheets("FilteredData").Columns.AutoFit
ListBox1.List = Sheets("FilteredData").Range("A2:O" & Sheets("FilteredData").Cells(Rows.Count, 1).End(xlUp).Row).Value
here8:
ActiveSheet.AutoFilterMode = False
Call Clear
 
Case "Notes"
ActiveSheet.AutoFilterMode = False
ListBox1.Clear
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=23, Criteria1:=TextBox52.Value & "*", Operator:=xlAnd
Sheets("FilteredData").Cells.Clear
 
If ActiveSheet.Range("A1").CurrentRegion.Columns(1).SpecialCells(xlCellTypeVisible).Count <= 1 Then
GoTo here9:
Else
ActiveSheet.Range("A2:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
        Destination:=Sheets("FilteredData").Range("A2")
End If
Sheets("FilteredData").Columns.AutoFit
ListBox1.List = Sheets("FilteredData").Range("A2:O" & Sheets("FilteredData").Cells(Rows.Count, 1).End(xlUp).Row).Value
here9:
ActiveSheet.AutoFilterMode = False
Call Clear
 
Case "Recommendation Code"
ActiveSheet.AutoFilterMode = False
ListBox1.Clear
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=6, Criteria1:=TextBox52.Value & "*", Operator:=xlAnd
Sheets("FilteredData").Cells.Clear
 
If ActiveSheet.Range("A1").CurrentRegion.Columns(1).SpecialCells(xlCellTypeVisible).Count <= 1 Then
GoTo here10:
Else
ActiveSheet.Range("A2:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
        Destination:=Sheets("FilteredData").Range("A2")
End If
Sheets("FilteredData").Columns.AutoFit
ListBox1.List = Sheets("FilteredData").Range("A2:O" & Sheets("FilteredData").Cells(Rows.Count, 1).End(xlUp).Row).Value
here10:
ActiveSheet.AutoFilterMode = False
Call Clear
 
Case "Audit Type"
ActiveSheet.AutoFilterMode = False
ListBox1.Clear
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=4, Criteria1:=TextBox52.Value & "*", Operator:=xlAnd
Sheets("FilteredData").Cells.Clear
 
If ActiveSheet.Range("A1").CurrentRegion.Columns(1).SpecialCells(xlCellTypeVisible).Count <= 1 Then
GoTo here11:
Else
ActiveSheet.Range("A2:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
        Destination:=Sheets("FilteredData").Range("A2")
End If
Sheets("FilteredData").Columns.AutoFit
ListBox1.List = Sheets("FilteredData").Range("A2:O" & Sheets("FilteredData").Cells(Rows.Count, 1).End(xlUp).Row).Value
here11:
ActiveSheet.AutoFilterMode = False
Call Clear
 
Case "Posted to SP-Grantee"
ActiveSheet.AutoFilterMode = False
ListBox1.Clear
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=17, Criteria1:=TextBox52.Value & "*", Operator:=xlAnd
Sheets("FilteredData").Cells.Clear
 
If ActiveSheet.Range("A1").CurrentRegion.Columns(1).SpecialCells(xlCellTypeVisible).Count <= 1 Then
GoTo here12:
Else
ActiveSheet.Range("A2:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
        Destination:=Sheets("FilteredData").Range("A2")
End If
Sheets("FilteredData").Columns.AutoFit
ListBox1.List = Sheets("FilteredData").Range("A2:O" & Sheets("FilteredData").Cells(Rows.Count, 1).End(xlUp).Row).Value
here12:
ActiveSheet.AutoFilterMode = False
Call Clear
 
Case "Posted to SP-RFM"
ActiveSheet.AutoFilterMode = False
ListBox1.Clear
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=20, Criteria1:=TextBox52.Value & "*", Operator:=xlAnd
Sheets("FilteredData").Cells.Clear
 
If ActiveSheet.Range("A1").CurrentRegion.Columns(1).SpecialCells(xlCellTypeVisible).Count <= 1 Then
GoTo here13:
Else
ActiveSheet.Range("A2:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
        Destination:=Sheets("FilteredData").Range("A2")
End If
Sheets("FilteredData").Columns.AutoFit
ListBox1.List = Sheets("FilteredData").Range("A2:O" & Sheets("FilteredData").Cells(Rows.Count, 1).End(xlUp).Row).Value
here13:
ActiveSheet.AutoFilterMode = False
Call Clear
 
Case "Posted to SP-ROA"
ActiveSheet.AutoFilterMode = False
ListBox1.Clear
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=23, Criteria1:=TextBox52.Value & "*", Operator:=xlAnd
Sheets("FilteredData").Cells.Clear
 
If ActiveSheet.Range("A1").CurrentRegion.Columns(1).SpecialCells(xlCellTypeVisible).Count <= 1 Then
GoTo here14:
Else
ActiveSheet.Range("A2:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
        Destination:=Sheets("FilteredData").Range("A2")
End If
Sheets("FilteredData").Columns.AutoFit
ListBox1.List = Sheets("FilteredData").Range("A2:O" & Sheets("FilteredData").Cells(Rows.Count, 1).End(xlUp).Row).Value
here14:
ActiveSheet.AutoFilterMode = False
Call Clear
 
Case "Posted to SP-Boss Letter"
ActiveSheet.AutoFilterMode = False
ListBox1.Clear
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=14, Criteria1:=TextBox52.Value & "*", Operator:=xlAnd
Sheets("FilteredData").Cells.Clear
 
If ActiveSheet.Range("A1").CurrentRegion.Columns(1).SpecialCells(xlCellTypeVisible).Count <= 1 Then
GoTo here16:
Else
ActiveSheet.Range("A2:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
        Destination:=Sheets("FilteredData").Range("A2")
End If
Sheets("FilteredData").Columns.AutoFit
ListBox1.List = Sheets("FilteredData").Range("A2:O" & Sheets("FilteredData").Cells(Rows.Count, 1).End(xlUp).Row).Value
here16:
ActiveSheet.AutoFilterMode = False
Call Clear
 
Case "Finding's Current Phase"
ActiveSheet.AutoFilterMode = False
ListBox1.Clear
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=25, Criteria1:=TextBox52.Value & "*", Operator:=xlAnd
Sheets("FilteredData").Cells.Clear
 
If ActiveSheet.Range("A1").CurrentRegion.Columns(1).SpecialCells(xlCellTypeVisible).Count <= 1 Then
GoTo here17:
Else
ActiveSheet.Range("A2:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
        Destination:=Sheets("FilteredData").Range("A2")
End If
Sheets("FilteredData").Columns.AutoFit
ListBox1.List = Sheets("FilteredData").Range("A2:O" & Sheets("FilteredData").Cells(Rows.Count, 1).End(xlUp).Row).Value
here17:
ActiveSheet.AutoFilterMode = False
Call Clear
 
Case "Fiscal Year"
ActiveSheet.AutoFilterMode = False
ListBox1.Clear
Select Case ComboBox2.ListIndex
Case "0"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=4, Criteria1:="=" & TextBox52.Value
Case "1"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=4, Criteria1:="<" & TextBox52.Value
Case "2"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=4, Criteria1:=">" & TextBox52.Value
Case "3"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=4, Criteria1:="<=" & TextBox52.Value
Case "4"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=4, Criteria1:=">=" & TextBox52.Value
End Select
 
Case "Boss Letter Received Date"
ActiveSheet.AutoFilterMode = False
ListBox1.Clear
Select Case ComboBox2.ListIndex
Case "0"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=12, Criteria1:="=" & Format([datecell], "mm/dd/yyyy")
Case "1"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=12, Criteria1:="<" & Format([datecell], "mm/dd/yyyy")
Case "2"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=12, Criteria1:=">" & Format([datecell], "mm/dd/yyyy")
Case "3"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=12, Criteria1:="<=" & Format([datecell], "mm/dd/yyyy")
Case "4"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=12, Criteria1:=">=" & Format([datecell], "mm/dd/yyyy")
End Select
 
Case "Boss Final Due Date"
ActiveSheet.AutoFilterMode = False
ListBox1.Clear
Select Case ComboBox2.ListIndex
Case "0"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=13, Criteria1:="=" & Format([datecell], "mm/dd/yyyy")
Case "1"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=13, Criteria1:="<" & Format([datecell], "mm/dd/yyyy")
Case "2"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=13, Criteria1:=">" & Format([datecell], "mm/dd/yyyy")
Case "3"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=13, Criteria1:="<=" & Format([datecell], "mm/dd/yyyy")
Case "4"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=13, Criteria1:=">=" & Format([datecell], "mm/dd/yyyy")
End Select
 
Case "Due Date-Grantee"
ActiveSheet.AutoFilterMode = False
ListBox1.Clear
Select Case ComboBox2.ListIndex
Case "0"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=15, Criteria1:="=" & Format([datecell], "mm/dd/yyyy")
Case "1"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=15, Criteria1:="<" & Format([datecell], "mm/dd/yyyy")
Case "2"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=15, Criteria1:=">" & Format([datecell], "mm/dd/yyyy")
Case "3"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=15, Criteria1:="<=" & Format([datecell], "mm/dd/yyyy")
Case "4"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=15, Criteria1:=">=" & Format([datecell], "mm/dd/yyyy")
End Select
 
Case "Submission Date-Grantee"
ActiveSheet.AutoFilterMode = False
ListBox1.Clear
Select Case ComboBox2.ListIndex
Case "0"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=16, Criteria1:="=" & Format([datecell], "mm/dd/yyyy")
Case "1"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=16, Criteria1:="<" & Format([datecell], "mm/dd/yyyy")
Case "2"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=16, Criteria1:=">" & Format([datecell], "mm/dd/yyyy")
Case "3"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=16, Criteria1:="<=" & Format([datecell], "mm/dd/yyyy")
Case "4"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=16, Criteria1:=">=" & Format([datecell], "mm/dd/yyyy")
End Select
Sheets("FilteredData").Cells.Clear
 
Case "Due Date-RFM"
ActiveSheet.AutoFilterMode = False
ListBox1.Clear
Select Case ComboBox2.ListIndex
Case "0"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=18, Criteria1:="=" & Format([datecell], "mm/dd/yyyy")
Case "1"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=18, Criteria1:="<" & Format([datecell], "mm/dd/yyyy")
Case "2"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=18, Criteria1:=">" & Format([datecell], "mm/dd/yyyy")
Case "3"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=18, Criteria1:="<=" & Format([datecell], "mm/dd/yyyy")
Case "4"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=18, Criteria1:=">=" & Format([datecell], "mm/dd/yyyy")
End Select
 
Case "Submission Date-RFM"
ActiveSheet.AutoFilterMode = False
ListBox1.Clear
Select Case ComboBox2.ListIndex
Case "0"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=19, Criteria1:="=" & Format([datecell], "mm/dd/yyyy")
Case "1"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=19, Criteria1:="<" & Format([datecell], "mm/dd/yyyy")
Case "2"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=19, Criteria1:=">" & Format([datecell], "mm/dd/yyyy")
Case "3"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=19, Criteria1:="<=" & Format([datecell], "mm/dd/yyyy")
Case "4"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=19, Criteria1:=">=" & Format([datecell], "mm/dd/yyyy")
End Select
 
Case "Due Date-ROA"
ActiveSheet.AutoFilterMode = False
ListBox1.Clear
Select Case ComboBox2.ListIndex
Case "0"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=21, Criteria1:="=" & Format([datecell], "mm/dd/yyyy")
Case "1"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=21, Criteria1:="<" & Format([datecell], "mm/dd/yyyy")
Case "2"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=21, Criteria1:=">" & Format([datecell], "mm/dd/yyyy")
Case "3"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=21, Criteria1:="<=" & Format([datecell], "mm/dd/yyyy")
Case "4"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=21, Criteria1:=">=" & Format([datecell], "mm/dd/yyyy")
End Select
 
Case "Submission Date-ROA"
ActiveSheet.AutoFilterMode = False
ListBox1.Clear
Select Case ComboBox2.ListIndex
Case "0"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=22, Criteria1:="=" & Format([datecell], "mm/dd/yyyy")
Case "1"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=22, Criteria1:="<" & Format([datecell], "mm/dd/yyyy")
Case "2"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=22, Criteria1:=">" & Format([datecell], "mm/dd/yyyy")
Case "3"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=22, Criteria1:="<=" & Format([datecell], "mm/dd/yyyy")
Case "4"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=22, Criteria1:=">=" & Format([datecell], "mm/dd/yyyy")
End Select
 
Case "FY End Date"
ActiveSheet.AutoFilterMode = False
ListBox1.Clear
Select Case ComboBox2.ListIndex
Case "0"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=28, Criteria1:="=" & Format([datecell], "mm/dd/yyyy")
Case "1"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=28, Criteria1:="<" & Format([datecell], "mm/dd/yyyy")
Case "2"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=28, Criteria1:=">" & Format([datecell], "mm/dd/yyyy")
Case "3"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=28, Criteria1:="<=" & Format([datecell], "mm/dd/yyyy")
Case "4"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=28, Criteria1:=">=" & Format([datecell], "mm/dd/yyyy")
End Select
 
Case "Document #"
ActiveSheet.AutoFilterMode = False
ListBox1.Clear
Select Case ComboBox2.ListIndex
Case "0"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=26, Criteria1:="=" & TextBox5.Value
Case "1"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=26, Criteria1:="<" & TextBox5.Value
Case "2"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=26, Criteria1:=">" & TextBox5.Value
Case "3"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=26, Criteria1:="<=" & TextBox5.Value
Case "4"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=26, Criteria1:=">=" & TextBox5.Value
End Select
 
Case "EIN"
ActiveSheet.AutoFilterMode = False
ListBox1.Clear
Select Case ComboBox2.ListIndex
Case "0"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=27, Criteria1:="=" & TextBox8.Value
Case "1"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=27, Criteria1:="<" & TextBox8.Value
Case "2"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=27, Criteria1:=">" & TextBox8.Value
Case "3"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=27, Criteria1:="<=" & TextBox8.Value
Case "4"
ActiveSheet.Range("A1:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=27, Criteria1:=">=" & TextBox8.Value
End Select
 
Sheets("FilteredData").Cells.Clear
If ActiveSheet.Range("A1").CurrentRegion.Columns(1).SpecialCells(xlCellTypeVisible).Count <= 1 Then
GoTo here20:
Else
ActiveSheet.Range("A2:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
        Destination:=Sheets("FilteredData").Range("A2")
End If
Sheets("FilteredData").Columns.AutoFit
ListBox1.List = Sheets("FilteredData").Range("A2:O" & Sheets("FilteredData").Cells(Rows.Count, 1).End(xlUp).Row).Value
here20:
ActiveSheet.AutoFilterMode = False
Call Clear
 
End Select
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.EnableEvents = True
End With
 
Label15.Caption = ListBox1.ListCount
End Sub
 
Private Sub CommandButton6_Click() 'Clear Search Textbox Button
TextBox52.Value = "": ComboBox1.Value = ""
ListBox1.Clear
Label15.Caption = ""
End Sub
 
Private Sub CommandButton7_Click() 'Close Button
Unload Me
End Sub
 
Private Sub CommandButton8_Click()
   
 Dim Litem As Long, LbRows As Long, LbCols As Long
 Dim bu As Boolean
 Dim Lbloop As Long, Lbcopy As Long
 
 LbRows = ListBox1.ListCount - 1
 LbCols = ListBox1.ColumnCount - 1
  
    For Litem = 0 To LbRows
    If ListBox1.Selected(Litem) = True Then
          bu = True
          Exit For
    End If
    Next
 
    If bu = True Then
    With Sheets("SelectedData").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
     
            For Litem = 0 To LbRows
                If ListBox1.Selected(Litem) = True Then 'Row selected
                  'Increment variable for row transfer range
                  Lbcopy = Lbcopy + 1
            For Lbloop = 0 To LbCols
                       'Transfer selected row to relevant row of transfer range
            .Cells(Lbcopy, Lbloop + 1) = ListBox1.List(Litem, Lbloop)
                       
           Next Lbloop
                End If
            Next
            For m = 0 To LbCols
                With Sheets("SelectedData").Cells(Rows.Count, 1).End(xlUp).Offset(0, m).Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = 23
        End With
Next
        End With
       
    Else
         MsgBox "Nothing chosen", vbCritical
         Exit Sub
    End If
MsgBox "The Selected Data Have Been Successfully Copied.", vbInformation
Sheets("SelectedData").Select
 
End Sub
 
 
Private Sub CommandButton9_Click()
ListBox1.ColumnWidths = "92;50;10;20;25;10;10;65;65;115;150;65;65;65;65;50;50;50;50;50;50;50;50;50;50;50;50;50;50;50;50"         'COLUMN WIDTHS OF LISTBOX
ListBox1.ColumnCount = 30                                                 'Column Count Of Listbox
ListBox1.List = Sheets("Data").Range("A2:O" & Sheets("data").Cells(Rows.Count, 1).End(xlUp).Row).Value
ListBox1.ListIndex = -1
TextBox50.Value = ListBox1.ListCount
End Sub
 
Private Sub Label1_Click()
 
End Sub
 
Private Sub Label12_Click()
 
End Sub
 
Private Sub Label21_Click()
 
End Sub
 
Private Sub Label22_Click()
 
End Sub
 
Private Sub Label29_Click()
 
End Sub
 
Private Sub Label3_Click()
 
End Sub
 
Private Sub Label30_Click()
 
End Sub
 
Private Sub Label33_Click()
 
End Sub
 
Private Sub Label38_Click()
 
End Sub
 
Private Sub Label42_Click()
 
End Sub
 
Private Sub Label5_Click()
 
End Sub
 
Private Sub Label9_Click()
 
End Sub
 
Private Sub ListBox1_Change()
 
End Sub
 
Private Sub ListBox1_Click()
 
Dim say, lastrow As Long, a As Byte
'ListBox1.MultiSelect = 0
OptionButton1.Value = True
For a = 0 To 1
Controls("textbox" & a + 1) = ListBox1.Column(a)
Next
ListBox2.Value = ListBox1.Column(2)
TextBox23.Value = ListBox1.Column(3)
TextBox4.Value = ListBox1.Column(4)
TextBox27.Value = ListBox1.Column(5)
TextBox28.Value = ListBox1.Column(6)
TextBox6.Value = ListBox1.Column(7)
TextBox9.Value = ListBox1.Column(8)
TextBox10.Value = ListBox1.Column(9)
TextBox11.Value = ListBox1.Column(10)
TextBox16.Value = ListBox1.Column(11)
TextBox29.Value = ListBox1.Column(12)
OptionButton4.Value = ListBox1.Column(13)
TextBox30.Value = ListBox1.Column(14)
TextBox31.Value = ListBox1.Column(15)
OptionButton6.Value = ListBox1.Column(16)
TextBox33.Value = ListBox1.Column(17)
TextBox32.Value = ListBox1.Column(18)
OptionButton9.Value = ListBox1.Column(19)
TextBox35.Value = ListBox1.Column(20)
TextBox34.Value = ListBox1.Column(21)
OptionButton11.Value = ListBox1.Column(22)
TextBox36.Value = ListBox1.Column(23)
ListBox3.Value = ListBox1.Column(24)
TextBox5.Value = ListBox1.Column(25)
TextBox8.Value = ListBox1.Column(26)
TextBox7.Value = ListBox1.Column(27)
ListBox4.Value = ListBox1.Column(28)
 
 
lastrow = Sheets("Data").Cells(Rows.Count, "A").End(xlUp).RowSheets("Data").Activate
Sheets("Data").Range("A2:A" & lastrow).Find(What:=ListBox1.Value, LookIn:=xlValues, LookAt:=xlWhole).Activate
 
say = ActiveCell.Row
TextBox51.Value = say
Sheets("Data").Range("A" & say & ":O" & say).Select
 
End Sub
 
Private Sub ListBox2_Click()
 
End Sub
 
Private Sub ListBox3_Click()
 
End Sub
 
Private Sub OptionButton1_Click()
ListBox1.MultiSelect = 0
End Sub
 
Private Sub OptionButton2_Click()
ListBox1.ListIndex = -1
ListBox1.MultiSelect = 1
End Sub
 
Private Sub OptionButton3_Click()
ListBox1.ListIndex = -1
ListBox1.MultiSelect = 2
End Sub
 
Private Sub SpinButton1_SpinDown()
On Error Resume Next
If ListBox1.ListIndex = ListBox1.ListCount - 1 Then Exit Sub
With Me.ListBox1
        .ListIndex = .ListIndex + 1
    End With
 End Sub
 
Private Sub SpinButton1_SpinUp()
On Error Resume Next
If ListBox1.ListIndex = 0 Then Exit Sub
 
With Me.ListBox1
        .ListIndex = .ListIndex - 1
    End With
    End Sub
 
Private Sub TextBox27_Change()
 
End Sub
 
Private Sub TextBox30_Change()
 
End Sub
 
Private Sub TextBox31_Change()
 
End Sub
 
Private Sub TextBox50_Change()
 
End Sub
 
Private Sub TextBox52_Change()
 
End Sub
 
Private Sub TextBox7_Change()
 
End Sub
 
Private Sub TextBox6_Change()
 
End Sub
 
Private Sub TextBox9_Change()
 
End Sub
 
Private Sub ToggleButton1_Click()
If ToggleButton1.Value = False Then
    Application.Visible = False
   End If
   If ToggleButton1.Value = True Then
    Application.Visible = True
   
End If
End Sub
 
Private Sub UserForm_Initialize()
Sheets("Data").Activate
ListBox1.ColumnWidths = "92;140;110;65;65;35;40;65;65;115;150;65;65;65;65;65;65;65;65;65;65;65;65;65;65;65;65;65;65"         'Column Widths Of Listbox
ListBox1.ColumnCount = 29                                                          'Column Count Of Listbox
ListBox1.List = Sheets("Data").Range("A2:O" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row).Value
'** SEARCH COMBOBOX
With ComboBox1
.AddItem "Grantee"
.AddItem "Report Number"
.AddItem "Document #"
.AddItem "EIN"
.AddItem "Repeat Finding"
.AddItem "FY End Date"
.AddItem "Audit Type"
.AddItem "Fiscal Year"
.AddItem "Finding #"
.AddItem "Recommendation Code"
.AddItem "Finding Type"
.AddItem "Finding Details"
.AddItem "Recommendation"
.AddItem "Financial Analyst"
.AddItem "Notes"
.AddItem "Boss Letter Received Date"
.AddItem "Boss Final Due Date"
.AddItem "Finding's Current Phase"
.AddItem "Posted to SP-Boss Letter"
.AddItem "Due Date-Grantee"
.AddItem "Submission Date-Grantee"
.AddItem "Posted to SP-Grantee"
.AddItem "Due Date-RFM"
.AddItem "Submission Date-RFM"
.AddItem "Posted to SP-RFM"
.AddItem "Due Date-ROA"
.AddItem "Submission Date-ROA"
.AddItem "Posted to SP-ROA"
End With
'**********************************************
With ComboBox2
.AddItem "="
.AddItem "<"
.AddItem "<="
.AddItem ">"
.AddItem ">="
End With
ComboBox2.ListIndex = 0
TextBox50.Value = ListBox1.ListCount
TextBox51.Value = ""
 
'**********************************************
With ListBox2
.AddItem "Z1"
.AddItem "Z2"
End With
 
With ListBox3
.AddItem "Pending RFM to Notify Grantee"
.AddItem "Pending Grantee Submissionto RFM "
.AddItem "Pending RFM Submission to ROA Liaison"
.AddItem "Pending ROA Liaison Submission to boss"
End With
 
With ListBox4
.AddItem "Yes"
.AddItem "No"
End With
 
With lblDone
        .Top = lblRemain.Top + 1
        .Left = lblRemain.Left + 1
        .Height = lblRemain.Height - 2
        .Width = 0
    End With
lblPct.Visible = False
OptionButton1.Value = True
 
OptionButton5.Value = True
OptionButton7.Value = True
OptionButton8.Value = True
OptionButton10.Value = True
 
End Sub
' PROGRESS BAR CODES
Sub Main()
Dim i, tot As Integer
tot = 5000
For i = 1 To tot
If i Mod 5 = 0 Then
ProgressBar i / tot
End If
Next i
lblDone.Width = 0
lblPct.Visible = False
End Sub
Sub ProgressBar(PctDone As Single)
    lblDone.Width = PctDone * (lblRemain.Width - 2)
    lblPct.Visible = True
    lblPct.Caption = Format(PctDone, "0%")
    DoEvents
 End Sub
 
Sub Clear()
Dim n As Byte
For n = 1 To 2
Controls("textbox" & n) = Empty
Next
ListBox2.Text = Empty: TextBox23.Value = Empty: TextBox4.Value = Empty: TextBox27.Value = Empty:
TextBox28.Value = Empty: TextBox6.Value = Empty: TextBox9.Value = Empty: TextBox10.Value = Empty:
TextBox11.Value = Empty: TextBox16.Value = Empty: TextBox29.Value = Empty: OptionButton4.Value = Empty:
TextBox30.Value = Empty: TextBox31.Value = Empty: OptionButton6.Value = Empty: TextBox33.Value = Empty:
TextBox32.Value = Empty: OptionButton9.Value = Empty: TextBox35.Value = Empty: TextBox34.Value = Empty:
OptionButton11.Value = Empty: TextBox36.Value = Empty: ListBox3.Value = Empty: TextBox5.Value = Empty:
TextBox8.Value = Empty: TextBox7.Value = Empty: ListBox4.Value = Empty:
End Sub
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Unfortunately, changing the macro as the link suggested still provides me with the same runtime error and the list still displays 15 columns only. Thank you for trying and the link, mugginsjr.

I see where you changed your columncount in CommandButton9_Click as well as userform_Open. I don't know if there is a limit to how many columns you can have. Perhaps you just need to change the size dimensions of the listbox so that it shows all 29 columns. This might help. https://www.mrexcel.com/forum/excel-questions/296323-maximum-number-columns-listbox.html
 
Upvote 0
I built a userform with one listbox and one button. I put dummy data on sheet2 from A1:CC15. (a single letter in each column and copied down to row 15) Below is some code that seems to work fine for me.

I found the basis of this code at HERE and modified it for your use.

Code:
Private Sub UserForm_Initialize()
'  This Populates the Listbox with 15 columns
    Dim lb As msforms.ListBox
    Dim rcArray() As Variant
    Dim lrw As Long, lcol As Long
    Dim rngTarget As Range
    
    'Define the range you want to use
    Set rngTarget = Sheet2.Range("A1:S15")
    abc = rngTarget.Rows.Count
    'Set the boundaries of the array
    ReDim Preserve rcArray(1 To rngTarget.Rows.Count, 1 To rngTarget.Columns.Count)
    
    'Fill the array with data from the worksheet
    With rngTarget
        For lcol = 1 To .Columns.Count
            For lrw = 1 To .Rows.Count
                rcArray(lrw, lcol) = rngTarget.Cells(lrw, lcol)
            Next lrw
        Next lcol
    End With
    
    'Place the array in the listbox
    Set lb = Me.ListBox1
    With lb
        .ColumnCount = 15
        .ColumnWidths = "20;20;20;20;20;20;20;20;20;20;20;20;20;20"
        .List = rcArray
    End With
    
End Sub


Private Sub CommandButton1_Click()
'   This button changes the list to 29 columns and populates it 
    Dim lb As msforms.ListBox
    Dim rcArray() As Variant
    Dim lrw As Long, lcol As Long
    Dim rngTarget As Range
    
    'Define the range you want to use
    Set rngTarget = Sheet2.Range("A1:AC15")
    abc = rngTarget.Rows.Count
    'Set the boundaries of the array
    ReDim Preserve rcArray(1 To rngTarget.Rows.Count, 1 To rngTarget.Columns.Count)
    
    'Fill the array with data from the worksheet
    With rngTarget
        For lcol = 1 To .Columns.Count
            For lrw = 1 To .Rows.Count
                rcArray(lrw, lcol) = rngTarget.Cells(lrw, lcol)
            Next lrw
        Next lcol
    End With
    
    'Place the array in the listbox
    Set lb = Me.ListBox1
    With lb
        .ColumnCount = 29
        .ColumnWidths = "20;20;20;20;20;20;20;20;20;20;20;20;20;20;20;20;20;20;20;20;20;20;20;20;20;20;20;20;20"
        .List = rcArray
    End With
End Sub
 
Upvote 0
Thank you so much, mugginsjr!!! It works great!

I built a userform with one listbox and one button. I put dummy data on sheet2 from A1:CC15. (a single letter in each column and copied down to row 15) Below is some code that seems to work fine for me.

I found the basis of this code at HERE and modified it for your use.

Code:
Private Sub UserForm_Initialize()
'  This Populates the Listbox with 15 columns
    Dim lb As msforms.ListBox
    Dim rcArray() As Variant
    Dim lrw As Long, lcol As Long
    Dim rngTarget As Range
    
    'Define the range you want to use
    Set rngTarget = Sheet2.Range("A1:S15")
    abc = rngTarget.Rows.Count
    'Set the boundaries of the array
    ReDim Preserve rcArray(1 To rngTarget.Rows.Count, 1 To rngTarget.Columns.Count)
    
    'Fill the array with data from the worksheet
    With rngTarget
        For lcol = 1 To .Columns.Count
            For lrw = 1 To .Rows.Count
                rcArray(lrw, lcol) = rngTarget.Cells(lrw, lcol)
            Next lrw
        Next lcol
    End With
    
    'Place the array in the listbox
    Set lb = Me.ListBox1
    With lb
        .ColumnCount = 15
        .ColumnWidths = "20;20;20;20;20;20;20;20;20;20;20;20;20;20"
        .List = rcArray
    End With
    
End Sub


Private Sub CommandButton1_Click()
'   This button changes the list to 29 columns and populates it 
    Dim lb As msforms.ListBox
    Dim rcArray() As Variant
    Dim lrw As Long, lcol As Long
    Dim rngTarget As Range
    
    'Define the range you want to use
    Set rngTarget = Sheet2.Range("A1:AC15")
    abc = rngTarget.Rows.Count
    'Set the boundaries of the array
    ReDim Preserve rcArray(1 To rngTarget.Rows.Count, 1 To rngTarget.Columns.Count)
    
    'Fill the array with data from the worksheet
    With rngTarget
        For lcol = 1 To .Columns.Count
            For lrw = 1 To .Rows.Count
                rcArray(lrw, lcol) = rngTarget.Cells(lrw, lcol)
            Next lrw
        Next lcol
    End With
    
    'Place the array in the listbox
    Set lb = Me.ListBox1
    With lb
        .ColumnCount = 29
        .ColumnWidths = "20;20;20;20;20;20;20;20;20;20;20;20;20;20;20;20;20;20;20;20;20;20;20;20;20;20;20;20;20"
        .List = rcArray
    End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top