How to get file path, switch to macro file then back to the original active workbook to execute code

Question

This is my first query posted online. I hope someone can help. Forgive me in advance, as I am no expert and in the very early stages of learning excel vba.

I would appreciate if you could provide any help on the following. Any suggestions of improvement of codes is always welcome too!.

Overview of the objective of code:

a) User will open the excel file 'Entity List Change' which has the vba and table contents - find and replace.

b) User will open the file they want to have text replaced by another value. The user will then click on Ctrl Q to execute the code held in 'Entity List Change' - I presume adding this on the options section of the macro will do this.

c) The code would then store the workbook name and file path of the one they want the details to be changed.

d) The code will then ask the user to confirm that the file which the code should be executed against using yes or no query.

e) If yes, then it will go to the 'Entity List Change' file to obtain array then excute the code against the original workbook held in the first sub.

I have the following problems:

1) I cannot seem to recall the workbook after switching workbooks to Entity Change name file – it cannot read the original workbook stored in first sub procedure. (Note I would not know what file is being used in the first instance).

2) Msg box recognising yes selection by user it ignores running the function and goes straight to the else – do nothing procedure. – why I have no idea?? It recognises Yes as 0 and No as 0

**Excel VBA Code:**

Option Explicit
Public retvalue As String ' hold the stored value so I can reuse it in a sub procedure.
Dim finalFile As Workbook

Public Sub GetFilePath() ' want to hold variable publicly so it can be reused.
    Dim strFileAndPath As String
    retvalue = ActiveWorkbook.FullName
    strFileAndPath = retvalue
    Set finalFile = ActiveWorkbook
    retvalue = Application.ActiveWorkbook.Path
    'finalFile = Application.ActiveWorkbook.Name this creates a 438 error, cannot use - unsure how to fix this?
    'Debug.Print retvalue  '********************(first check)
    'personal note if you have the string in the public then you do not need to hold it anywhere else.
    'this part of the code works, retvalue hold the path and strFileAndPath hold the complete path and file name. FinalFile does store the activeworkbook at a workbook. - checked Locals window!
End Sub

Sub Multi_FindReplace()
'Note this code will not work if there are formula errors, these cells will be omitted from the change. - tested!

Application.ScreenUpdating = False
Application.DisplayStatusBar = False

Dim sht As Worksheet
Dim fndList As Integer
Dim rplcList As Integer
Dim tbl As ListObject
Dim myArray As Variant
Dim ActiveOrgFilePath As String
Dim answer As Integer
Dim TempArray As Variant
Dim x As Integer
Dim strFileName As Workbook

'Store and get the file name of the active workbook.
Call GetFilePath

''Debug.Print retvalue '********************(second check)
'Convert the string (file path) to workbook name
retvalue = Split(retvalue, "\")(UBound(Split(retvalue, "\")))

'Confirm that this is the file you would like the change to occur.
MsgBox retvalue & vbNewLine & "Please confirm that this is the file name you wish to change the entity names?", vbYesNo, "Change Entity Names"

'if you wanted the file name to be held after the msg
If answer = vbYes Then
    Debug.Print answer '********************(second check)
'file with table array identified to obtain array information:
    Windows("Entity List Change.xlsm").Activate  'Obtain code and array from Macro workbook.
'Create variable to point to your table
    Set tbl = Worksheets("Sheet1").ListObjects("EntityList1")
'Create an Array out of the Table's Data
    Set TempArray = tbl.DataBodyRange
    myArray = Application.Transpose(TempArray)
'Designate Columns for Find/Replace data
    fndList = 1
    rplcList = 2
'Then to go to the original workbook held at the beginning held in 'retvalue', to loop through all sheets in the workbook find values as per those held in Entity List Change.xlsm,
'once found then replace the values in workbook 'retvalue'
    Set finalFile = ActiveWorkbook
'finalFile = Application.ActiveWorkbook.Name cannot use, not recognised in first sub procedure?
'Windows(finalFile).Activate
'Workbooks("retvalue").Select
'Windows("finalFile").Activate
'None of these are not working??

'Loop through each item in Array lists
    For x = LBound(myArray, 1) To UBound(myArray, 2)
'Loop through each worksheet in ActiveWorkbook (skip sheet with table in it)
        For Each sht In ActiveWorkbook.Worksheets
            If sht.Name < tbl.Parent.Name Then
                sht.Cells.Replace What:=myArray(fndList, x), Replacement:=myArray(rplcList, x), _
                LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
                SearchFormat:=False, ReplaceFormat:=False
            End If
        Next sht
    Next x
    MsgBox "The Entity name change has been completed." & vbNewLine & "Thank you for your patience."
Else
'do nothing
    MsgBox "The Entity name change has not been completed." & vbNewLine & "Please find and open the correct file to make the change."
End If

Application.ScreenUpdating = True
Application.DisplayStatusBar = True
End Sub

Answer 1

Welcome to StackOverflow, and congratulations to asking this detailed. Regarding your question(s):

1. As far as I can see, retvalue has a proper value stored inside (You display it in the MsgBox, and you're not saying that it won't work there). I only see a problem in the code you commented out: Workbooks("retvalue").Activate will look for a Workbook named "retvalue". To reference the value currently stored inside the variable retvalue, use Workbooks(retvalue).Select without the quotation marks.
2. To store the result of a MsgBox inside a variable, you need to actively do so. If you look at your code, you will notice that you never assign a value to answer. Change this to answer = MsgBox(retvalue & vbNewLine & ...). Notice the brackets!

One last thought: In VBA, .Activate isn't your friend very often. In almost every case you'll be better off if you store the thing you're activating inside a variable (e.g. Dim ws as Worksheet), assign that variable (Set ws = ThisworkBook.Sheets("Sheetname")) and then address it by using the .Value-property (e.g. ws.Cells(1,1).Value). This will save you a lot of confusion and preventable errors. For more information on this. see this thread on SO.