此问题已在此处有答案:
Test or check if sheet exists(23答案)
昨天关门了。
我目前正在运行一个宏,它复制一个模板工作表,接受用户的输入,重命名工作表,并将用户的输入放在工作表中。
除了检查工作表名称是否已经存在的函数外,所有功能似乎都正常工作。实际的“工作表已经存在”工作,但是,在提示我这个错误之前,它由于某种原因复制了TEMPLATE工作表。
下面是实际宏的代码(受影响的区域是:Sheets(“TEMPLATE”).Copy After:=Sheets(“TEMPLATE”))
'============================================================================
'Button to load new GSA Form
'============================================================================
Private Sub CommandButton1_Click()
Const cstrTitle As String = "Create a new GSA worksheet"
Const cstrPrompt As String = "Enter the GSA number for the new worksheet"
Dim projName As String
Dim projAddress As String
Dim projDate As Date
Dim strInput As Variant 'Input value from user (GSA Number)
Dim strDefault As String: strDefault = "" 'Sets default value for inputbox
Dim strInputErrorMessage As String 'Error message
Dim booValidateOK As Boolean: booValidateOK = False
On Error GoTo HandleError
Do
strInput = ActiveSheet.Range("C9").Value
projName = ActiveSheet.Range("C6").Value
projAddress = ActiveSheet.Range("C7").Value
projDate = ActiveSheet.Range("C8").Value
If Len(strInput) = 0 Then GoTo HandleExit
GoSub ValidateInput
If Not booValidateOK Then
If vbCancel = MsgBox(strInputErrorMessage & "Retry?", vbExclamation + vbOKCancel) Then GoTo HandleExit
End If
Loop While Not booValidateOK
Sheets("TEMPLATE").Copy After:=Sheets("TEMPLATE") 'Copy Template Sheet, places the copy after the template sheet
ActiveSheet.Name = strInput 'Renames the new sheet to the user's input
ActiveSheet.Range("C5").Value = projName 'Inputs Project Name to new sheet
ActiveSheet.Range("C6").Value = projAddress 'Inputs Project Address to new sheet
ActiveSheet.Range("C7").Value = projDate 'Inputs Project Date to new sheet
ActiveSheet.Range("C8").Value = strInput 'Inputs GSA # to new sheet
ThisWorkbook.Worksheets("MASTER").Range("C6").Value = "" 'name' 'Deletes inputs from MASTER sheet
ThisWorkbook.Worksheets("MASTER").Range("C7").Value = "" 'address'
ThisWorkbook.Worksheets("MASTER").Range("C8").Value = "" 'date'
ThisWorkbook.Worksheets("MASTER").Range("C9").Value = "" 'gsa #'
HandleExit:
Exit Sub
HandleError:
MsgBox Err.Description
Resume HandleExit
ValidateInput:
If SheetExists(strSheetName:=strInput) Then
strInputErrorMessage = "Sheet already exists. "
Else
booValidateOK = True
End If
Return
End Sub
下面是检查工作表是否已经存在的函数
Public Function SheetExists(strSheetName As Variant, Optional wbWorkbook As Workbook) As Boolean
If wbWorkbook Is Nothing Then Set wbWorkbook = ActiveWorkbook 'or ThisWorkbook - whichever appropriate
Dim obj As Object
On Error GoTo HandleError
Set obj = wbWorkbook.Sheets(strSheetName)
SheetExists = True
Exit Function
HandleError:
SheetExists = False
End Function
2条答案
按热度按时间nzk0hqpo1#
试试这个:
然后像这样使用:
Excel VBA If WorkSheet("wsName") Exists
x7yiwoj42#