Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to lock column values
Hi,
I would like to be able to use a subroutine to lock cells in columns that have column headers of specific dates. In this sittuation, I have quarterly dates interjected between monthly dates in the cells in the row that serves as the column headers. For the columns that have a header of a monthly date, say "Jul-2009", I would like the cells in a certain range of that column say row 8 through row 11 to be unlocked, and for a columns that have a header of a quarterly date, say "Q2-2009", I would like the cells in that range (rows 8-11) to be locked. Any thoughts? Thanks! -- Hugo |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to lock column values
If those "date" values are simply text, you may be able to use something like:
Option Explicit Sub testme() Dim myCell As Range Dim myHeader As Range With Worksheets("sheet1") .unprotect password:="topsecret" Set myHeader = .Range("A1", .Cells(1, .Columns.Count).End(xlToLeft)) For Each myCell In myHeader.Cells If LCase(myCell.Value) Like "q#-####" Then Intersect(myCell.EntireColumn, .Rows(8).Resize(4)).Locked = True ElseIf LCase(myCell.Value) Like "???-####" Then Intersect(myCell.EntireColumn, .Rows(8).Resize(4)).Locked = False End If Next myCell .protect password:="topsecret" End With End Sub Hugo wrote: Hi, I would like to be able to use a subroutine to lock cells in columns that have column headers of specific dates. In this sittuation, I have quarterly dates interjected between monthly dates in the cells in the row that serves as the column headers. For the columns that have a header of a monthly date, say "Jul-2009", I would like the cells in a certain range of that column say row 8 through row 11 to be unlocked, and for a columns that have a header of a quarterly date, say "Q2-2009", I would like the cells in that range (rows 8-11) to be locked. Any thoughts? Thanks! -- Hugo -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lock Axis Values? | Charts and Charting in Excel | |||
Any way to Lock format but not the values? | Excel Discussion (Misc queries) | |||
How do I lock cell value even if other values change | Excel Discussion (Misc queries) | |||
how do I lock values in excel | Excel Worksheet Functions | |||
Lock cells for combo box values only | Excel Programming |