Snasui Excel Forum

Code: Select all

 Private Sub CommandButton2_Click()
    
   ' TotalSheets = ThisWorkbook.Worksheets.Count
    'For i = 1 To TotalSheets
    'If ThisWorkbook.Worksheets(i).Name = TextBox1.Text Then
     'UserForm3.Show
    'Exit Sub
    'End If
    'Next
    
    If TextBox1.Text = "" Or TextBox2.Text = "" Then
       UserForm2.Show
    
    Else
    Do
    Dim b As String
        r = r + 1
        c = r - 1
        b = TextBox1.Text
     
    Loop Until Cells(r, 2) = ""
    Cells(r, 1) = c
    Cells(r, 2) = b
    Cells(r, 3) = TextBox2.Text
    Sheets("template").Copy After:=Worksheets("template")
    ActiveSheet.Name = b

    With Sheets(b)
      Cells(1, 3) = b
      Cells(2, 3) = TextBox2.Text
    End With
    
    With Sheets("list")
        .Hyperlinks.add Anchor:=.Cells(r, 4), Address:="", SubAddress:= _
        "#'" & b & "'!A1", TextToDisplay:="Link"
    End With
   Unload Me
    End If
    
End Sub

Code: Select all

    If TextBox1.Text = "" Or TextBox2.Text = "" Then
       UserForm2.Show
    Else
    Do
    Dim b As String
        r = r + 1
        c = r - 1
        b = TextBox1.Text
 If Application.WorksheetFunction.CountIf(Range("B2:B100"), b) > 0 Then
       UserForm3.Show
        Exit Sub
End If
    Loop Until Cells(r, 2) = ""
'...Other Code