Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Password stopping macro working (2003 SP2)

    Good morning

    I have several worksheets that are password protected but when the user form is used they momentarily unlock whilst updating, however on one Worksheet I just cannot get this to work.

    Without a password this macro runs as it should, once the password part is added though it chokes on the highlighted line

    Sub Autoshape42_click()

    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False

    Worksheets("Lists").Unprotect Password:="lists"

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    Worksheets("Lists").Protect Password:="lists"

    Next
    End Sub

    Private Sub TxtCost_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    txtCost = Format(txtCost, "0.00")
    End Sub

    Private Sub cmbCost_Click()
    Dim Irow As Long
    Dim c As Long
    Dim Ws As Worksheet

    Set Ws = Worksheets("Lists")

    Irow = Ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

    With Ws

    <span style="background-color: #FFFF00; color: #000000; font-weight: bold">.Cells(Irow, 1).Value = Me.txtBoxName</span hi> .Cells(Irow, 2).Value = Me.txtCost
    .Cells(Irow, 3).Value = Me.txtSupplier

    End With

    Me.txtBoxName.Value = ""
    Me.txtCost.Value = ""
    Me.txtSupplier = ""
    Me.txtBoxName.SetFocus

    End Sub
    Private Sub cmbCloseForm_Click()
    Unload Me
    End Sub

    I have tried everything I can think of but I cannot see why I am getting an error 1004 here, the text box definitely has that name and I have deleted and reinserted it dozens of times just in case but I just cannot see where it is wrong

    Any ideas please

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Password stopping macro working (2003 SP2)

    In Autoshape42_Click you unprotect the sheet, then reprotect it but you don't do that in cmbCost_Click. Have you tried unprotecting the sheet at the beginning of cmbCost_Click and reprotecting it at the end?

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Password stopping macro working (2003 SP2)

    Thanks Hans

    No I have not, I will try now

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Password stopping macro working (2003 SP2)

    Cheers Hans

    That did it

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •