Locking/unlocking cells based on column headers
 

I am using the following code for a subroutine, which outputs a header of
dates (in the format:
mmm-yy,mmm-yy,mmm-yy,qq-yy,mmm-yy,mmm-yy,mmm-yy,qq-yy,...) in a row based on
the starting date and the number of months (duration). I was wondering if
there is a way to get this same subroutine to make it so that all the cells
in the columns that correspond to the date format (qq-yy) are locked and all
the cells in the columns that correspond to the date format (mmm-yy) are
unlocked. Any thoughts

Sub DatesWithQuarters()
Dim X As Long, Col As Long
Dim StartDate As Variant, Duration As Variant
Col = 5 ' This is the starting column for the list
StartDate = InputBox("Tell me the starting month and 4-digit year")
Duration = InputBox("How many months should be listed?")
If IsDate(StartDate) And Len(Duration) 0 And _
Not Duration Like "*[!0-9]*" Then
If Duration 0 Then
StartDate = CDate(StartDate)
For X = 0 To Duration - 1
Cells(1, Col).NumberFormat = "mmm-yyyy"
Cells(1, Col).Value = DateAdd("m", X, StartDate)
If Month(DateAdd("m", X, StartDate)) Mod 3 = 0 Then
Col = Col + 1
Cells(1, Col).NumberFormat = "@"
Cells(1, Col).Value = Format(DateAdd("m", X, _
StartDate), "\Qq-yyyy")
End If
Col = Col + 1
Next
End If
End If
End Sub

--
Hugo

Locking/unlocking cells based on column headers
 

For X = 0 To Duration - 1
Cells(1, Col).NumberFormat = "mmm-yyyy"
Cells(1, Col).Value = DateAdd("m", X, StartDate)
cells(1, col).locked = false '<-- added
If Month(DateAdd("m", X, StartDate)) Mod 3 = 0 Then
Col = Col + 1
Cells(1, Col).NumberFormat = "@"
Cells(1, Col).Value = Format(DateAdd("m", X, _
StartDate), "\Qq-yyyy")
cells(1, col).locked = true '<-- added
End If
Col = Col + 1
Next X



Hugo wrote:

I am using the following code for a subroutine, which outputs a header of
dates (in the format:
mmm-yy,mmm-yy,mmm-yy,qq-yy,mmm-yy,mmm-yy,mmm-yy,qq-yy,...) in a row based on
the starting date and the number of months (duration). I was wondering if
there is a way to get this same subroutine to make it so that all the cells
in the columns that correspond to the date format (qq-yy) are locked and all
the cells in the columns that correspond to the date format (mmm-yy) are
unlocked. Any thoughts

Sub DatesWithQuarters()
Dim X As Long, Col As Long
Dim StartDate As Variant, Duration As Variant
Col = 5 ' This is the starting column for the list
StartDate = InputBox("Tell me the starting month and 4-digit year")
Duration = InputBox("How many months should be listed?")
If IsDate(StartDate) And Len(Duration) 0 And _
Not Duration Like "*[!0-9]*" Then
If Duration 0 Then
StartDate = CDate(StartDate)
For X = 0 To Duration - 1
Cells(1, Col).NumberFormat = "mmm-yyyy"
Cells(1, Col).Value = DateAdd("m", X, StartDate)
If Month(DateAdd("m", X, StartDate)) Mod 3 = 0 Then
Col = Col + 1
Cells(1, Col).NumberFormat = "@"
Cells(1, Col).Value = Format(DateAdd("m", X, _
StartDate), "\Qq-yyyy")
End If
Col = Col + 1
Next
End If
End If
End Sub

--
Hugo

--

Dave Peterson

Locking/unlocking cells based on column headers
 

That is helpful, but I've gotten to this point before on my own. I should
have been clearer in my description. The solution you have provided only
locks cells in one row (row=1), but the solution I am looking for is one
which locks/unlocks the cells in all rows in a range beneath the column
headers. So say the range I am looking to modify is A5:D10, how could I get
*all* the cells in that range to lock/unlock based on their corresponding
column headers in row=1?

Thanks for your help!
--
Hugo


"Dave Peterson" wrote:

