我创建了一个工作簿,只有在使用以下代码运行时才显示UserForm
Private Sub Workbook_Open()
Application.Visible = False
UserForm1.Show
End Sub
初始化UserForm时,它会打开另一个工作簿(WHITEBOOK.xls) . 将数据输入表单并且用户单击“提交”按钮后,数据将插入到已打开的此工作簿中(我已省略该代码,但它可以正常工作)
Private Sub UserForm_Initialize()
Set wb = Workbooks.Open("S:\!Data Tracking\WHITEBOOK.xls")
wb.Sheets("Jobs with Correct Template").Activate
If IsEmpty(wb.Worksheets("Jobs with Correct Template").Range("A3").Value) = True Then
Set SheetNameCellRange = wb.Worksheets("Jobs with Correct Template").Range("A2")
Else
wb.Worksheets("Jobs with Correct Template").Activate
Set SheetNameCellRange = wb.Worksheets("Jobs with Correct Template").Range("A2", Range("A2").End(xlDown))
End If
End Sub
当用户关闭UserForm时,我使用此代码关闭表单,并关闭UserForm的工作簿 .
Private Sub UserForm_Terminate()
Unload Me
For Each wb In Application.Workbooks
If (StrComp(wb.Name, "Whitebook Data Entry (Final).xlsm", vbTextCompare) = 0) Then
wb.Close
End If
Next
End Sub
我遇到的问题是,如果WHITEBOOK.xls工作簿已经打开,我无法打开UserForm . 我得到一个运行时错误'9':下标超出范围,它在UserForm_Initialize()Sub的这一行失败 .
wb.Sheets("Jobs with Correct Template").Activate
当我打开WHITEBOOK工作簿以检查它是否已经打开时,我已经尝试添加一个条件,如果是这样,要使其处于活动状态,但我仍然遇到同样的问题 .
我用来测试工作簿是否打开的代码是
Private Sub UserForm_Initialize()
Dim wkb As Workbook
On Error Resume Next
Set wkb = Application.Workbooks("S:\!Data Tracking\WHITEBOOK.xls")
If wkb Is Nothing Then
Set wb = Workbooks.Open("S:\!Data Tracking\WHITEBOOK.xls")
Else
wb.Sheets("Jobs with Correct Template").Activate
End If