Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 14th, 2004, 11:12 AM
Registered User
 
Join Date: Apr 2004
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Editing Unlocked cells in VBA

Hi,

I need some inputs on how to,
1) Write into a protected, but unlocked cells through VBA
2) Get the password of the excel sheet in the VB code directly.

1) I had protected my excel sheet with a password. When the sheet is protected, all the cells becomes read-only. So I unlocked few cells that I had to edit by using 'Format Cells-> Protection -> Uncheck Locked' in the excel sheet. So now I am able to edit the values in those unlocked cells through Excel without unprotecting the sheet.

The question here is, when I try to edit those cells through VBA code like assigning some value to that cell, it gives an error stating that the cell is protected. Why does it behave so inspite of the cells being unlocked. Let me know the steps to edit values in protected sheet and unlocked cells through VBA.

One more strange behavior related to this which I observed is, I tried to unlock a range of cells and then protected the sheet. When I tried to edit the cells one by one, the first editing worked, and the rest failed. Also when I unprotected the sheet and saw the cells states, the cells were locked back. Could someone explain this behavior as well?

2) Since I was stuck up with the above scenario, I went with 'Unprotect' -ing the sheet with the password supplied when protecting the sheet, edited the values in those cells and then 'Protect'-ed it back. Here I had to hardcode the password. So it is visible and each time the password in the excel sheet is changed, here too it has to be changed. I have protected my VBA code, but still I would like to know, is there any way in VBA to avoid this.

TIA,
sham


 
Old April 16th, 2004, 10:07 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 150
Thanks: 0
Thanked 0 Times in 0 Posts
Default

It seems to me you've placed your protection code the wrong place/event.

Try substituting the protection code with a MsgBox stating which event it's placed in, eg

MsgBox "Protection ON from Worksheet_Activate() event"

and other places necessary. Then try edit your sheet and see what would have triggered the protection code if it had been there.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Sorting Cells using VBA james_sellwood Excel VBA 7 October 8th, 2012 01:58 PM
Formatting the cells from VBA yogeshyl Excel VBA 1 July 20th, 2007 12:26 AM
Problem with defining cells in Excel VBA sunny76 Excel VBA 6 July 6th, 2005 04:59 AM
Editing Unlocked cells in VBA hems VB How-To 0 April 14th, 2004 11:08 AM
Editing Unlocked cells in VBA hems VBScript 1 April 14th, 2004 10:42 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.