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