Hello,
I have a workbook, and every worksheet contains a table. When I create a table, the filter options next to each column heading is available, but.. when I add to the table through a macro, these filters disappear. I'm able to get them back by pressing the "filter" button on the ribbon, but I don't want to have to explain this to every user using the workbook.
Is there a way to always keep the filters there? or is there something wrong with the way i'm adding rows to the table?
Private Sub Finishbtn2_Click()
Dim rownum As Long
If Me.purchasetb.Text = "" Or Me.purchasetb.Text = "mm/dd/yy" _
Or Me.contractorcb2.Value = "" Or Me.equipmentcb2.Value = "" _
Or Me.repairtb2.Text = "" Or Me.Dinitialstb.Text = "" _
Or Me.CompletionDatetb.Text = "" Or Me.CompletionDatetb.Text = "mm/dd/yy" Then
MsgBox ("Please enter all fields")
Else
Sheets("Current Contractors").Unprotect "123steel"
With Worksheets("Current Contractors")
'Set Headers
.Cells(9, 1) = "Today's Date"
.Cells(9, 2) = "Purchase Order Date"
.Cells(9, 3) = "Contractor"
.Cells(9, 4) = "Equipment"
.Cells(9, 5) = "Location"
.Cells(9, 6) = "Description"
.Cells(9, 7) = "Expected Time of Completion"
.Cells(9, 8) = "Completed? y or n"
.Cells(9, 9) = "Time of Completion"
Application.EnableEvents = False
Sheets("Current Contractors").Activate
Application.EnableEvents = True
rownum = .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
'Adds Today's Date
Cells(rownum, "A") = CDate(Me.datetb2.Text)
'Adds Purchase Date
Cells(rownum, "B") = Me.purchasetb.Text
'Adds Contractor
Cells(rownum, "C") = Me.contractorcb2.Text
'Adds Equipment
Cells(rownum, "D") = Me.equipmentcb2.Text
'Adds Location
Cells(rownum, "E") = Right(Me.equipmentcb2.Value, 2)
'Adds Description
Cells(rownum, "F") = Me.Dinitialstb.Text & " - " & Me.repairtb2.Text
'Adds Expected time
Cells(rownum, "G") = Me.CompletionDatetb.Text
Worksheets("Current Contractors").Columns().AutoFit
'resizes the columns for new entries
End With
Sheets("Current Contractors").Protect "123steel"
Unload Me 'closes the form, opens the spreadsheet
End If
End Sub
my workbook is attached just in case. the password is "MillSteel"
Bookmarks