For X = 0 To Duration - 1
Cells(1, Col).NumberFormat = "mmm-yyyy"
Cells(1, Col).Value = DateAdd("m", X, StartDate)
cells(1, col).locked = false '<-- added
If Month(DateAdd("m", X, StartDate)) Mod 3 = 0 Then
Col = Col + 1
Cells(1, Col).NumberFormat = "@"
Cells(1, Col).Value = Format(DateAdd("m", X, _
StartDate), "\Qq-yyyy")
cells(1, col).locked = true '<-- added
End If
Col = Col + 1
Next X



Hugo wrote:

I am using the following code for a subroutine, which outputs a header of
dates (in the format:
mmm-yy,mmm-yy,mmm-yy,qq-yy,mmm-yy,mmm-yy,mmm-yy,qq-yy,...) in a row based on
the starting date and the number of months (duration). I was wondering if
there is a way to get this same subroutine to make it so that all the cells
in the columns that correspond to the date format (qq-yy) are locked and all
the cells in the columns that correspond to the date format (mmm-yy) are
unlocked. Any thoughts

Sub DatesWithQuarters()
Dim X As Long, Col As Long
Dim StartDate As Variant, Duration As Variant
Col = 5 ' This is the starting column for the list
StartDate = InputBox("Tell me the starting month and 4-digit year")
Duration = InputBox("How many months should be listed?")
If IsDate(StartDate) And Len(Duration) 0 And _
Not Duration Like "*[!0-9]*" Then
If Duration 0 Then
StartDate = CDate(StartDate)
For X = 0 To Duration - 1
Cells(1, Col).NumberFormat = "mmm-yyyy"
Cells(1, Col).Value = DateAdd("m", X, StartDate)
If Month(DateAdd("m", X, StartDate)) Mod 3 = 0 Then
Col = Col + 1
Cells(1, Col).NumberFormat = "@"
Cells(1, Col).Value = Format(DateAdd("m", X, _
StartDate), "\Qq-yyyy")
End If
Col = Col + 1
Next
End If
End If
End Sub

--
Hugo

--

Dave Peterson

Locking/unlocking cells based on column headers
 

I'm not sure how the rows below row 1 start with row 5, but if you can find the
starting row and ending row, then maybe something like:

Dim StartRow as long
Dim EndRow as long
startrow = 5
endrow = 10

For X = 0 To Duration - 1
....
range(cells(startrow, col),cells(endrow,col)).locked = false
if ....
....
range(cells(startrow, col),cells(endrow,col)).locked = true
...

If you can pick out a column that determines the last used row, you could use:

endrow = cells(rows.count,"A").end(xlup).row

(I used column A)


Hugo wrote:

That is helpful, but I've gotten to this point before on my own. I should
have been clearer in my description. The solution you have provided only
locks cells in one row (row=1), but the solution I am looking for is one
which locks/unlocks the cells in all rows in a range beneath the column
headers. So say the range I am looking to modify is A5:D10, how could I get
*all* the cells in that range to lock/unlock based on their corresponding
column headers in row=1?

Thanks for your help!
--
Hugo

"Dave Peterson" wrote:

For X = 0 To Duration - 1
Cells(1, Col).NumberFormat = "mmm-yyyy"
Cells(1, Col).Value = DateAdd("m", X, StartDate)
cells(1, col).locked = false '<-- added
If Month(DateAdd("m", X, StartDate)) Mod 3 = 0 Then
Col = Col + 1
Cells(1, Col).NumberFormat = "@"
Cells(1, Col).Value = Format(DateAdd("m", X, _
StartDate), "\Qq-yyyy")
cells(1, col).locked = true '<-- added
End If
Col = Col + 1
Next X



Hugo wrote:

I am using the following code for a subroutine, which outputs a header of
dates (in the format:
mmm-yy,mmm-yy,mmm-yy,qq-yy,mmm-yy,mmm-yy,mmm-yy,qq-yy,...) in a row based on
the starting date and the number of months (duration). I was wondering if
there is a way to get this same subroutine to make it so that all the cells
in the columns that correspond to the date format (qq-yy) are locked and all
the cells in the columns that correspond to the date format (mmm-yy) are
unlocked. Any thoughts

