×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Unprotect another Workbook via Macro

Unprotect another Workbook via Macro

Unprotect another Workbook via Macro

(OP)
Hello:

I am writing a macro to insert data from one workbook (source) into another workbook comprised of one sheet (target)that is protected.

In code snippet below, I activate my source worksheet, define my copy range (1st With/End With block) and copy it.

Then I open my protected target workbook, activate it, try to unprotect it. Then the 2nd With/End With block inserts and pastes the lines of data into the target. Then reprotect and save and close the target workbook.

Data does not get pasted to target, just rows of blank cells.

The code, as shown with the Protect and Unprotect lines commented out, works perfectly if the taget workbook is unptotected.

Any ideas?

Thanks

-Pete

' Compile a range of output for valid entries, then write data to archive
' Need at least 1 valid entry so that ranges below are valid thus check if b at least zed
If b >= 0 Then
    
    Workbooks("TubingCost.xls").Activate
    
    With Worksheets("Summary")
        .Range(.Cells(a, 1), .Cells(a + b, c)).Select
        Selection.Copy
    End With
    
    Workbooks.Open DatabasePath
    Workbooks("TubingHistory.xls").Activate
    ' Worksheets("TubingHist").Unprotect BUG- ONLY WRITES LINES OF BLANK DATA
  
    With Worksheets("TubingHist")
        .Range(.Cells(a1, 1), .Cells(a1 + b, c)).Select
        Selection.Insert Shift:=xlDown
    End With
    
    ' Worksheets("TubingHist").Protect BUG- ONLY WRITES BLANK DATA
    
    Workbooks("TubingHistory.xls").Close SaveChanges:=True
    
    Workbooks("TubingCost.xls").Activate
    Worksheets("Summary").Activate
    
End If

RE: Unprotect another Workbook via Macro

You get blanks because Excel "forgets" what you copied when you  don't immediately paste the data. So the easiest for you is to open and unprotect the target workbook first, and then do the copy and paste operation:

    Workbooks.Open DatabasePath
    Worksheets("TubingHist").Unprotect
  
    Workbooks("TubingCost.xls").Activate
    With Worksheets("Summary")
        .Range(.Cells(a, 1), .Cells(a + b, c)).Copy
    End With
    
    Workbooks("TubingHistory.xls").Activate
    With Worksheets("TubingHist")
        .Range(.Cells(a1, 1), .Cells(a1 + b, c)).Insert Shift:=xlDown
    End With
    
    Worksheets("TubingHist").Protect
    
    Workbooks("TubingHistory.xls").Close SaveChanges:=True
    
    Workbooks("TubingCost.xls").Activate
    Worksheets("Summary").Activate

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

RE: Unprotect another Workbook via Macro

(OP)
Joerd:

Thanks for the tip. Yes, I have the code working now, odd that Excel forgets, it was a behaviour I did not expect.

Thanks

-Pete

RE: Unprotect another Workbook via Macro

Pardon my rookie question, nut can this code be modified to unprotect a password protected worksheet and reprotect with same password?

Thanks,
Jeff

RE: Unprotect another Workbook via Macro

(OP)

Jeff:

Funny you should ask. In my same macro, I later chose to alter a worksheet that was protected, and then reprotect after I was done with the modifications. But you must know the password that the sheet was originally protected with (ie MyPassword below)

Worksheets("Summary").Unprotect Password:="MyPassword"
' Do operations needed here
Worksheets("Summary").Protect Password:="MyPassword"

Hope this helps, from one rookie to another.

-Pete

RE: Unprotect another Workbook via Macro

Pete,
Thanks.  This looks like it will solve my problem!  This forum is incredibly useful, don't you think!

Thanks again,
Jeff

RE: Unprotect another Workbook via Macro

Just a tip:
The statement workbook.protect password:="whatever" reveals the protection password to whomever cares to look in the VBA code. Unless you don't care, but then why use a password at all , you should also protect your VBA project with a password (Tools/VBA Project Properties/Protection tab, from the VB editor).

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources