时间戳

首页 > 解决方案 > 多个工作簿使用时保持打开相同的表单

excel - 多个工作簿使用时保持打开相同的表单

问题描述

在此处输入图像描述我正在寻找解决方案、选项或更好地理解这一点：

我在每个客户的工作簿上捕获数据。为了防止不同数量的工作表并保持每个客户端的数据完整性，我从默认工作簿创建新工作簿，其中包含所有相同的表单和工具。我将一个新客户端添加到一个连续的客户端列表中，然后保存旧的默认工作簿，使其具有最新的客户端列表，然后将新创建的工作簿保存到我生成的文件夹中的静态位置。

在我的工作簿中的表单上，我有一个客户字段，该字段与填充组合框的客户列表范围相关联。当用户更改客户端并触发更改事件时。我正在尝试尽可能无缝地将工作簿切换到新选择的客户端的工作簿。

用户表单为当前工作簿和用户选择的工作簿分配变量和目录。VBA 打开所需位置的工作簿，然后打开两个工作簿。而现在的问题。

  1. 我激活用户选择的工作簿并尝试从该工作簿打开相同的表单，然后关闭当前工作簿，使新选择的工作簿保持打开状态，并在工作表中显示正确的客户数据，以便用户可以继续。我收到有关运行时 1004 无法运行未找到或不存在的宏的错误。由于当我关闭工作簿时表单在当前工作簿中打开，所以整个事情都关闭了。我的新工作簿在那里，但表单没有运行。

有一个更好的方法吗？我读过一些关于使用户表单模块化的内容，但不确定这是否是正确的思路。

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

还有一些关于添加“解决方案转换器”或类似宏错误并使其可信的东西。我也不知道这是否是正确的轨道。

提前致谢

更新：2/1/21 尝试添加更多代码和图像，以确保我不会错过任何帮助的重要信息。

'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

' 需要打开文件以获取进程的名称并工作表导入进程数据将成为 Workbooks.Open (directory & fileName) 的一部分' - 这可以为切换的客户端打开正确的工作簿

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

'所附图像显示了打开的工作簿和两个工作簿中的相同 MeetingMinutesForm1。但由于某种原因，它不会从第一个工作簿和表单中打开工作簿和表单。

标签： excelvbauserform

解决方案

试试这条线：

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

Application.Run命令将宏的名称作为第一个参数；以下参数用于宏的参数。

编辑

采用的解决方案：

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

推荐阅读