Turn a range of cells into 'read only'

[Michael Lanier]

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

 

[Gord Dibben]

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

 

[Michael Lanier]

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 Dibben]

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

 

[Michael Lanier]

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 Dibben]

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

 

[Michael Lanier]

Gord,

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

Michael

 

[Gord Dibben]

Thanks

Post back if need more help on this subject.


Gord