Results 1 to 23 of 23

filter option always available on tables when rows are added and deleted through macro

Threaded View

emilyloz filter option always... 07-02-2013, 12:34 PM
AB33 Re: filter option always... 07-02-2013, 12:41 PM
emilyloz Re: filter option always... 07-02-2013, 12:44 PM
emilyloz Re: filter option always... 07-02-2013, 12:43 PM
AB33 Re: filter option always... 07-02-2013, 01:16 PM
emilyloz Re: filter option always... 07-02-2013, 01:21 PM
AB33 Re: filter option always... 07-02-2013, 01:34 PM
emilyloz Re: filter option always... 07-02-2013, 01:49 PM
AB33 Re: filter option always... 07-02-2013, 01:55 PM
emilyloz Re: filter option always... 07-02-2013, 01:56 PM
emilyloz Re: filter option always... 07-03-2013, 08:07 AM
emilyloz Re: filter option always... 07-03-2013, 08:39 AM
emilyloz Re: filter option always... 07-08-2013, 08:15 AM
JosephP Re: filter option always... 07-08-2013, 08:26 AM
emilyloz Re: filter option always... 07-08-2013, 08:29 AM
JosephP Re: filter option always... 07-08-2013, 08:34 AM
emilyloz Re: filter option always... 07-08-2013, 08:37 AM
JosephP Re: filter option always... 07-08-2013, 08:50 AM
emilyloz Re: filter option always... 07-08-2013, 08:55 AM
JosephP Re: filter option always... 07-08-2013, 08:57 AM
emilyloz Re: filter option always... 07-08-2013, 09:01 AM
JosephP Re: filter option always... 07-08-2013, 09:10 AM
emilyloz Re: filter option always... 07-08-2013, 09:25 AM
  1. #1
    Forum Contributor
    Join Date
    05-20-2013
    Location
    Ann Arbor, MI
    MS-Off Ver
    Excel 2010
    Posts
    255

    filter option always available on tables when rows are added and deleted through macro

    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"
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1