I've set up a userform, my first one. I've currently got a supervisor testing
it.
Obviously this is my first successful attempt, and i've been working with
VBA for maybe 5/6 months, so I may have done things the long way round, or
just generally wrong.

Below are the macro's in the userform. All the buttons and boxes are still
called things like "ComboBox4" or "CommandButton3" so its quite difficult to
follow. What I'm really looking at is regardless of this (I know the entire
thing works) is if you think i've gone wrong somewhere, or have say 12 lines
of code where 1 will have done.

Really just wanting advise so I can better myself and my code.

What the userform does, is when opened take everything in row 1, and put it
in 4 combo boxes. Each of these combo boxes has another combo box to the
right of it. When you select a column header from one of the boxes on the
left, the box to the right populates with all the unique references in that
column (sorted).
When the update button is hit, it copies the entire spreadsheet, puts on the
filter and selects everything that *hasn't* been selected and deletes it.
Then it adds a couple of lines at the top to work on, and counts how many
lines of data are correct. At the top of each line it also adds together all
the numbers, so if say column 3 is "Balance" you can add all the Balances
together.

Private Sub CommandButton2_Click()
Unload Me
End Sub

Private Sub CommandButton3_Click()
ComboBox2.clear
ComboBox3.clear
ComboBox5.clear
ComboBox7.clear
ComboBox1.clear
ComboBox4.clear
ComboBox6.clear
ComboBox8.clear

For Each cell In Range("A1:EA1")

If cell.Value = "" Then Exit Sub

ComboBox1.AddItem cell.Value
ComboBox4.AddItem cell.Value
ComboBox6.AddItem cell.Value
ComboBox8.AddItem cell.Value

Next

End Sub

Private Sub UserForm_Initialize()


For Each cell In Range("A1:EA1")

If cell.Value = "" Then Exit Sub

ComboBox1.AddItem cell.Value
ComboBox4.AddItem cell.Value
ComboBox6.AddItem cell.Value
ComboBox8.AddItem cell.Value


Next

End Sub

Private Sub ComboBox8_AfterUpdate()
Dim allcells As Range, cell As Range
Dim nodupes As New Collection
Dim rang As Variant
Dim rang2 As Variant
Set nodupes = Nothing

If ComboBox8.ListIndex = -1 Then
ComboBox7.clear
Exit Sub
Else

Application.ScreenUpdating = False

rang = ActiveCell.Address

ComboBox7.clear

With Range("a1:z1")
Set c = .Find(ComboBox8.List(ComboBox8.ListIndex),
LookIn:=xlValues)
If Not c Is Nothing Then

rang2 = Range(c.Address).Offset(rowOffset:=1, columnOffset:=0).Address

Cells.Select
Selection.Sort Key1:=Range(rang2), Order1:=xlAscending,
Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal


Range(c.Address).Select
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
Range(Selection, Selection.End(xlDown)).Select

On Error Resume Next
For Each cell In Selection
nodupes.Add cell.Value, CStr(cell.Value)
Next cell
On Error GoTo 0
For Each Item In nodupes
ComboBox7.AddItem Item
Next Item
End If
End With

Range(rang).Select

Application.ScreenUpdating = True

End If

End Sub


Private Sub CommandButton1_Click()
Dim row1 As Variant
Dim row2 As Variant
Dim row3 As Variant
Dim row4 As Variant
Dim crit1 As Variant
Dim crit2 As Variant
Dim crit3 As Variant
Dim crit4 As Variant
Dim check1 As Variant
Dim check2 As Variant
Dim check3 As Variant
Dim check4 As Variant
Dim res1 As Variant
Dim res2 As Variant
Dim res3 As Variant
Dim res4 As Variant
Dim res5 As Variant
Dim ans1 As Variant
Dim ans2 As Variant
Dim ans3 As Variant
Dim ans4 As Variant

Application.ScreenUpdating = False

If CheckBox1 = True Then
check1 = 1
End If

If CheckBox2 = True Then
check2 = 1
End If

If CheckBox3 = True Then
check3 = 1
End If

If CheckBox4 = True Then
check4 = 1
End If

row1 = 9999
row2 = 9999
row3 = 9999
row4 = 9999
res2 = 999999999
res3 = 999999999
res4 = 999999999
res5 = 999999999

crit1 = ComboBox1.ListIndex
crit2 = ComboBox4.ListIndex
crit3 = ComboBox6.ListIndex
crit4 = ComboBox8.ListIndex

