Custom ribbon button opens macro file but then switches focus

Question

I have written an excel macro that parses a list of URL's, saving back information from the worksheet to each URL.

I have created a button on the ribbon to run the macro.

The macro is stored in a file of its own, as the files with the URL's are uniquely generated each time.

When I hit the button to run the macro, and the file is not loaded. Excel will load the file and open a window, showing the Macro file with an empty worksheet.

Then it will run the macro, on the newly opened macro file, instead of the URL-file where I started the macro.

In fact, one or two of the first rows in the URL file will be handled by the macro, before it switches focus I guess.

Can this be fixed, so I can run the macro from the button in a correct way even if the macro file is not loaded first? Or, second option, the macro file is opened if it wasn't already, but then stops, not executing any of the rows?

Thanks, Thomas

You didn't write your macro correct if that's the case. You should read up on https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba , especially you should read about explicitly referencing workbooks and sheets. Without the actual code we can't help you any further. — Luuklag, Jan 21 '19 at 11:01
@Luuklag Thanks. I found a way using Workbook.Activate. I added the code to another answer to the question. Too long to add in this comment. — Thomas Nilsson, Jan 21 '19 at 12:20

score 0 · Answer 1 · edited Jan 21 '19 at 12:32

@Luuklag

I found a way using Workbook Activate. The code is shown below, including the "fix". But it does not feel like a good or robust way of doing it.

Sub cmdParsePartsList()
Dim iRow As Long
Dim Count As Long
Dim pctCmpl As Integer
Dim TotalCnt As Long

Dim Wb As Workbook
Set Wb = ActiveWorkbook
Wb.Activate    'To move focus back to the TIKA list if pressing the button to start the macro
               'automatically opened the macro file and switched focus to the macro file.
               ' This method does not feel robust. But it is what I came up with.

iRow = 2  'Start at second row, as first row is Heading.
Count = 0 'Counting number of parts written back
pctCmpl = 1

TotalCnt = Application.WorksheetFunction.Subtotal(3, Range("C:C"))

ProgressForm.LabelProgress.Width = 0
ProgressForm.Show

Do Until IsEmpty(Cells(iRow, 3))
    If Not Cells(iRow, 3).Rows.Hidden Then
        Application.StatusBar = iRow
        Call cmdSaveToTika(HLink(Cells(iRow, 3)), Cells(iRow, 5).Text)
        Count = Count + 1

        pctCmpl = ((100 * Count) / TotalCnt)
        With ProgressForm
            .LabelCaption.Caption = "Processing Row " & iRow & " - " & pctCmpl & "%"
            .LabelProgress.Width = (Count / TotalCnt) * (.FrameProgress.Width)
        End With

        DoEvents

    End If
    iRow = iRow + 1
    Wb.Activate         'To move focus back to the TIKA list if pressing the button to start the macro,
                        'automatically opened the macro file and switched focus to the macro file.
                        ' This method does not feel robust. But it is what I came up with.
Loop

Application.StatusBar = "Done with " & Count & " comments saved back to TIKA! :)"
Unload ProgressForm

End Sub

Don't know why the first few rows and the last one, did not end up inside the code box. — Thomas Nilsson, Jan 21 '19 at 12:21
You should explicitly reference your workbooks and worksheets. So use `Set WB = workbooks("TIKA")` (or whatever the name is). And then where ever you use `Cells` use `WB.Cells`to explicitly call the cell from the TIKA workbook. — Luuklag, Jan 21 '19 at 13:11
Ok, thank you! I will do that. Now I got how the code-box works as well :) — Thomas Nilsson, Jan 21 '19 at 13:33
Ok, so the cells needs to be referenced through `Ws = Wb.ActiveSheet` and then the cells are referenced by `Ws.Cells` Apparently some code of the macro gets executed before the actual window of the macro becomes active. So the Workbook gets set to the workbook that the macro was called from, rather than the Macro file itself. Macro will be used by many different people with different files, so file name is unknown unfortunately. But this seems to work, so I will leave it like this then. — Thomas Nilsson, Jan 21 '19 at 14:54

Custom ribbon button opens macro file but then switches focus

1 Answers1