Locking/unlocking cells based on column headers

H

Hugo

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
 
D

Dave Peterson

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


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
Click to expand...
 
H

Hugo

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 said:
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


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
Click to expand...
Click to expand...
 
D

Dave Peterson

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)

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 said:
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


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
Click to expand...
Click to expand...
Click to expand...
 
H

Hugo

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 said:
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)

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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

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).
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 said:
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)

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

:

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Applying formatting to non-continuous named ranges 1
Help with mess of code 1
VBA in Excel 2007 date formatting 2
VBA in Excel 2007, NumberFormat 1
Date format. 1
Dynamic Offset from an Application.Goto cell = Date or Time 10
Paste TextBox value to worksheet with specific format 1
Add a new row in Named Table (Table1) and add data from txtboxes into last empty cells based on header named of the Table1 0

Top