CODEWITHSUNDEEP
vbaexcel
Davagaz
Davagaz

Reputation: 884

Let button with macro work in any excel file without open file where I wrote macro

I have a code, which help me to insert image in cell with some keyword, and set some print parameters. And every day I have many new files where I must do it. I make a button with macro on excel panel, but when I click it there open a file where I write macro in first time. What should I do to let it work without open old file?

Here is the code:

Sub Macro2()
With ActiveSheet.PageSetup
    .Zoom = False
        .FitToPagesWide = 1
    End With
    Set ff = ActiveSheet.Cells.Find("forpicture")
    MyTop = [ff].Top
    MyLeft = [ff].Left
    InsertPicture "C:\Users\Public\222222.png", _
        Range(ff.Address), True, True
        Dim f As Range
    With ActiveSheet.PageSetup
        .LeftMargin = Application.InchesToPoints(0.5)
        .RightMargin = Application.InchesToPoints(0.2)
        .TopMargin = Application.InchesToPoints(0.5)
        .BottomMargin = Application.InchesToPoints(0.2)
    End With
End Sub

Sub InsertPicture(PictureFileName As String, TargetCell As Range, _
    CenterH As Boolean, CenterV As Boolean)
' inserts a picture at the top left position of TargetCell
' the picture can be centered horizontally and/or vertically
Dim p As Object, t As Double, l As Double, w As Double, h As Double
    If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub
    If Dir(PictureFileName) = "" Then Exit Sub
    ' import picture
    Set p = ActiveSheet.Pictures.Insert(PictureFileName)
    ' determine positions
    With TargetCell
        t = .Top
        l = .Left
    End With
    ' position picture
    With p
        .Top = t
        .Left = l
        .Height = 150
    End With
    Set p = Nothing
End Sub

Upvotes: 0

Views: 147

Answers (1)

User30923
User30923

Reputation: 62

In the workbook where you wrote the macro, try this: Click on the Windows Button on the upper left. Select Excel Options. Click Customize on the left side. On the drop-down on the top left, select "Macros". Click the Macro you want then move it over to the right side. Make sure the drop-down on the top right says "For All Documents". This should give you a button that will run the macro on any excel document.

Upvotes: 1

Related Questions