excel - Keep same form open when multiple workbook use it

Question

enter image description hereI'm looking for a solutions, options or to understand this better:

I am capturing data on a workbook per client. As to prevent a varying number of sheets and preserve data integrity for each client, I create new workbooks from a default workbook that has all the same forms and tools within it. I added a new client to a continuing list of clients and then save the old default workbook so it has the latest client list and then save the newly created workbook to a static location in folders I have generated.

On my form in my workbook I have a client field tied to a range of the list of clients populating a combo box. When the user changes the client and triggers the change event. I'm trying to switch the workbook to the workbook for the newly selected client as seamless as possible.

The user form assigns the variables and directory for the current workbook and the one the user selected. VBA opens the workbook I need for the location I want, so then have both workbooks open. And now the problem.

1. I activate the user selected workbook and try to open the same form from that workbook and then close the current workbook, leaving the new selected workbook open with the correct clients data in the worksheets so the user can continue. I get an error about run time 1004 can not run macro it was not found or doesn't exist. Since the form is opened in the current workbook when i close the workbook the whole thing closes. My new workbook is there but the form is not running.

Is there a better way to do this? I read something about making the userform modular but not sure if this is the right line of thought.

Sub SwitchClient(clientname As String)


Dim directory As String
Dim fileName As String
Dim wrk As Workbook
Dim switchwrk As Workbook
Dim sheet As Worksheet

Dim routine As String
Dim passclient As String

Dim wbname As String
Dim filenamep0 As String
Dim filenamep1 As String
Dim filenamep2 As String

Dim sh As String
Dim shname


wbname = ThisWorkbook.Name
filenamep0 = "BA - Briefcase"
filenamep1 = clientname
filenamep2 = "BA - Tools"


Set wrk = ThisWorkbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False


directory = CreateObject("wscript.shell").specialfolders("Desktop") & "" & 
(filenamep0) & "" & (filenamep1) & "" & (filenamep2) & ""


fileName = Dir(directory & "EB_Analyst_Tool - " & filenamep1 & ".xlsm") 


Workbooks.Open (directory & fileName)

Set switchwrk = Workbooks("EB_Analyst_Tool - " & filenamep1 & ".xlsm")

'Sub I'm trying to run from the new workbook before closing the current workbook - Meeting Minutes sub is userform.show
routine = "MeetingMinutes"

switchwrk.Activate

'Can't get the right syntax to run the macro from the newly opened workbox
Run "'" & switchwrk.Name & "'!" & routine(passclient)
Application.Run fileName & "!MeetingMinutes"

'Save existing workbook with updated clientlist

wrk.Save

'Close current workbook - Everything closes except new opened workbook so i 
need to start the form again.

wrk.Close

There was also a something about an add in "solution converter" or something like that for the macro error and making it trusted. I don't know if that is the right track either.

Thanks in advance

UPDATE: 2/1/21 Trying to add some more of the code and images to make sure I'm not missing any essential Information those helping.

'Immediately after changeing the Combobox to a different client.

Private Sub cbmmclient_Change()

Dim mmclientname As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

mmclientname = cbmmclient

SwitchClient


Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

' SwitchClient is is a subroutine in the Module - FormActions
'With Example1 and 2  i get : Run- Time Error '1004' Cannot run the 
macro'EB_Analyst_Tool - Client.xlsm!MeetingMinutes'. The macro may not be 
available in this workbook or all macros maybe disabled

Sub SwitchClient()


Dim directory As String
Dim fileName As String
Dim wrk As Workbook
Dim switchwrk As Workbook
Dim sheet As Worksheet
Dim total As Integer
Dim routine As String
Dim passclient As String

Dim wbname As String
Dim filenamep0 As String
Dim filenamep1 As String
Dim filenamep2 As String
Dim filenamep3 As String
Dim filenamep4 As String
Dim sh As String
Dim shname
Dim openrng As Range

wbname = ThisWorkbook.Name
filenamep0 = "BA - Briefcase"
filenamep1 = MeetingMinutesForm1.cbmmclient
filenamep2 = "BA - Tools"

Dim prffilename

Set wrk = ThisWorkbook


directory = CreateObject("wscript.shell").specialfolders("Desktop") & "" & 
(filenamep0) & "" & (filenamep1) & "" & (filenamep2) & ""


'Add the Process List document name here and be able to generate an error if 
the list isn't here and prevent accidental xlm files being loaded
fileName = Dir(directory & "EB_Analyst_Tool - " & filenamep1 & ".xlsm") 
'Requires a Static name to be found and Excel sheets must be name accordingly

' Need to open the file to get the name of the process and sheet the Import Process Data will be a part of Workbooks.Open (directory & fileName) ' - This works and opens the correct workbook for the switched client

Set switchwrk = Workbooks("EB_Analyst_Tool - " & filenamep1 & ".xlsm")

'routine = "MeetingMinutes" '- Tried this because I got an error that it 
expected a varible
'switchwrk.Activate ' Tried to make sure the correct workbook was active when 
the code ran.
'wrk.Activate  ' Tried to make sure the correct workbook was active when teh 
code ran.


'Application.Run ("EB_Analyst_Tool - Hawaii - DOT.xlsm! MeetingMinutes") 
'Example 1 I tried to use.

Run switchwrk.Name & "!" & routine, (filenamep1) 'Example 2 I tried to use


'Determine if you want to save the current workbook before closing

wrk.Save

'Close current workbook

 wrk.Close


End Sub

'The attached image shows the workbook opened and the same MeetingMinutesForm1 in both Workbooks. But for some reason it won't open the workbook and the form from the first workbook and form.

question from:https://stackoverflow.com/questions/65915343/keep-same-form-open-when-multiple-workbook-use-it

Answer 1

Try this line:

Run switchwrk.Name & "!" & routine, passclient

The Application.Run command puts as first argument the name of the macro; the followings arguments are for the macro's arguments.

---

EDIT

Adopted solution:

Application.Run("'" & YourStringVariable & "'!GetUserform")