Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Locking column headers | Excel Discussion (Misc queries) | |||
Locking/Unlocking based on another cell value | Excel Worksheet Functions | |||
Locking/Unlocking cells upon condition | Excel Discussion (Misc queries) | |||
Locking/unlocking cells | Excel Programming | |||
locking and unlocking a row of data based on whats entered in a cell | Excel Programming |