Sub DatesWithQuarters()
Dim X As Long, Col As Long
Dim StartDate As Variant, Duration As Variant
Col = 5 ' This is the starting column for the list
StartDate = InputBox("Tell me the starting month and 4-digit year")
Duration = InputBox("How many months should be listed?")
If IsDate(StartDate) And Len(Duration) 0 And _
Not Duration Like "*[!0-9]*" Then
If Duration 0 Then
StartDate = CDate(StartDate)
For X = 0 To Duration - 1
Cells(1, Col).NumberFormat = "mmm-yyyy"
Cells(1, Col).Value = DateAdd("m", X, StartDate)
If Month(DateAdd("m", X, StartDate)) Mod 3 = 0 Then
Col = Col + 1
Cells(1, Col).NumberFormat = "@"
Cells(1, Col).Value = Format(DateAdd("m", X, _
StartDate), "\Qq-yyyy")
End If
Col = Col + 1
Next
End If
End If
End Sub

--
Hugo

--

Dave Peterson


--

Dave Peterson

Locking/unlocking cells based on column headers
 

Dave, thank you so much! This is a huge help. I've tried that before with
named ranges, but for some reason it has never worked. Any thoughts on how I
could do this with a named range?

The reason I am so concerned about the range having locked/unlocked cells is
because I am creating an excel tool for others to input data into, and I want
to limit the amount of human error that can be caused from any modification
of cells contents. The range I gave you was just an example, the range I am
working with is a little more complex because it has some rows that sum up
other rows, etc. so that is why I am so interested in being able to do this
for a named range. However, your code example has tought me a lot about how
to embed the cells() command within the range() command.

Thanks again!
--
Hugo


"Dave Peterson" wrote:

I'm not sure how the rows below row 1 start with row 5, but if you can find the
starting row and ending row, then maybe something like:

Dim StartRow as long
Dim EndRow as long
startrow = 5
endrow = 10

For X = 0 To Duration - 1
....
range(cells(startrow, col),cells(endrow,col)).locked = false
if ....
....
range(cells(startrow, col),cells(endrow,col)).locked = true
...

If you can pick out a column that determines the last used row, you could use:

endrow = cells(rows.count,"A").end(xlup).row

(I used column A)


Hugo wrote:

That is helpful, but I've gotten to this point before on my own. I should
have been clearer in my description. The solution you have provided only
locks cells in one row (row=1), but the solution I am looking for is one
which locks/unlocks the cells in all rows in a range beneath the column
headers. So say the range I am looking to modify is A5:D10, how could I get
*all* the cells in that range to lock/unlock based on their corresponding
column headers in row=1?

Thanks for your help!
--
Hugo

"Dave Peterson" wrote:

For X = 0 To Duration - 1
Cells(1, Col).NumberFormat = "mmm-yyyy"
Cells(1, Col).Value = DateAdd("m", X, StartDate)
cells(1, col).locked = false '<-- added
If Month(DateAdd("m", X, StartDate)) Mod 3 = 0 Then
Col = Col + 1
Cells(1, Col).NumberFormat = "@"
Cells(1, Col).Value = Format(DateAdd("m", X, _
StartDate), "\Qq-yyyy")
cells(1, col).locked = true '<-- added
End If
Col = Col + 1
Next X



Hugo wrote:

I am using the following code for a subroutine, which outputs a header of
dates (in the format:
mmm-yy,mmm-yy,mmm-yy,qq-yy,mmm-yy,mmm-yy,mmm-yy,qq-yy,...) in a row based on
the starting date and the number of months (duration). I was wondering if
there is a way to get this same subroutine to make it so that all the cells
in the columns that correspond to the date format (qq-yy) are locked and all
the cells in the columns that correspond to the date format (mmm-yy) are
unlocked. Any thoughts

Sub DatesWithQuarters()
Dim X As Long, Col As Long
Dim StartDate As Variant, Duration As Variant
Col = 5 ' This is the starting column for the list
StartDate = InputBox("Tell me the starting month and 4-digit year")
Duration = InputBox("How many months should be listed?")
If IsDate(StartDate) And Len(Duration) 0 And _
Not Duration Like "*[!0-9]*" Then
If Duration 0 Then
StartDate = CDate(StartDate)
For X = 0 To Duration - 1
Cells(1, Col).NumberFormat = "mmm-yyyy"
Cells(1, Col).Value = DateAdd("m", X, StartDate)
If Month(DateAdd("m", X, StartDate)) Mod 3 = 0 Then
Col = Col + 1
Cells(1, Col).NumberFormat = "@"
Cells(1, Col).Value = Format(DateAdd("m", X, _
StartDate), "\Qq-yyyy")
End If
Col = Col + 1
Next
End If
End If
End Sub

