CODEWITHSUNDEEP
excelvba
26man
26man

Reputation: 31

run addin macro on open workbook

I have seen where you can run a macro based on inserted text, but the macro has to be embedded in the sheet, i.e., Private Sub Worksheet_Change(ByVal Target As Range).

What I want to do is call the macro from my add-in from a user's workbook that will not have "Worksheet_Change..." already embedded at the sheet level. Is there a way to do this?

As for additional background, I know I can run the macro from the add in, but I want to activate it using a bar-code scan rather than calling the macro from a button or some other interface.

My bar-code reads as Make Landscape 1 pg when scanned. Hoping to use some modification of this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If (Target.Value) = "Make Landscape 1 pg" Then
   With Application
        .EnableEvents = False
        .Undo
        .EnableEvents = True
    End With
    Application.ScreenUpdating = False
        'ActiveWindow.SelectedSheets.PrintPreview
        ActiveSheet.Select
        With ActiveSheet.PageSetup
            .Orientation = xlLandscape
            .FitToPagesWide = 1
            .FitToPagesTall = 1
        End With
        Application.ScreenUpdating = True
    End If
End Sub

Upvotes: 3

Views: 1035

Answers (1)

CaffeinatedMike
CaffeinatedMike

Reputation: 1607

Here's a link that I found very useful when needing to access Worksheet/Workbook-level events via modules in my Add-In. The basic implementation is like so:

In Add-In's ThisWorkbook module:

Private WithEvents App As Application
Private Sub Workbook_Open()
    Set App = Application
End Sub

Now App can be used to call/access workbook & worksheet events. If you're trying to check something upon selection change from a module in an add-in it has to be Workbook_Sheetselectionchange event (which you can read more about here) instead of the Worksheet_Change event. This event can be used in conjunction with the previously set App variable like so:

In Add-In's ThisWorkbook module:

Private Sub App_SheetSelectionChange(ByVal Sh As Object, _ 
ByVal Target As Excel.Range)
    If (Target.Value) = "Make Landscape 1 pg" Then
        With Application
            .EnableEvents = False
            .Undo
            .EnableEvents = True
        End With
        Application.ScreenUpdating = False
        ActiveSheet.Select
        With ActiveSheet.PageSetup
            .Orientation = xlLandscape
            .FitToPagesWide = 1
            .FitToPagesTall = 1
        End With
        Application.ScreenUpdating = True
    End If
End Sub

Upvotes: 1

Related Questions