Snasui Excel Forum

Code: Select all

Do
    Pswd = InputBox("Please enter Password.")
Loop Until Pswd = "123"

    ActiveSheet.Unprotect Password:=Pswd

Code: Select all

Sub CopyNewSheet()
Dim i As Integer
Dim strNameSheet As String
Dim Pswd As String
'Do
strNameSheet = InputBox("Please enter sheet name.")
   If strNameSheet = "" Then
       Exit Sub
    End If
'Loop Until strNameSheet <> ""

For i = 1 To Worksheets.Count
        If UCase(Worksheets(i).Name) = UCase(strNameSheet) Then
           MsgBox "Please try again"
           Exit Sub
        End If
Next

    Worksheets("Report").Copy After:=Worksheets(Worksheets.Count)
    ActiveSheet.Name = strNameSheet
    
'  If Password = "" Then
'       Exit Sub
'   End If

Do
    Pswd = InputBox("Please enter Password.")
Loop Until Pswd = "123"

    ActiveSheet.Unprotect Password:=Pswd
    ActiveSheet.Cells.Copy
    ActiveSheet.Cells.PasteSpecial xlPasteValues
    Worksheets("Report").Range("L7:L16").Copy
    ActiveSheet.Range("L7:L16").PasteSpecial xlPasteFormulas
    Worksheets("Report").Range("K16").Copy
    ActiveSheet.Range("K16").PasteSpecial xlPasteFormulas
    Worksheets("Report").Range("J17").Copy
    ActiveSheet.Range("J17").PasteSpecial xlPasteFormulas
    Application.CutCopyMode = False
    ActiveSheet.Protect
End Sub

Code: Select all

   .....
    strPassword = Right(ActiveSheet.Name, 2)
    ActiveSheet.Protection.AllowEditRanges("Range1").Delete
    ActiveSheet.Protection.AllowEditRanges.Add _
    Title:="Range1", _
    Range:=Range("B7:I37,E3:G3"), _
    Password:=strPassword
    .....

Code: Select all

Sub SearchSheet()
Dim strNameSheet As String
strNameSheet = InputBox("Please enter sheet name.")
   If strNameSheet = "" Then
       Exit Sub
      End If
 Worksheets(strNameSheet).Select
 End Sub

Code: Select all

Sub SearchSheet()
Dim i As Integer
Dim strNameSheet As String
strNameSheet = InputBox("Please enter sheet name.", "Search Name Sheet")
   If strNameSheet = "" Then
       Exit Sub
      End If
      For i = 1 To Worksheets.Count
        If UCase(Worksheets(i).Name) <> UCase(strNameSheet) Then
           MsgBox "Please try again"
           Exit Sub
        End If
Next
Worksheets(strNameSheet).Select  
End Sub

Code: Select all

Sub SearchSheet()
Dim i As Integer
Dim iCount As Integer
Dim strNameSheet As String
strNameSheet = InputBox("Please enter sheet name.", "Search Name Sheet")
    If strNameSheet = "" Then
        Exit Sub
    End If
    For i = 1 To Worksheets.Count
        If UCase(Worksheets(i).Name) = UCase(strNameSheet) Then
            iCount = iCount + 1
        End If
    Next
    If iCount > 0 Then
        Worksheets(strNameSheet).Select
    Else
        MsgBox "Please try again"
    End If
End Sub

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
Dim strNameSheet As String
Application.EnableEvents = False
   If Target.Address = "$E$25" Then
        strNameSheet = Target.Address
       Worksheets(strNameSheet).Select
    End If
 Application.EnableEvents = True
End Sub

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$E$5" Then
        Worksheets(Target.Value).Select
    End If
End Sub

Code: Select all

Private Sub ComboBox1_Click()
Dim MySheet() As String
Dim MyTotalSheets As String
Dim i As Integer
MyTotalSheets = Application.Worksheets.Count
ReDim MyName(MyTotalSheets)
    For i = 1 To MyTotalSheets
           MyName(i) = Sheets(i).Name
           ComboBox1.AddItem MyName(i)
    Next i
Worksheets(ComboBox1.Value).Select
End Sub

Code: Select all

Sub GoComboBox1()
Sheets("INDEX").ComboBox1.DropDown
End Sub

Code: Select all

UserForm1.ComboBox1.Visible = True

Code: Select all

UserForm1.ComboBox1.Visible = False

Code: Select all

Private Sub ComboBox1_Click()
Dim MyName() As String
Dim MyTotalSheets As Integer
Dim i As Integer
MyTotalSheets = Application.Worksheets.Count
ReDim MyName(MyTotalSheets)
    For i = 1 To MyTotalSheets
           MyName(i) = Sheets(i).Name
           ComboBox1.AddItem MyName(i)
    Next i
Worksheets(ComboBox1.Value).Select
End Sub

Code: Select all

Sub FilComboBox()
Dim MyName() As String
Dim MyTotalSheets As Integer
Dim i As Integer
MyTotalSheets = Application.Worksheets.Count
ReDim MyName(MyTotalSheets)
    For i = 1 To MyTotalSheets
           MyName(i) = Sheets(i).Name
           ActiveSheet.ComboBox1.AddItem MyName(i)
    Next i
End Sub

Code: Select all

Private Sub ComboBox1_Click()
    Worksheets(ComboBox1.Value).Select
End Sub