--
Hugo

--

Dave Peterson


--

Dave Peterson

Locking/unlocking cells based on column headers
 

I'm not sure how you're using the named range. If the row of headers that's the
named range, you could use:

dim myCell as range
dim myRng as range
dim wks as worksheet

set wks = activesheet

with wks
set myrng = .range("NamedRangeHere")
for each mycell in myrng.cells
'some test here
.range(.cells(startrow, mycell.column), .cells(endrow,mycell.column)) _
.locked = true 'or false
...

I like qualifying my .cells() and .range()'s. Those dots in front of those
properties and methods mean that they apply to the object in the previous With
statement (in this case wks, which is the active sheet).

Hugo wrote:

Dave, thank you so much! This is a huge help. I've tried that before with
named ranges, but for some reason it has never worked. Any thoughts on how I
could do this with a named range?

The reason I am so concerned about the range having locked/unlocked cells is
because I am creating an excel tool for others to input data into, and I want
to limit the amount of human error that can be caused from any modification
of cells contents. The range I gave you was just an example, the range I am
working with is a little more complex because it has some rows that sum up
other rows, etc. so that is why I am so interested in being able to do this
for a named range. However, your code example has tought me a lot about how
to embed the cells() command within the range() command.

Thanks again!
--
Hugo

"Dave Peterson" wrote:

I'm not sure how the rows below row 1 start with row 5, but if you can find the
starting row and ending row, then maybe something like:

Dim StartRow as long
Dim EndRow as long
startrow = 5
endrow = 10

For X = 0 To Duration - 1
....
range(cells(startrow, col),cells(endrow,col)).locked = false
if ....
....
range(cells(startrow, col),cells(endrow,col)).locked = true
...

If you can pick out a column that determines the last used row, you could use:

endrow = cells(rows.count,"A").end(xlup).row

(I used column A)


Hugo wrote:

That is helpful, but I've gotten to this point before on my own. I should
have been clearer in my description. The solution you have provided only
locks cells in one row (row=1), but the solution I am looking for is one
which locks/unlocks the cells in all rows in a range beneath the column
headers. So say the range I am looking to modify is A5:D10, how could I get
*all* the cells in that range to lock/unlock based on their corresponding
column headers in row=1?

Thanks for your help!
--
Hugo

"Dave Peterson" wrote:

For X = 0 To Duration - 1
Cells(1, Col).NumberFormat = "mmm-yyyy"
Cells(1, Col).Value = DateAdd("m", X, StartDate)
cells(1, col).locked = false '<-- added
If Month(DateAdd("m", X, StartDate)) Mod 3 = 0 Then
Col = Col + 1
Cells(1, Col).NumberFormat = "@"
Cells(1, Col).Value = Format(DateAdd("m", X, _
StartDate), "\Qq-yyyy")
cells(1, col).locked = true '<-- added
End If
Col = Col + 1
Next X



Hugo wrote:

I am using the following code for a subroutine, which outputs a header of
dates (in the format:
mmm-yy,mmm-yy,mmm-yy,qq-yy,mmm-yy,mmm-yy,mmm-yy,qq-yy,...) in a row based on
the starting date and the number of months (duration). I was wondering if
there is a way to get this same subroutine to make it so that all the cells
in the columns that correspond to the date format (qq-yy) are locked and all
the cells in the columns that correspond to the date format (mmm-yy) are
unlocked. Any thoughts

Sub DatesWithQuarters()
Dim X As Long, Col As Long
Dim StartDate As Variant, Duration As Variant
Col = 5 ' This is the starting column for the list
StartDate = InputBox("Tell me the starting month and 4-digit year")
Duration = InputBox("How many months should be listed?")
If IsDate(StartDate) And Len(Duration) 0 And _
Not Duration Like "*[!0-9]*" Then
If Duration 0 Then
StartDate = CDate(StartDate)
For X = 0 To Duration - 1
Cells(1, Col).NumberFormat = "mmm-yyyy"
Cells(1, Col).Value = DateAdd("m", X, StartDate)
If Month(DateAdd("m", X, StartDate)) Mod 3 = 0 Then
Col = Col + 1
Cells(1, Col).NumberFormat = "@"
Cells(1, Col).Value = Format(DateAdd("m", X, _
StartDate), "\Qq-yyyy")
End If
Col = Col + 1
Next
End If
End If
End Sub

--
Hugo

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson