Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Turn a range of cells into 'read only'
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Turn a range of cells into 'read only'
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Turn a range of cells into 'read only'
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Turn a range of cells into 'read only'
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Turn a range of cells into 'read only'
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Turn a range of cells into 'read only'
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Turn of 'Read Only' | Excel Worksheet Functions | |||
How do you turn on the voice read back of excel cells in spreadshe | Excel Worksheet Functions | |||
Turn off read only attribute on shortcut | Excel Programming | |||
Turn off Read-Only | Excel Programming | |||
Turn off pasting in range of cells | Excel Programming |