Lock a row with an entry in Col B on saving or closing workbook

**glenin** · 02-15-2009, 06:56 PM

Hi,

My first posting so apologies in advance if I offend the rules.

I have a spreadsheet for recording and summarising particular transactions. Column B records the date and Col C details the transaction type (from a data valdation list). Using conditional formatting the cell (in the same row) to whch the transaction value is to be entered is highlighted.

Other cells in the row contain formulas and are locked and the sheet is password protected.

On saving or closing the workbook I want to lock all the cells (used or unused) in any row in which there is a date in column B. How can I do this?

I'm new to vba so detail of what code goes where will be appreciated.

Thanks.

**shg** · 02-15-2009, 08:09 PM

Press Alt+F11 to open the Visual Basic Editor, double-click ThisWorkbook in the Project window, and paste this code:

Please Login or Register  to view this content.

Change the sheet name and password appropriately.

Save as type xlsm.

**glenin** · 02-15-2009, 08:57 PM

Thanks for the speedy response.

After saving and on trying to close the worrkbook I'm getting the following message.

Run-time error '1004'
Unable to set the Locked property of the Range class.

When I hit the debug button on the message panel the 4th last line of the code is highlighted

Please Login or Register  to view this content.

When I hover the mouse cursor over the IsNumeric(cell.Value) section of that highlighed code line a floating message box box identifies the cell value as the first of the header rows in column B (all the header rows are text).

Maybe the IsNumeric bit is the issue? If so, is there some sort of "is anything" statement that can be used instead - locking the (text) header rows is not a problem.

**glenin** · 02-15-2009, 10:11 PM

I've progressed this a bit further.

I've got around what appears to be the text problem by simply inserting a hidden column and assigning a numeric value if the cell in column B is either text or numeric. I've changed the column reference in the code to the hidden column and now rows with a value in column B are locking as required.

Two remaining problems.

1. After saving and trying to close the workbook I'm getting the "Do you want to the changes" message box.

2. On re-opening the workbook the formulas in protected cells in rows that do not have a date in column B are now unlocked.

Any asistance with 1 & 2 will be appeciated.

**shg** · 02-16-2009, 12:17 AM

Post a workbook.

**glenin** · 02-16-2009, 01:53 AM

Workbook attached with your code included but no other changes, Password set to pw.

**shg** · 02-16-2009, 10:46 AM

Try this. Merged cells in Excel are a PITA.

Please Login or Register  to view this content.

**glenin** · 02-16-2009, 06:21 PM

Many thanks for your patience and help.

Rows are now locking but two problems remain.

I've copied the code to the Workbook panel of ThisWorkbook. After saving & on hitting either the workbook or excel close button (top right) the rows with a date in col B lock ok but neither the workbook or excel will actually close.

However if I change the second last line of the code to Cancel = False the workbook and excel can be closed in the normal way. Then on reopening the workbook the rows with a date in col B are locked ok, but the cells in columns H & I in rows without a date in col B are now unlocked. Because columns H & I contain formulas I need them to be locked at all times.

Any suggestions on how to keep cols H & I locked? What does the Cancel = True or Cancel = False line of the code do? (sorry if this is a dumb question but I'm struggling with this vba stuff)

**shg** · 02-16-2009, 06:38 PM

The Cancel line was a holdover from testing, sorry. Try this:

Please Login or Register  to view this content.

**glenin** · 02-16-2009, 07:18 PM

That's the trick - works perfectly.

Thanks again for your effort.

**shg** · 02-16-2009, 07:42 PM

You’re welcome. Would you please mark the thread as Solved?

Click the Edit button on your first post in the thread

Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes

**glenin** · 02-17-2009, 04:15 AM

Done - apologies for oversight but I'm getting the hang of this.

Lock a row with an entry in Col B on saving or closing workbook

LinkBack

Thread Tools

Rate This Thread

Display

Lock a row with an entry in Col B on saving or closing workbook

Re: Lock a row with an entry in Col B on saving or closing workbook

Re: Lock a row with an entry in Col B on saving or closing workbook

Re: Lock a row with an entry in Col B on saving or closing workbook

Re: Lock a row with an entry in Col B on saving or closing workbook

Re: Lock a row with an entry in Col B on saving or closing workbook

Re: Lock a row with an entry in Col B on saving or closing workbook

Re: Lock a row with an entry in Col B on saving or closing workbook

Re: Lock a row with an entry in Col B on saving or closing workbook

Re: Lock a row with an entry in Col B on saving or closing workbook

Re: Lock a row with an entry in Col B on saving or closing workbook

Re: Lock a row with an entry in Col B on saving or closing workbook

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions