Need VB to allow pasting in a protected sheet

papagino

New Member
Joined
Dec 12, 2004
Messages
28
I guys!

I need a VB command that would allow me to be able to paste in a protected worksheet.

Thanks

Dan
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Glaswegian

Well-known Member
Joined
Oct 14, 2003
Messages
1,487
Dan

You'll need to unprotect and then protect, something like

Sub Test()

Sheets("Sheet1").Unprotect "My Password"
'do your paste here
Sheets("Sheet1").Protect "My Password"
End Sub


Regards
 

papagino

New Member
Joined
Dec 12, 2004
Messages
28
Thanks for your reply Glaswegian,

Let me explain what I'm trying to accomplish...

I have the following VB command in my Worksheets:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Locked = False Then
Me.Protect Password:="cabin"
End If

If Target.Count = Target.EntireRow.Cells.Count Then
Application.OnKey "{Delete}", ""
Else
Application.OnKey "{Delete}"
End If

If Target.Count = Target.EntireRow.Cells.Count Then
Me.Unprotect Password:="cabin"
End If


End Sub


The spreadsheets are parts of an Estimating and management system for construction contractors. It is composed of many spreadsheets and I need to be able to copy and paste from one sheet to and other.

The above VB commands allow me to do the functions that I want except that if you copy cell(s) from one spreadsheet and try to paste it in and other spreadsheet, the paste function is not available.

Any ideas of the line I can add to the VB to do this?

Thanks

Dan
 

Glaswegian

Well-known Member
Joined
Oct 14, 2003
Messages
1,487
By using the Selection Change event, every time you click on any cell, the sheet will lock, unless you select an entire row. Therefore you will not be able to paste. If you have unlocked an entire row then you will be able to paste in that row but the sheet will then remain unprotected. I'm not sure exactly what your aims are here, so I'm not sure what to suggest.

Regards
 

Forum statistics

Threads
1,147,846
Messages
5,743,520
Members
423,801
Latest member
paulj4177

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
Top