If crit1 = -1 Then
MsgBox ("Please complete the first Criteria, otherwise there can be
nothing to add.")
Exit Sub
End If

With Range("a1:z1")
Set c = .Find(ComboBox1.List(ComboBox1.ListIndex),
LookIn:=xlValues)
If Not c Is Nothing Then

row1 = Range(c.Address).Column
End If
End With

If crit2 -1 Then

With Range("a1:z1")
Set c = .Find(ComboBox4.List(ComboBox4.ListIndex),
LookIn:=xlValues)
If Not c Is Nothing Then

row2 = Range(c.Address).Column
End If
End With
End If

If crit3 -1 Then

With Range("a1:z1")
Set c = .Find(ComboBox6.List(ComboBox6.ListIndex),
LookIn:=xlValues)
If Not c Is Nothing Then

row3 = Range(c.Address).Column
End If
End With
End If

If crit4 -1 Then

With Range("a1:z1")
Set c = .Find(ComboBox8.List(ComboBox8.ListIndex),
LookIn:=xlValues)
If Not c Is Nothing Then

row4 = Range(c.Address).Column
End If
End With
End If

Cells.Select
Selection.Copy
Range("A1").Select
Sheets.Add
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
Selection.AutoFilter

crit1 = ComboBox2.ListIndex
crit2 = ComboBox3.ListIndex
crit3 = ComboBox5.ListIndex
crit4 = ComboBox7.ListIndex

If crit1 < -1 Then
If row1 < 9999 Then
crit1 = ComboBox2.List(ComboBox2.ListIndex)
Selection.AutoFilter Field:=row1, Criteria1:="<" & crit1, Operator:=xlAnd
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter Field:=row1
Range("A1").Select
End If
End If

If crit2 < -1 Then
If row2 < 9999 Then
crit2 = ComboBox3.List(ComboBox3.ListIndex)
Selection.AutoFilter Field:=row2, Criteria1:="<" & crit2, Operator:=xlAnd
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter Field:=row2
Range("A1").Select
End If
End If

If crit3 < -1 Then
If row2 < 9999 Then
crit2 = ComboBox5.List(ComboBox5.ListIndex)
Selection.AutoFilter Field:=row3, Criteria1:="<" & crit3, Operator:=xlAnd
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter Field:=row3
Range("A1").Select
End If
End If

If crit4 < -1 Then
If row4 < 9999 Then
crit4 = ComboBox7.List(ComboBox7.ListIndex)
Selection.AutoFilter Field:=row4, Criteria1:="<" & crit4, Operator:=xlAnd
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter Field:=row4
Range("A1").Select
End If
End If


Rows("1:3").Select
Selection.Insert Shift:=xlDown
Range("B2").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(C[-1],""<""&"""")-2"
Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(R[4]C:R[6000]C)"
Range("A1").Select
Selection.NumberFormat = "0.00"
Selection.AutoFill Destination:=Range("A1:Z1"), Type:=xlFillDefault
Range("A1:Z1").Select
Range("A1").Select
Range(Selection, Selection.End(xlToLeft)).Select
Range("E1").Select

res1 = Range("B2").Value

If row1 < 9999 Then
If check1 = 1 Then
With Range("a4:z4")
Set c = .Find(ComboBox1.List(ComboBox1.ListIndex),
LookIn:=xlValues)
If Not c Is Nothing Then
res2 = Range(c.Address).Offset(rowOffset:=-3,
columnOffset:=0).Value
End If
End With
End If
End If

If row2 < 9999 Then
If check2 = 1 Then
With Range("a4:z4")
Set c = .Find(ComboBox4.List(ComboBox4.ListIndex),
LookIn:=xlValues)
If Not c Is Nothing Then
res3 = Range(c.Address).Offset(rowOffset:=-3,
columnOffset:=0).Value
End If
End With
End If
End If

If row3 < 9999 Then
If check3 = 1 Then
With Range("a4:z4")
Set c = .Find(ComboBox6.List(ComboBox6.ListIndex),
LookIn:=xlValues)
If Not c Is Nothing Then
res4 = Range(c.Address).Offset(rowOffset:=-3,
columnOffset:=0).Value
End If
End With
End If
End If

If row4 < 9999 Then
If check4 = 1 Then
With Range("a4:z4")
Set c = .Find(ComboBox8.List(ComboBox8.ListIndex),
LookIn:=xlValues)
If Not c Is Nothing Then
res5 = Range(c.Address).Offset(rowOffset:=-3,
columnOffset:=0).Value
End If
End With
End If
End If


Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True

Application.ScreenUpdating = True

If res2 = 999999999 Then
ans1 = ""
Else
ans1 = vbCrLf & "Total sum for " &
ComboBox1.List(ComboBox1.ListIndex) & " is: " & res2
End If

If res3 = 999999999 Then
ans2 = ""
Else
ans2 = vbCrLf & "Total sum for " &
ComboBox4.List(ComboBox4.ListIndex) & " is: " & res3
End If

If res4 = 999999999 Then
ans3 = ""
Else
ans3 = vbCrLf & "Total sum for " &
ComboBox6.List(ComboBox6.ListIndex) & " is: " & res4
End If

If res5 = 999999999 Then
ans4 = ""
Else
ans4 = vbCrLf & "Total sum for " &
ComboBox8.List(ComboBox8.ListIndex) & " is: " & res5
End If

MsgBox ("There are " & res1 & " accounts that match your criteria." &
ans1 & ans2 & ans3 & ans4)

End Sub

Private Sub CheckBox2_Click()
If CheckBox2.Value = True Then
ComboBox3.clear
ComboBox3.Visible = False
Else
ComboBox3.Visible = True
ComboBox4_AfterUpdate
End If

End Sub