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.
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