Dave Peterson
7/29/2009 8:26:00 PM
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