Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

How to run macro on protected worksheet

0

Hi,

attached workbook Sheet 9,11,13 are passcord protected how to run macro   module 5 in order to copy invoice (sheet 13) details to sheet  9 & 11

 sheett 9,11,13 Password is "1234"

Thanks

Answer
Discuss

Answers

0
Selected Answer

Ramzein

You haven't really followed the recent  separate advice from @Varaiatus but please try Module 5 in the attached file.

I have:

1) Moved all statements unlocking sheet protection to the start of Module 5 (and all re-apply protection statements to the end). You can "comment them out" and (working with unprotected sheets, perfect your code before enabling those stanements again (as Variatus suggested)

2) Commented out the sheet.activate statements- you don't really need them

3) Modified the formatting in Invoice Main (as I discussed in your earler question)

3) Removed merged cells from Invoice (but apply Wrap Text to address cell E23) so a mofified (single) statement can clear the necessary cells in that sheet.

Full code is below; see changed bits in bold. Again, I strongly recommend you work with unprotected code until everything is working well.

Sub Invtotal()

Dim ERow As Integer
Dim myPassword As String

'unlock sheets with same password

myPassword = "1234"

Sheet9.Unprotect Password:=myPassword
Sheet11.Unprotect Password:=myPassword
Sheet13.Unprotect Password:=myPassword

' Sheet9.Activate


ERow = Sheet9.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
With Sheet9

    .Cells(ERow, 1).Value = Sheet13.Cells(22, 8).Value
    .Cells(ERow, 2).Value = Sheet13.Cells(20, 5).Value
    .Cells(ERow, 3).Value = Sheet13.Cells(20, 8).Value
    .Cells(ERow, 4).Value = Sheet13.Cells(21, 8).Value
        .Cells(ERow, 5).Value = Day(.Cells(ERow, 4))
        .Cells(ERow, 6).Value = MonthName(Month(.Cells(ERow, 4)))
        .Cells(ERow, 7).Value = Year(.Cells(ERow, 4))
    .Cells(ERow, 8).Value = Sheet13.Cells(21, 5).Value
    .Cells(ERow, 9).Value = Sheet13.Cells(45, 7).Value
    .Cells(ERow, 10).Value = Sheet13.Cells(49, 9).Value

End With




Dim ItemCount As Integer

' Sheet11.Activate


ERow = Sheet11.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

With Sheet11

For ItemCount = 34 To 43

    If Sheet13.Cells(ItemCount, 4) <> "" Then

        .Cells(ERow, 1).Value = Sheet13.Cells(22, 8).Value
        .Cells(ERow, 2).Value = Sheet13.Cells(20, 5).Value
        .Cells(ERow, 3).Value = Sheet13.Cells(20, 8).Value
        .Cells(ERow, 4).Value = Sheet13.Cells(21, 8).Value
            .Cells(ERow, 5).Value = Day(.Cells(ERow, 4))
            .Cells(ERow, 6).Value = MonthName(Month(.Cells(ERow, 4)))
            .Cells(ERow, 7).Value = Year(.Cells(ERow, 4))
        .Cells(ERow, 8).Value = Sheet13.Cells(21, 5).Value
     'add the item data
        .Cells(ERow, 9).Value = Sheet13.Cells(ItemCount, 4).Value
        .Cells(ERow, 10).Value = Sheet13.Cells(ItemCount, 5).Value
        .Cells(ERow, 11).Value = Sheet13.Cells(ItemCount, 7).Value
        .Cells(ERow, 12).Value = Sheet13.Cells(ItemCount, 8).Value




     ERow = ERow + 1

    Else
        GoTo Finish

    End If

Next ItemCount

End With

Finish:




'SavePDF Macro


    'ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
    "D:\bills\" & Sheet13.Range("H22").Value, OpenAfterPublish:=True


MsgBox "Copied invoice details to " & Sheet13.Name & " and " & Sheet9.Name

' Clear_invoice Macro

' Sheet13.Activate

    Sheet13.Range("E20:e23,H20:I20,c34:h43").ClearContents 'but not Amount column

    'New Invoice.
    Sheet13.Range("H22").Value = Sheet13.Range("H22").Value + 1



'protect sheets with same password
Sheet9.Protect Password:=myPassword, Contents:=True
Sheet11.Protect Password:=myPassword, Contents:=True
Sheet13.Protect Password:=myPassword, Contents:=True

End Sub
Discuss

Discussion

John

Thanks, but why it goes to inventory sheet (11)  when the mocto run?
it supposed to be back on invoice sheet (13) 

This is my earlier question may be i couldnt clarify exactly what i wanted to

 Since im just the beginer for VBA im not sure whther macro runs on protected sheet this way
Ramzein (rep: 8) Nov 26, '20 at 10:20 am
Ramzein

The sheet you are left on (after the macro runs) will depend on what the nacro code says. If you run Module 5 of my file above from the STOCK INWARD sheet (for example), you will end up at the same place, i.e. still on STOCK INWARD. That's because I told you I'd commented out the lines like SheetXX.Activate so it doesn't chnage the active sheet while it does the copying

If you always want to end up on Sheet13 after running a macro, then put a line 
Sheet13.Activate
near the end of the macro (or uncomment the disabled one that's there in my Module 5) and you'll get there.

  To improve your VBA knowledge, I suggest you do some of the great Tutorials on TeachExcel. Try the one covering VBA activation using sheet names like "Invoice" or its Index number- go to Activate or Navigate to a Worksheet using Macros VBA in Excel
John_Ru (rep: 6102) Nov 26, '20 at 11:55 am
John

Thank you so much for everything  and your answer is selected
Ramzein (rep: 8) Nov 26, '20 at 12:07 pm
Glad it worked, thanks
John_Ru (rep: 6102) Nov 26, '20 at 2:35 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login