Unprotect range, copy and paste range, protect range

Jeevz_87

New Member
Joined
Sep 21, 2021
Messages
37
Office Version
  1. 365
Platform
  1. Windows
Hi All,


I'm currently experimenting with unprotecting and protecting workbooks using VBA but have been unsuccessful in getting it to work for one of my workbooks.

In row 1 which is a hidden, fully formatted blank row, I'm trying to unprotect a portion of that row (BP1:DR1), copy and paste the entire row, paste it to the bottom of the table below and then protect that specific portion again.

I intedn for this function to be incorporated within the new row macro that I'm currently using.

The existing VBA Im using is fairly simple but keeps incurring an error. I would imagine that it would need to change to suit this new requirement;

VBA Code:
Sub Rev_Add_new_row_2()



Dim tbl As ListObject
Dim rw As Range

    
    Set tbl = Sheets("Master Input").ListObjects(1)
    Set rw = tbl.ListRows.Add.Range
    
    Sheets("Master Input").Range("A1:HS1").Copy rw



End Sub

Where in this would I need to fit the protect/unprotect feature in this case?

Any help with this would be greatly appreciated!

Thanks in advance!


Jeevz
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Hi there...

Do you mean perhaps something like below? "1234" is the password you use to protect and unprotect sheet...

VBA Code:
Option Explicit
Sub Rev_Add_new_row_2()
    ActiveSheet.Unprotect "1234"
    Dim tbl As ListObject
    Dim rw As Range
    Set tbl = Sheets("Master Input").ListObjects(1)
    Set rw = tbl.ListRows.Add.Range
    Sheets("Master Input").Range("A1:HS1").Copy rw
    ActiveSheet.Protect "1234"
End Sub
 
Upvote 0
Solution
Hi Jimmy,

I've just run this code and it seems to work - I even adapted it for one of the other macros I've developed.

To answer your question though, I really required an understanding of the placement of the protect/unprotect function but whereas a typical example would protect the whole sheet, I required something that would lock off specific cells.

The idea was to make sure that the end-user could enter values into unprotected parts of the work sheet but not in a specific ranges where formulae are present.

Everything seems to be working great so far in protecting the range I mentioned! You're a wizard Jimmy but will let you know if something falls over :LOL:

Many thanks


Jeevz
 
Upvote 0
Your welcome and thanks for the feedback....

Well basically one would unprotect and then have the user enter info and edit sheet and after they are finished then one would protect again. At the moment I only know how to do this on the whole sheet and not individual ranges... but I do think it would be possible with some more thinking...🍻😎
 
Upvote 0
To answer your question though, I really required an understanding of the placement of the protect/unprotect function but whereas a typical example would protect the whole sheet, I required something that would lock off specific cells.

The idea was to make sure that the end-user could enter values into unprotected parts of the work sheet but not in a specific ranges where formulae are present.

Come to think of it you could always lock the cells you do not want edited and unlock the cells you would want edited and then when you protect the sheet just specify whether they are able to select locked or unlocked cells...

Screenshot 2022-09-06 152919.png
 
Upvote 0

Forum statistics

Threads
1,223,958
Messages
6,175,636
Members
452,662
Latest member
Aman1997

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top