Excel VBA create embedded macro with external macro

Question

I have a macro file I use to edit and format a hundred Excel files a week that are then sent out. I am looking to add some more involved functionality to the files that are sent out.

Each of the files sent out will need to have code similar to:

Option Explicit

Sub DropDown4_Change()
    With ThisWorkbook.Sheets("ExampleData").Shapes("Drop Down 4").ControlFormat
        Select Case .List(.Value)
            Case "Value1": SelectValue1
            Case "Value2": SelectValue2
            Case "Value3": SelectValue3
            Case "Value4": SelectValue4
            Case "Value5": SelectValue5
            Case "Value6": SelectValue6
            Case "Value7": SelectValue7
            Case "Value8": SelectValue8
        End Select
    End With
End Sub

Sub SelectValue1()
    ActiveSheet.ListObjects("Table4").Range.AutoFilter
    ActiveSheet.ListObjects("Table4").Range.AutoFilter Field:=2, Criteria1:="<>"
End Sub

Sub SelectValue2()
    ActiveSheet.ListObjects("Table4").Range.AutoFilter
    ActiveSheet.ListObjects("Table4").Range.AutoFilter Field:=3, Criteria1:="<>"
End Sub

Sub SelectValue3()
    ActiveSheet.ListObjects("Table4").Range.AutoFilter
    ActiveSheet.ListObjects("Table4").Range.AutoFilter Field:=4, Criteria1:="<>"
End Sub

Sub SelectValue4()
    ActiveSheet.ListObjects("Table4").Range.AutoFilter
    ActiveSheet.ListObjects("Table4").Range.AutoFilter Field:=5, Criteria1:="<>"
End Sub

Sub SelectValue5()
    ActiveSheet.ListObjects("Table4").Range.AutoFilter
    ActiveSheet.ListObjects("Table4").Range.AutoFilter Field:=6, Criteria1:="<>"
End Sub

Sub SelectValue6()
    ActiveSheet.ListObjects("Table4").Range.AutoFilter
    ActiveSheet.ListObjects("Table4").Range.AutoFilter Field:=7, Criteria1:="<>"
End Sub

Sub SelectValue7()
    ActiveSheet.ListObjects("Table4").Range.AutoFilter
    ActiveSheet.ListObjects("Table4").Range.AutoFilter Field:=8, Criteria1:="<>"
End Sub

Sub SelectValue8()
    ActiveSheet.ListObjects("Table4").Range.AutoFilter
    ActiveSheet.ListObjects("Table4").Range.AutoFilter Field:=9, Criteria1:="<>"
End Sub

It's basic filtering based on a combobox selection. What code do I need in my external macro to have it write this code within each Excel file it is run on? Is this possible?

How are the Excel files created? Can you use a template? Add the code to the template and have the files be created with that template. — MatthewD
– MatthewD, Commented Dec 30, 2016 at 22:29
The excel files are modified CSV files. I receive a CSV and run my external macro on it to generate all of our reporting, then send it off to the recipient. If I am understanding that answer, that is a method for importing a previously exported module? In my example code, the name and number of Values will be different on a file by file basis, but I believe I should be able to cover that within a previously written module. — Adam Smith
– Adam Smith, Commented Dec 31, 2016 at 3:47
@AdamSmith do you just want to run this macro on a bunch of files without having to open each individually? Or do you want to put the macro in to each file and also run the macro ? — BruceWayne
– BruceWayne, Commented Dec 31, 2016 at 18:37
I want to put the above (or modified versions below) into each file. It doesn't have to run at the time of creation. These macro enabled files are then sent out to other people. I have a macro that I run that modifies the files I receive to be sent out and want to include within that macro code that will create the listed macro in the files being modified. — Adam Smith
– Adam Smith, Commented Jan 1, 2017 at 21:38

BruceWayne · Accepted Answer · 2016-12-30 22:51:00Z

3

Just a note, unless I'm missing something, you can greatly reduce the size of that code with a small tweak:

Option Explicit

Sub DropDown4_Change()
    Dim fieldVal As Long

    With ThisWorkbook.Sheets("ExampleData").Shapes("Drop Down 4").ControlFormat
        Select Case .List(.Value)
            Case "Value1": fieldVal = 2
            Case "Value2": fieldVal = 3
            Case "Value3": fieldVal = 4
            Case "Value4": fieldVal = 5
            Case "Value5": fieldVal = 6
            Case "Value6": fieldVal = 7
            Case "Value7": fieldVal = 8
            Case "Value8": fieldVal = 9
        End Select
    End With
    Call SelectValue(fieldVal)
End Sub

Sub SelectValue(myVal As Long)
    ActiveSheet.ListObjects("Table4").Range.AutoFilter
    ActiveSheet.ListObjects("Table4").Range.AutoFilter Field:=myVal, Criteria1:="<>"
End Sub

answered Dec 30, 2016 at 22:51

BruceWayne

23.5k16 gold badges75 silver badges126 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Adam Smith Over a year ago

Thanks for the info. I'll definitely use this trimmed down version.

user3598756 · Accepted Answer · 2016-12-31 08:58:09Z

1

a further trimmed version

Sub DropDown4_Change()
    Dim myVal As Long

    With ThisWorkbook.Sheets("ExampleData")
        With .Shapes("Drop Down 4").ControlFormat
            myVal = CLng(Replace(.list(.Value), "Value", "")) + 1
        End With
        .ListObjects("Table4").Range.AutoFilter
        .ListObjects("Table4").Range.AutoFilter Field:=myVal, Criteria1:="<>"
    End With
End Sub

a super-trimmed version

Sub DropDown4_Change()
    With ThisWorkbook.Sheets("ExampleData")
        .ListObjects("Table4").Range.AutoFilter
        .ListObjects("Table4").Range.AutoFilter Field:=CLng(Replace(.Shapes("Drop Down 4").ControlFormat.list(.Shapes("Drop Down 4").ControlFormat.Value), "Value", "")) + 1, Criteria1:="<>"
    End With
End Sub

answered Dec 31, 2016 at 8:58

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

1 Comment

Adam Smith Over a year ago

These are great, but it looks like they require the values to all be sequential Value#? In the final version, all of the values within the combobox are going to be client charges, and they will not be the same from file to file.

Collectives™ on Stack Overflow

Excel VBA create embedded macro with external macro

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related