I want to turn a range of Sheet1 cells into 'read only' when my file
is saved. The conditions are as follows:

If range A1:A100 (a range of dates) = TODAY()
Then range C1:C100 which corresponds to TODAY() is made 'read only'

I don't know if it matters, but the sheet will be password protected.
Can you help with a macro for this? Thanks.

Michael

What do you mean by "read only"?

Do you want the cells to be values only?

What values are in C1:C100?

Do you want the C1:C100 cells to become locked if any corresponding cell in
column A matches TODAY?


Gord Dibben MS Excel MVP

On Tue, 27 Apr 2010 10:56:20 -0700 (PDT), Michael Lanier
wrote:

I want to turn a range of Sheet1 cells into 'read only' when my file
is saved. The conditions are as follows:

If range A1:A100 (a range of dates) = TODAY()
Then range C1:C100 which corresponds to TODAY() is made 'read only'

I don't know if it matters, but the sheet will be password protected.
Can you help with a macro for this? Thanks.

Michael

Thanks Gord,

I'm using a business spreadsheet. When I'm finished for the day,
hence the TODAY() entries located in column A, I want all my
corresponding entries in column C to become fixed for every
transaction entered for TODAY() when I save the file. In truth, there
will be other columns other than just C, but they won't be adjacent.
I hope this helps.

Michael

Still not clear to me.

Column A has what? =TODAY() in every cell? Some cells?

Column C has what? Formulas?

"Fixed" means "values only"?

I'm thick and need more detail. Can you post a representative sample of
what you have in say A1:A10 and C1:C10

You will need code, I would imagine

For each cell in column A that equals Today
Convert corresponding cell in column C to value

Would that work?


Gord

On Tue, 27 Apr 2010 12:52:23 -0700 (PDT), Michael Lanier
wrote:

Thanks Gord,

I'm using a business spreadsheet. When I'm finished for the day,
hence the TODAY() entries located in column A, I want all my
corresponding entries in column C to become fixed for every
transaction entered for TODAY() when I save the file. In truth, there
will be other columns other than just C, but they won't be adjacent.
I hope this helps.

Michael

Gord,

Picture a check register. Basically, the idea is to enter today's
date in column A. My computerized register balances my daily entries
and returns a balance for each transaction in column C. When I close
the file at the end of the day, I want the amount reflected in C to
become permanent so that it cannot be changed at a later date.
Therefore, any entry that reflects today's date in A will become
permanent (fixed) in C. Sorry about the confusion. I hope this
helps.

Michael

Like pulling teeth<g

"Cannot change" to me means "locked"

Locked means sheet protection must be applied.

When you save at end of day you can run event code to look down column A and
find today's date or earlier.

Then lock the cells in column C

Does that work for you?

First select all cells on Sheet1 and clear the "locked" property.

Then paste this code into Thisworkbook module.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim c As Range
Dim rng1 As Range
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
Set rng1 = ws.Range(Cells(1), Cells(Rows.Count, 1).End(xlUp))
ws.Unprotect Password:="justme"
For Each c In rng1
If c.Value = Date Or c.Value < Date Then
c.Offset(0, 2).Locked = True
Else: c.Offset(0, 2).Locked = False
End If
Next
ws.Protect Password:="justme"
ThisWorkbook.Save 'save no ask
End Sub


Gord

On Tue, 27 Apr 2010 14:18:41 -0700 (PDT), Michael Lanier
wrote:

Gord,

Picture a check register. Basically, the idea is to enter today's
date in column A. My computerized register balances my daily entries
and returns a balance for each transaction in column C. When I close
the file at the end of the day, I want the amount reflected in C to
become permanent so that it cannot be changed at a later date.
Therefore, any entry that reflects today's date in A will become
permanent (fixed) in C. Sorry about the confusion. I hope this
helps.

Michael

Gord,

Thanks for your patience and help. It appears to be working.

Michael

Thanks

Post back if need more help on this subject.


Gord

On Wed, 28 Apr 2010 07:40:56 -0700 (PDT), Michael Lanier
wrote:

Gord,

Thanks for your patience and help. It appears to be working.

Michael