为什么我的代码输入第二个else，即使它是假的？

Question

Private Sub txt_Number_of_sites_Change() 
    If IsNumeric(txt_Number_of_sites.Value) And txt_Number_of_sites.Value <= 20 And txt_Number_of_sites > 0 Then 
     Dim i As Integer 
     Dim j As Integer 
     Dim x As Integer 
     Dim AWS 
     Dim diff 'diffrence between AWS and number of sites 
     AWS = ActiveWorkbook.Sheets.Count - 1 
     If AWS = 1 Then 
      If txt_Number_of_sites.Value = 1 Then 
       IT_Staff.Visible = xlSheetVisible 
       ElseIf txt_Number_of_sites.Value > 1 Then 
       IT_Staff.Visible = xlSheetVisible 
       For i = 2 To txt_Number_of_sites.Value 
       Sheets("IT Staff").Select 
       Sheets("IT Staff").Copy After:=Sheets(Sheets.Count) 
       Next 
      End If 
     ElseIf AWS < txt_Number_of_sites.Value Then 
      diff = txt_Number_of_sites - AWS 
      For i = 1 To diff 
      Sheets("IT Staff").Select 
      Sheets("IT Staff").Copy After:=Sheets(Sheets.Count) 
      Next 
     ElseIf AWS > txt_Number_of_sites.Value Then 
      x = 0 
      j = Sheets.Count 
      For x = j To 19 Step -1 '# <- please note the change here 
      Application.DisplayAlerts = False 
      Sheets(x).Delete 
      Application.DisplayAlerts = True 
      Next x 
     End If 
     ElseIf txt_Number_of_sites.Value = "" Then 
     Exit Sub 
     Else 
     msgbox "Please enter a numberic value from 1-20" 
    End If 
End Sub 

Answer 1

请尝试下面的修改后的代码。我确定了很多你肯定会遇到问题的地方。一点建议，永远不要留下一个不合格的参考（即“表格（”表名“，我们从未指向工作簿）

另外，我发现一个地方，你有一个早产的”End If“ “T追溯到一个独立的if语句。这可能是造成你的问题的一部分。

Private Sub txt_Number_of_sites_Change() 
    ' You use this number repeatedly, just declare a variable. 
    Dim SiteCount As Long 
    If IsNumeric(txt_Number_of_sites.Value) Then 
     SiteCount = txt_Number_of_sites.Value 
    Else 
     ' Handle this here. What should happen if text is entered instead? 
     ' For now, we will just exit the routine. 
     Exit Sub 
    End If 

' Once SiteCount is declared as long, we no longer need to check for IsNumeric. 
If SiteCount <= 20 And SiteCount > 0 Then 
    ' Use long instead of Integer. Or use double if you need non-round numbers. 
    Dim AWS As Long 

    ' Qualify this Workbook reference. Absolutely avoid ActiveWorkbook unless absolutely neccesary. 
    AWS = ActiveWorkbook.Sheets.Count - 1 

    ' Consider using ThisWorkbook if you are running this code in the same workbook being targeted. 
    ' AWS = ThisWorkbook.Sheets.Count - 1 

    Dim i As Long 
    Dim diff As Long 'diffrence between AWS and number of sites 
    If AWS = 1 Then 
     If SiteCount = 1 Then 
      ' Is this a CodeName? 
      IT_Staff.Visible = xlSheetVisible 
     ElseIf txt_Number_of_sites.Value > 1 Then 
      IT_Staff.Visible = xlSheetVisible 
     End If 

     For i = 2 To SiteCount 
      ' Qualify these sheet references. Or use the codename. 
      ' There's no need to select the sheet before copying. 
      'Sheets("IT Staff").Copy After:=Sheets(Sheets.Count) 

      ThisWorkbook.Sheets("IT Staff").Copy After:=Sheets(Sheets.Count) 
      ' Or 
      ' IT_Staff.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count) 
     Next 
    ElseIf AWS < SiteCount Then 
     diff = SiteCount - AWS 
     For i = 1 To diff 
      ' See my above note about qualification 
      'Sheets("IT Staff").Copy After:=Sheets(Sheets.Count) 
      ThisWorkbook.Sheets("IT Staff").Copy After:=Sheets(Sheets.Count) 
     Next 
    ElseIf AWS > SiteCount Then 
     Dim x As Long 
     x = 0 
     ' Qualify this reference as well. 
     'j = Sheets.Count 
     j = ThisWorkbook.Sheets.Count 

     ' Why would you declare a j variable just to not use it? Just set x = ThisWorkbook.Sheets.Count instead. 
     ' No need to repeatedly toggle Application.DisplayAlerts 
     ' Also, you shouldnt hardcode the 19 here. Set it equal to Sheets.Count - 1 or something similar. Otherwise you will 
     ' inevitably run into a Subscript Out of Range error. 
     Application.DisplayAlerts = False 
     For x = j To 19 Step -1 '# <- please note the change here 
      Sheets(x).Delete 
     Next 
     Application.DisplayAlerts = True 
     ' It looks like you have a premature End If here causing an issue. 
     ' End If <<<<<<<<<<<<<<< 

     ' This is impossible since we are using a number for the condition in the first place. 
     ' ElseIf txt_Number_of_sites.Value = "" Then 
     Exit Sub 
    End If 
Else 
    MsgBox "Please enter a numeric value from 1-20" 
End If 
End Sub