Clipboard turn empty running worksheet.unprotect - workaround ?

[Marie J-son]

Hi,

I have a worksheet I need to have protected, and when I run code to restore
cell format, I unprotect it temporary. However, this causes clipboard to
turn empty and since I run the code both at worksheet_activate and
worksheet_change events, the result is that you can't copy-paste between
sheets.

At line below, the clipboard turn empty ...
"Application.Worksheets(XshtName).UnProtect Password:=sPass"

1/ Is there a way to work around this and still use the code both at
worksheet_change and worksheet_activate?
2/ The reason why I have it on worksheet_activate is because I want to
restore cell formats when drag and drop occure from other sheets - - it
doesn't trig the worksheet_change event, I'm told. Right? Workaround?


/Kind regards

 

[Jim Rech]

I think your best approach is to turn on worksheet protection with the
UserinterfaceOnly parameter set to true.

For example, protect Sheet1 manually. If A1 is locked and you run this code
it will fail of course:

Sub CopyCell()
Range("C1").Copy Range("A1")
End Sub

But first run this code and then try it:

Sub UserIntefaceOnly()
Sheet1.Protect , , , , True
End Sub


The only catch is that UserInterfaceOnly setting is not saved with a
workbook. So when it is re-opened it has to be set again by macro. You
should use your Auto_Open or Workbook_Open code to do this.

--
Jim
| Hi,
|
| I have a worksheet I need to have protected, and when I run code to
restore
| cell format, I unprotect it temporary. However, this causes clipboard to
| turn empty and since I run the code both at worksheet_activate and
| worksheet_change events, the result is that you can't copy-paste between
| sheets.
|
| At line below, the clipboard turn empty ...
| "Application.Worksheets(XshtName).UnProtect Password:=sPass"
|
| 1/ Is there a way to work around this and still use the code both at
| worksheet_change and worksheet_activate?
| 2/ The reason why I have it on worksheet_activate is because I want to
| restore cell formats when drag and drop occure from other sheets - - it
| doesn't trig the worksheet_change event, I'm told. Right? Workaround?
|
|
| /Kind regards
|
|

 

[Marie J-son]

Hi,

This sounds ok - will there be any security fallbacks doing this? The code
is today protected with a looong password (for the Rob Bovey utilities
password cracker etc. had to work to long for it to be likly to use it),
both workbook and worksheets. Will this affect the possiblity of the user
to come into the code?

/Kind regards

 

[Marie J-son]

When testing, it seems like a lot of other changes empty the clipboard
also - when I copy a cell and change number format or font.bold with code
like below, the paste icon grays out right on the line. Is this true? Can I
not change anything without clipboard get empty.

The mystery closes up when I have one worksheet I run a lot of code on
including Font and numberformat that doesn't gray out the paste button -
until i come to last line " Exit Sub". When the code stop, the paste button
gray out.

Testsubs - I tested it on a brand new workbook with same result - 1/ Copy 2/
Run code 3/ can't paste anymore:

Sub test()
Application.ActiveCell.Font.Bold = True
'Application.ActiveCell.NumberFormat = "@"
End Sub

What is happening?

/Regards

 

[Jim Rech]

Yes, the clipboard empties on the slightest pretext. Defer your copy as
late as possible.

--
Jim
| When testing, it seems like a lot of other changes empty the clipboard
| also - when I copy a cell and change number format or font.bold with code
| like below, the paste icon grays out right on the line. Is this true? Can
I
| not change anything without clipboard get empty.
|
| The mystery closes up when I have one worksheet I run a lot of code on
| including Font and numberformat that doesn't gray out the paste button -
| until i come to last line " Exit Sub". When the code stop, the paste
button
| gray out.
|
| Testsubs - I tested it on a brand new workbook with same result - 1/ Copy
2/
| Run code 3/ can't paste anymore:
|
| Sub test()
| Application.ActiveCell.Font.Bold = True
| 'Application.ActiveCell.NumberFormat = "@"
| End Sub
|
| What is happening?
|
| /Regards
|
| -----------------------
|
|
| "Marie J-son" <[email protected]> skrev i meddelandet
| | > Hi,
| >
| > I have a worksheet I need to have protected, and when I run code to
| > restore cell format, I unprotect it temporary. However, this causes
| > clipboard to turn empty and since I run the code both at
| > worksheet_activate and worksheet_change events, the result is that you
| > can't copy-paste between sheets.
| >
| > At line below, the clipboard turn empty ...
| > "Application.Worksheets(XshtName).UnProtect Password:=sPass"
| >
| > 1/ Is there a way to work around this and still use the code both at
| > worksheet_change and worksheet_activate?
| > 2/ The reason why I have it on worksheet_activate is because I want to
| > restore cell formats when drag and drop occure from other sheets - - it
| > doesn't trig the worksheet_change event, I'm told. Right? Workaround?
| >
| >
| > /Kind regards
| >
|
|