VBA Code for Dropdown List with Assigned Macros

[benri]

Hi everyone,

Long time forum searcher, first time posting.

I am hoping to get some direction with code that has completely stumped me. I have written code that is pretty basic, and the end result is a workbook with a number of columns with thousands of rows of data. Currently the code creates three worksheets in the same workbook to view the data different ways (via AutoFilter), but I'd much rather have a single worksheet with a dropdown list so a user could select a few ways to view the data. I have scoured the web looking for a solution, but it eludes me.

I was able to write code that creates the dropdown list with the three filter views, but no action is taken when selecting any of the drop down items.

The code I am posting below is the second set of procedures. I am very new to VBA, so please be gentle.

Thanks to everyone in advance for taking the time to read this.

Option Explicit


Sub LocTrackerMacro2()


' localization tracker macro2, second set of procedures ; module 3


    Dim Output As Workbook, Source As Workbook
    Dim sh As Worksheet
    Dim FileName As String
    Dim firstcell
    
    Application.ScreenUpdating = False
    Set Source = ActiveWorkbook
    
    Set Output = Workbooks.Add
    Application.DisplayAlerts = False
    
    Dim i As Integer
    
    For Each sh In Source.Worksheets
    
        Dim NewSheet As Worksheet
        
        ' select all used cells in the source sheet
        sh.Activate
        sh.UsedRange.Select
        Application.CutCopyMode = False
        Selection.Copy
        
        ' create new destination sheet
        Set NewSheet = Output.Worksheets.Add
        NewSheet.Name = "AllEvents"
        
        ' make sure the destination sheet is selected with right cell
        NewSheet.Activate
        firstcell = sh.UsedRange.Cells(1, 1).Address
        NewSheet.Range(firstcell).Select
        
        ' paste the values
        Range(firstcell).PasteSpecial Paste:=xlPasteValues, _
        Operation:=xlNone, SkipBlanks:=True, Transpose:=False
        
        ' delete the empty sheet in the new workbook
        Worksheets("Sheet1").Delete
        
        ' turn on autofilter
        If ActiveSheet.AutoFilterMode = True Then
        ' do nothing
        Else
        ActiveSheet.Range("A1:S1").AutoFilter
        End If
        
        ' basic formatting
        Range("A:A,C:D,I:I").Delete
        Range("A1:O1").Interior.ColorIndex = 37
        Range("A1:O1").WrapText = True
        Columns("A:C").EntireColumn.AutoFit
        Columns("D:E").ColumnWidth = 15
        Columns("F").ColumnWidth = 20
        Columns("G:H").ColumnWidth = 8
        Columns("I").ColumnWidth = 10
        Columns("J").ColumnWidth = 20
        Columns("K").ColumnWidth = 9
        Columns("L:M").ColumnWidth = 20
        Columns("N:O").ColumnWidth = 10
        Rows("2:2").Select
        ActiveWindow.FreezePanes = True
        
        ' copying the allevents sheet for noMainsEnds data
        Sheets("AllEvents").Copy After:=Sheets("AllEvents")
        ActiveSheet.Name = "noMainsEnds"
        
        ' filter to exclude mains and ends
        Range("F:F").AutoFilter Field:=6, Criteria1:=Array( _
        "Foreign Dialogue-No Subtitles", "Main Title", "Narrative Title", "On-Screen Text" _
        , "Subtitle"), Operator:=xlFilterValues
        
       ' copying the allevents sheet for sub decisions data
        Sheets("AllEvents").Copy After:=Sheets("noMainsEnds")
        ActiveSheet.Name = "SubDecisions"
        
        ' filter sub decisions
        Range("D:D").AutoFilter Field:=4, Criteria1:="Subtitle"
        
        Sheets("AllEvents").Select
        
        
    Next
    Application.ScreenUpdating = True
    
' everything below is new test code


' insert row


'Range("A1").EntireRow.Insert


' adjust row height


'Rows(1).RowHeight = 17


' create combo box for tracker views


Worksheets("AllEvents").DropDowns.Add(0, 0, 100, 15).Name = "TrackerViews"


    ' add values to the tracker views combo box


    With Worksheets("AllEvents").Shapes("TrackerViews").ControlFormat
        .AddItem "All Events"
        .AddItem "No Mains & Ends"
        .AddItem "Sub Decisions"


    End With
    
        
End Sub




