Geekzone: technology news, blogs, forums
Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.

Hawkes Bay
8477 posts

Uber Geek
+1 received by user: 5

Mod Emeritus
Lifetime subscriber

Topic # 14456 4-Jul-2007 15:09
Send private message

I want to check the value of a cell (which, through a VB script is given a value based on a Windows variable), and then affect access to the workbook based on that value.

I can get to the point where the value is either true (displaying a '1'), or false (displaying a '0').

What I want to do, is, if the value is false, render the workbook unusable, or totally uneditable, or unviewable (you get my drift anyway) - I dont mind them seeing whats there, but I dont want them to be able to update any cells.

The worksheet is already password protected, and only about 10 cells are unlocked for editing. I guess locking those 10 cells out from being edited, if the above value is false, would be my ultimate goal.

I understand that anyone capable of unlocking Excel workbooks would get around this in 10 seconds flat, but that is not of a concern to me.

Create new topic
170 posts

Master Geek
+1 received by user: 8

  Reply # 76908 4-Jul-2007 15:49
Send private message

Not tested, but you could try the following:

- In the Visual Basic editor add an event handler for Worksheet_Change on the sheet that has the value you want to test
- Declare a Range variable that represents the 10 cells that need to be protected
- Check the value of your cell (eg: A1) to see if the value is 0, and set the Locked property of the 10 cell Range to true
- If the cell is 1, then set the Locked property of the 10 cell Range to false.

(If that doesn't work, you may need to call the Protect method again to ensure that Excel recognises that those cells need to be locked.)


Create new topic

Twitter »

Follow us to receive Twitter updates when new discussions are posted in our forums:

Follow us to receive Twitter updates when news items and blogs are posted in our frontpage:

Follow us to receive Twitter updates when tech item prices are listed in our price comparison site:

Geekzone Live »

Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.

Geekzone Live »

Our community of supporters help make Geekzone possible. Click the button below to join them.

Support Geezone on PressPatron

Are you subscribed to our RSS feed? You can download the latest headlines and summaries from our stories directly to your computer or smartphone by using a feed reader.

Alternatively, you can receive a daily email with Geekzone updates.