Global variable is empty if 2 similar xls files with same macro are opened

Question

There is macro in Excel which was already written and there was bug reported on this and I have to fix this. Initial investigations are below... There is ABC.xls file which has the macro.

Now, the macro has a sub named changeTheCode which is getting called when I press Ctrl + M.

This sub will open a Open File Dialog where the user can choose a CSV file. The path of the CSV file I am storing in a global variable declare outside of all the function...

Public txtFileNameAndPath As String

This global variable will be used to save the changes into the CSV file when the user closes the excel.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call saveUnicodeCSV
    Call deleteXLS
End Sub

I use this ABC.xls file for opening a ABC123.CSV file.

I use this DEF.xls (a copy of ABC.xls) file to open DEF123.CSV file. But when I open the DEF123.CSV using Ctrl + M, the sub changeTheCode of the ABC.xls is getting called and the global variable txtFileNameAndPath of DEF.xls is empty and when I close the Excel, things are not getting saved because of this.

Code where the global variable is getting set.

Public txtFileNameAndPath As String
Sub CodePageChange()
    Dim SheetName As Worksheet
    Dim fd As Office.FileDialog
    Dim sheetName1 As String
    Dim tabSheetName As String

    Set fd = Application.FileDialog(msoFileDialogFilePicker)

    With fd
        '....
        '....
        '....
        If .Show = True Then
          txtFileNameAndPath = .SelectedItems(1)
        Else
            MsgBox "Please start over.  You must select a csv file."
            Exit Sub
        End If
    End With

Inputs on how to handle this will help me a lot.

Note: The Excel containing macro will be given to customer. Hence I cannot ask customer to do some registry tweaks to open the Excel in separate instance.

Thanks.

Answer 1

The problem I think is that when you bind the same macro names to a Shortcut Key, the first book that is opened will be assigned to that shortcut key.

A work around would be to make another macro to call the right macro based on the workbook name. This might require you to replace some of the Workbooks and Worksheets to ActiveWorkbook or ActiveWorksheet. But please try it out first.

This is just example code I tried out but please edit it to your needs. I tried it by making two files a.xlsm and b.xlsm. difference is a.xlsm has msgbox "a" in first line and b.xlsm has msbox "b" in the first line. You check by opening a.xlsm first then b.xls. Then assign the same shortcut key. You will see that when you run Ctrl+M in b.xlsm that the macro that will be run will be in a.xlsm with msgbox "A" but then it will call the correct macro in b.xlsm.

Short Answer

This code makes use of Application.Run which runs the macro from a specific workbook. create a helper macro that will check filename then append macro name.

So when you ctrl+m, regardless which workbook run_code is run from, it will go back to activeworkbook and run the macro, in this case plaster, from that workbook. Also it will populate the Public Variable with the activeworkbook value.

https://www.rondebruin.nl/win/s9/win001.htm some samples of application.run

By far the easiest solution for you just add Caller Macro

Caller Macro:

Sub call_changeTheCode() ' add to all workbooks, that have changeTheCode macro then assign to Ctrl + m

Application.Run ActiveWorkbook.Name & "!changeTheCode"

End Sub

Proof of concept, not your actual code, use above Caller Macro:

Public varvar As String
Sub run_code() 'assignt to shortcut key CTRL+M both macros in a.xlsm and b.xlsm
MsgBox "a" ' to test create another workbook and change this to b
file_path = ActiveWorkbook.Path 'just to check path
file_name = ActiveWorkbook.Name 'gets the file name
MsgBox file_path 'msgbox the file_path
MsgBox file_name 'msgbox the file_name
MsgBox file_name & "!plaster" 'msgbox the file name plus macro name, in your instance it would be "ABC.xlsm'!macro_name" please note the format
Application.Run file_name & "!plaster"

End Sub

Sub plaster() 'this is the test macro that will show correct macro in workbook is called
varvar = ActiveWorkbook.Name
MsgBox "hi this is workbook " & varvar

End Sub