Sheet1.Unprotect does not work in xl 97? (Sorry, I still nee

charlie79 · Apr 9th, 2002, 07:21 AM

I created an excel app in xl2k. I used sheet protection throughout my vba code, without problems. Then going to xl 97 I find that the unprotect and protect method of sheets does not work. I am getting a Method 'Protect' of object '_Worksheet' failed runtime error.

Sheet1.Protect or Sheet1.Unprotect

?????

[ This Message was edited by: charlie79 on 2002-04-09 06:21 ]

[ This Message was edited by: charlie79 on 2002-04-09 07:39 ]

Dave Hawley · Apr 9th, 2002, 07:25 AM

Hi Charlie

It could be due to the method used to run the code. If using a ActiveX CommandButton set it's "TakeFocus*******" Property to False.

charlie79 · Apr 9th, 2002, 07:41 AM

Just tried that Dave, thanks for the suggestion but no go. Any other ideas?

charlie79 · Apr 9th, 2002, 07:43 AM

A little side note, I started with an empty xl97 workbook, added a commandbutton and put:

commandbutton1.takefocus******* = false
sheet1.protect

in the commandbutton1_click event and that produces same results. So I can assume this isn't an issue with creating the original workbook in xl2k.

????

Dave Hawley · Apr 9th, 2002, 08:03 AM

Hi Charlie

It would be worth setting this Property at 'Design-time' rather than 'Run-time'

I am pretty certain there are no real issues with using a Sheets CodeName in 97 over 2000.

charlie79 · Apr 9th, 2002, 08:10 AM

Here's the problem with that however Dave. My app is going to be used by sensitive users. By that I mean, somewhat illeterate as far as Excel goes. I do need the sheet protected, however, that sheet is being refreshed every 5 secs (I'm using an Add-In for another application that goes to a server to retrieve current values and my code then places those current values into cells). That said, on every refresh, I must unprotect first, then protect when done. Also, I have a comboboox that will change values in cells through the combobox's change event. The thing is, I know doing it at run-time works, in xl2k.

???

Dave Hawley · Apr 9th, 2002, 08:14 AM

Charlie, I am not suggesting to not 'Protect/Unprotect' at 'Run-Time', I mean the "TakeFocus*******" Property of the Control running the code.

charlie79 · Apr 9th, 2002, 08:20 AM

Sorry Dave, looks like that is going to work! Thanks a million. Do you have an explanation of why I didn't have to set that property for xl2k?

Thanks again!

charlie79 · Apr 9th, 2002, 08:29 AM

Oops!
Guess what. A combobox does not have the property, TakeFocus*******. That is the main concern, when the user change the combobox, the sheet needs to be unprotected, update values in the cells, and then protected.

Dave Hawley · Apr 9th, 2002, 08:48 AM

Hi Charlie

Not too sure why, but it's a case of the fix of a problem in 97 causing problems of its own in 2000.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 9th, 2002, 07:21 AM	#1
charlie79 Board Regular Join Date: Feb 2002 Posts: 97	I created an excel app in xl2k. I used sheet protection throughout my vba code, without problems. Then going to xl 97 I find that the unprotect and protect method of sheets does not work. I am getting a Method 'Protect' of object '_Worksheet' failed runtime error. Sheet1.Protect or Sheet1.Unprotect ????? [ This Message was edited by: charlie79 on 2002-04-09 06:21 ] [ This Message was edited by: charlie79 on 2002-04-09 07:39 ]

Apr 9th, 2002, 07:25 AM	#2
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi Charlie It could be due to the method used to run the code. If using a ActiveX CommandButton set it's "TakeFocus*******" Property to False.

Apr 9th, 2002, 07:41 AM	#3
charlie79 Board Regular Join Date: Feb 2002 Posts: 97	Just tried that Dave, thanks for the suggestion but no go. Any other ideas?

Apr 9th, 2002, 07:43 AM	#4
charlie79 Board Regular Join Date: Feb 2002 Posts: 97	A little side note, I started with an empty xl97 workbook, added a commandbutton and put: commandbutton1.takefocus******* = false sheet1.protect in the commandbutton1_click event and that produces same results. So I can assume this isn't an issue with creating the original workbook in xl2k. ????

Apr 9th, 2002, 08:03 AM	#5
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi Charlie It would be worth setting this Property at 'Design-time' rather than 'Run-time' I am pretty certain there are no real issues with using a Sheets CodeName in 97 over 2000.

Apr 9th, 2002, 08:10 AM	#6
charlie79 Board Regular Join Date: Feb 2002 Posts: 97	Here's the problem with that however Dave. My app is going to be used by sensitive users. By that I mean, somewhat illeterate as far as Excel goes. I do need the sheet protected, however, that sheet is being refreshed every 5 secs (I'm using an Add-In for another application that goes to a server to retrieve current values and my code then places those current values into cells). That said, on every refresh, I must unprotect first, then protect when done. Also, I have a comboboox that will change values in cells through the combobox's change event. The thing is, I know doing it at run-time works, in xl2k. ???

Apr 9th, 2002, 08:14 AM	#7
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Charlie, I am not suggesting to not 'Protect/Unprotect' at 'Run-Time', I mean the "TakeFocus*******" Property of the Control running the code.

Apr 9th, 2002, 08:20 AM	#8
charlie79 Board Regular Join Date: Feb 2002 Posts: 97	Sorry Dave, looks like that is going to work! Thanks a million. Do you have an explanation of why I didn't have to set that property for xl2k? Thanks again!

Apr 9th, 2002, 08:29 AM	#9
charlie79 Board Regular Join Date: Feb 2002 Posts: 97	Oops! Guess what. A combobox does not have the property, TakeFocus*******. That is the main concern, when the user change the combobox, the sheet needs to be unprotected, update values in the cells, and then protected.

Apr 9th, 2002, 08:48 AM	#10
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi Charlie Not too sure why, but it's a case of the fix of a problem in 97 causing problems of its own in 2000.