Private Sub Worksheet_Change(ByVal Target As Range)


    'Application.EnableEvents = False
    
    'If Not Intersect(Target, Range("A1")) Is Nothing Then
        'Select Case Target.Value
        
        If Target.Count > 1 Then Exit Sub
        If Target.Address <> "$A$1" Then Exit Sub
        Select Case Target
        
            Case "All Events"
                ActiveSheet.ShowAllData
                
            Case "No Mains & Ends"
                ActiveSheet.ShowAllData
                ActiveSheet.Range("$A$1:$O$1493").AutoFilter Field:=6, Criteria1:= _
               "Main Title"
               
            Case "Sub Decisions"
                
                ActiveSheet.ShowAllData
                ActiveSheet.Range("$A$1:$O$1493").AutoFilter Field:=4, Criteria1:= _
                "Subtitle"
                
            Case Else
                
        End Select
    End If
    Application.EnableEvents = True
    
End Sub


'Private Sub Worksheet_Change(ByVal Target As Range)


    'If Not Intersect(Target, Range("A1")) Is Nothing Then
        'Select Case Range("A1")
        
            'Case "All Events": ResetFilter
            'Case "No Mains & Ends": MainsEndsFilter
            'Case "Sub Decisions": SubDecisionsFiler
        'End Select
        


'End Sub


'Sub DropDown_Click(ByVal Target As Range)


'Dim drpdwn As DropDown
'Set drpdwn = ActiveSheet.DropDowns(Application.Caller)
'Select Case drpdwn.ListIndex


    'If Target.Address = "$A$1" Then
    'Select Case Target.Value
    
    'Case "All Events"
            'Call ResetFilter
            'module 8
            
    'Case "No Mains & Ends"
            'Call MainsEndsFilter
            'module 5
            
    'Case "Sub Decisions"
            'Call SubDecisionsFilter
            'module 7
    
    'Case Else 'do nothing
    
    'End Select
    


'If Not Intersect(Target, Range("A1")) Is Nothing Then
    
    
    'Select Case Range("A1")
        
        
            
    'End Select
    
'End Sub

 

[benri]

Ok, I added the message box line of code and it didn't solve the problem. So I went ahead and added the Application.EnableEvents line in a sub above the worksheet_change code. The message box finally appeared, but now the code is failing at the two active sheet.range lines of code for the autofilter.

Note: I inserted a row above the headers to make way for the data validation dropdown, so I changed the ranges to begin with $A$2.

 

[benri]

The error I'm receiving is:

Run-Time error 1004
Application-defined or object-defined error

 

[Fluff]

Is your data in a table of some sort, or just regular data?

 

[benri]

Just regular data. What I'm trying to accomplish is taking a data dump as an export from a system, and perform a number of automated tasks for end users.

Most of the code is pretty basic, such as formatting.

This is the last step I would like to take so the data appears polished and negates the need for multiple tabs, which again, is the same data just viewed differently.

I am happy to send you the workbook if that helps diagnose the problem.

 

[Fluff]

The workbook would help as the code is working for me.
Could you upload to a share site such as Ondrive, Dropbox, GoogleDrive, mark for sharing & post the link to the thread.

 

[benri]

Thank you, Fluff. I have included a link to the workbook here

In terms of the current macros, module 1 is run first, which calls module 6 and then module 3. I was unable to write all the code as a single macro.

 

[Fluff]

As you uploaded an xlsx file, I've no idea what your other macros are.
But with the code from post#8 in the "All Events" sheet module (adjusted for row2), it worked fine for me.

 

[benri]

Apologies for uploading the file with the wrong extension. The macro enabled workbook can be found here

The workbook still isn't responding to items selected in the dropdown list for me. Did it work for you - meaning the items selected change the filtered views?

 

[Fluff]

I suspect that rather than copy/pasting the code into your workbook, you wrote it out.
Both your Criteria are wrong, they should end with a 1 (one), not an l (lowercase L)

 

[benri]

Thank you so much for all the help, I truly appreciate it.

I corrected the spelling errors for both lines of code, and updated the first filter string to include critera1:="<>Mains and Ends" to only filter that out and leave the rest.

I do have a couple more questions, if you'll be so kind to answer:

1. Do I need to keep in place the short sub that set the ApplicationEnable status to true, or is there a way to combine them together? Every now and again, VBE will prompt me to choose a macro when attempting to run this.

2. It was fairly easy for me to manually type this out into the AllEvents sheet module, but how can I automate this? Ideally, I'd like to place code in module 3 that automatically creates the data validation drop down, and also inserts code into the AllEvents sheet module. Can this be accomplished?