ActiveSheet.Pictures.Insert(filenam).Select changes active worksheet first

Phil Smith 1 Reputation point

Two Worksheets. MyMacro and MyData. MyMacro has VBA that has the user select a file, then does things to that file like formatting, including adding pictures based on a column of values. As soon as MyData is opened, it is made the active workbook, and the first sheet the active worksheet.

I can step through the code, and see that MyData Sheet1 is active, Getting the URL, (filenam) from MyData, but the next line makes MyMacro active, and puts the resulting image there instead of MyData.

For Each CELL In Rng
'Get image URL from CELL in MyData
filenam = CELL.Value
'Yep, got a good value from MyData
'Insert Picture into MyData NOT MyMacro
'There's my picture, in MyMacro

It is literally the execution of that line that changes the active workbook, so I can't put anything in front of it. (I tried.)



Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,369 questions
No comments
{count} votes

2 answers

Sort by: Most helpful
  1. OssieMac 411 Reputation points

    Can't test without more detail but should be something like the following..

    Sub Example()
    Dim wsMyData As Workbook
    Dim wsMyMacro As Workbook
    Dim rng As Range
    Dim rCel As Range 'Suggest not using CELL which is a reserved word
    Dim fileNam

    Set wsMyData = Workbooks("MyData.xlsx")
    Set wsMyMacro = Workbooks("MyMacro.xlsm")
    Set rng = wsMyData.Worksheets("Sheet1").Range("A1:A10") ***'Example. Edit as required***
    For Each rCel In rng
        'Get image URL from CELL in MyData
        fileNam = rCel.Value
        'Yep, got a good value from MyData
        'Insert Picture into MyData NOT MyMacro
        Application.Goto rCel  *'Return to rCel will activate the workbook containing rCel*
        ActiveSheet.Pictures.Insert (fileNam)
    Next rCel

    End Sub

  2. Phil Smith 1 Reputation point

    OK. I found the problem, but do not understand it. It activates the Right Workbook, but a worksheet from the wrong workbook, no matter how I try. ATSTEST is the name of the Workbook that contains 1 sheet called "MyDataSheet" and nothing else. The following code prints the following results.

    Debug.Print ActiveWorkbook.Name
    Debug.Print "ActiveWorkbook.Worksheets(1) Select", ActiveWorkbook.Name, ActiveSheet.Name
    Debug.Print "", ActiveWorkbook.Name, ActiveSheet.Name
    Debug.Print "Sheets(MyDataSheet).select", ActiveWorkbook.Name, ActiveSheet.Name
    Debug.Print "Sheets(MyDataSheet).Activate", ActiveWorkbook.Name, ActiveSheet.Name

    ActiveWorkbook.Worksheets(1) Select ATSTEST.xlsx MyMacroSheet ATSTEST.xlsx MyMacroSheet
    Sheets(MyDataSheet).select ATSTEST.xlsx MyMacroSheet
    Sheets(MyDataSheet).Activate ATSTEST.xlsx MyMacroSheet

    No matter how I slice it, I get the correct workbook,(ATSTEST) but a worksheet from the workbook running my Macro. MyDataSheet is what I want, MyMacroSheet does not exist in ATSTEST. How do I get the thing to select that sheet?