Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Improve code that checks a range of names - currently using Activate
I have a spreadsheet with some hidden helper columns. One of these columns
is titled "Delete Row Allowed" and contains TRUE or a blank. True means that the yes, the row can be deleted. In the spreadsheet, I've defined a name, "boolDeleteItemAllowed" which refers to "=MySheet!$G1". Thus, the name always tells whether the currently selected row can be deleted. In my code I've got the following function, which checks whether row deletion is allowed. It checks all the rows in the current selection. It activates one cell in each row to do this. I haven't used Activate in code for some time, but I'm not sure how to get the relative reference in each row without it. Function DeleteRowAllowed() As Boolean Dim cell As Range Dim rngActiveCell As Range DeleteRowAllowed = True With Workbooks("MyBook.xls").ActiveSheet 'need to set this back at end Set rngActiveCell = ActiveCell 'check one cell in each row For Each cell In Selection.Columns(1).Cells cell.Activate If CBool(.Names("boolDeleteRowAllowed").RefersToRange .Value) = False Then DeleteRowAllowed = False Exit Function End If Next cell 'set back to original ActiveCell rngActiveCell.Activate End With End Function I don't think it's a big deal in terms of efficiency. As soon as it hits a blank row it exits and I'll add ScreenUpdating = False. But I am curious if there's a way to do it without Activate and still keeping the defined names in the worksheet. Thanks in advance, Doug |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Improve code that checks a range of names - currently using Activate
Hi Doug,
I am not certain that I have understood your requirement, but, perhaps, try: '========= Function DeleteRowAllowed() As Boolean Dim Rng As Range Set Rng = Selection.EntireRow.Columns("G:G") DeleteRowAllowed3= _ Application.CountIf(Rng, "False") 0 End Function '<<========= --- Regards. Norman "Doug Glancy" wrote in message ... I have a spreadsheet with some hidden helper columns. One of these columns is titled "Delete Row Allowed" and contains TRUE or a blank. True means that the yes, the row can be deleted. In the spreadsheet, I've defined a name, "boolDeleteItemAllowed" which refers to "=MySheet!$G1". Thus, the name always tells whether the currently selected row can be deleted. In my code I've got the following function, which checks whether row deletion is allowed. It checks all the rows in the current selection. It activates one cell in each row to do this. I haven't used Activate in code for some time, but I'm not sure how to get the relative reference in each row without it. Function DeleteRowAllowed() As Boolean Dim cell As Range Dim rngActiveCell As Range DeleteRowAllowed = True With Workbooks("MyBook.xls").ActiveSheet 'need to set this back at end Set rngActiveCell = ActiveCell 'check one cell in each row For Each cell In Selection.Columns(1).Cells cell.Activate If CBool(.Names("boolDeleteRowAllowed").RefersToRange .Value) = False Then DeleteRowAllowed = False Exit Function End If Next cell 'set back to original ActiveCell rngActiveCell.Activate End With End Function I don't think it's a big deal in terms of efficiency. As soon as it hits a blank row it exits and I'll add ScreenUpdating = False. But I am curious if there's a way to do it without Activate and still keeping the defined names in the worksheet. Thanks in advance, Doug |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Improve code that checks a range of names - currently using Activate
Norman,
Thanks, but that's not it. In each row, the cell value in column G could be True or not. Some rows, basically detail rows in a bid, can be deleted, others can not. If multiple rows are selected and any of the values is not True then the function must return False. I may be making it more complicated than necessary by referring to the name - I could do something like what you've done. I am interested though in keeping as much of the logic in the worksheet as possible, and so would like to use the name. Thanks, Doug "Norman Jones" wrote in message ... Hi Doug, I am not certain that I have understood your requirement, but, perhaps, try: '========= Function DeleteRowAllowed() As Boolean Dim Rng As Range Set Rng = Selection.EntireRow.Columns("G:G") DeleteRowAllowed3= _ Application.CountIf(Rng, "False") 0 End Function '<<========= --- Regards. Norman "Doug Glancy" wrote in message ... I have a spreadsheet with some hidden helper columns. One of these columns is titled "Delete Row Allowed" and contains TRUE or a blank. True means that the yes, the row can be deleted. In the spreadsheet, I've defined a name, "boolDeleteItemAllowed" which refers to "=MySheet!$G1". Thus, the name always tells whether the currently selected row can be deleted. In my code I've got the following function, which checks whether row deletion is allowed. It checks all the rows in the current selection. It activates one cell in each row to do this. I haven't used Activate in code for some time, but I'm not sure how to get the relative reference in each row without it. Function DeleteRowAllowed() As Boolean Dim cell As Range Dim rngActiveCell As Range DeleteRowAllowed = True With Workbooks("MyBook.xls").ActiveSheet 'need to set this back at end Set rngActiveCell = ActiveCell 'check one cell in each row For Each cell In Selection.Columns(1).Cells cell.Activate If CBool(.Names("boolDeleteRowAllowed").RefersToRange .Value) = False Then DeleteRowAllowed = False Exit Function End If Next cell 'set back to original ActiveCell rngActiveCell.Activate End With End Function I don't think it's a big deal in terms of efficiency. As soon as it hits a blank row it exits and I'll add ScreenUpdating = False. But I am curious if there's a way to do it without Activate and still keeping the defined names in the worksheet. Thanks in advance, Doug |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Improve code that checks a range of names - currently using Activate
Hi Doug,
Since you define the right to delete a row according to the value of the corresponding column G cell, why not use this in your function rather than a derived condition: '========= Function DeleteRowAllowed() As Boolean Dim Rng As Range Dim rCell As Range Dim aCell As Range Set Rng = Selection For Each rCell In Rng.Columns(1).Cells Set aCell = Application.Intersect(rCell.EntireRow, _ Rng.Parent.Columns("G:G")) If aCell.Value = "False" Then DeleteRowAllowed = False Exit Function End If Next rCell End Function '<<========= --- Regards. Norman "Doug Glancy" wrote in message ... Norman, Thanks, but that's not it. In each row, the cell value in column G could be True or not. Some rows, basically detail rows in a bid, can be deleted, others can not. If multiple rows are selected and any of the values is not True then the function must return False. I may be making it more complicated than necessary by referring to the name - I could do something like what you've done. I am interested though in keeping as much of the logic in the worksheet as possible, and so would like to use the name. Thanks, Doug "Norman Jones" wrote in message ... Hi Doug, I am not certain that I have understood your requirement, but, perhaps, try: '========= Function DeleteRowAllowed() As Boolean Dim Rng As Range Set Rng = Selection.EntireRow.Columns("G:G") DeleteRowAllowed3= _ Application.CountIf(Rng, "False") 0 End Function '<<========= --- Regards. Norman "Doug Glancy" wrote in message ... I have a spreadsheet with some hidden helper columns. One of these columns is titled "Delete Row Allowed" and contains TRUE or a blank. True means that the yes, the row can be deleted. In the spreadsheet, I've defined a name, "boolDeleteItemAllowed" which refers to "=MySheet!$G1". Thus, the name always tells whether the currently selected row can be deleted. In my code I've got the following function, which checks whether row deletion is allowed. It checks all the rows in the current selection. It activates one cell in each row to do this. I haven't used Activate in code for some time, but I'm not sure how to get the relative reference in each row without it. Function DeleteRowAllowed() As Boolean Dim cell As Range Dim rngActiveCell As Range DeleteRowAllowed = True With Workbooks("MyBook.xls").ActiveSheet 'need to set this back at end Set rngActiveCell = ActiveCell 'check one cell in each row For Each cell In Selection.Columns(1).Cells cell.Activate If CBool(.Names("boolDeleteRowAllowed").RefersToRange .Value) = False Then DeleteRowAllowed = False Exit Function End If Next cell 'set back to original ActiveCell rngActiveCell.Activate End With End Function I don't think it's a big deal in terms of efficiency. As soon as it hits a blank row it exits and I'll add ScreenUpdating = False. But I am curious if there's a way to do it without Activate and still keeping the defined names in the worksheet. Thanks in advance, Doug |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Improve code that checks a range of names - currently using Activate
Norman,
Upon reflection, I think that does make the most sense. I'll give the column a name to make the function more robust. Thanks, Doug "Norman Jones" wrote in message ... Hi Doug, Since you define the right to delete a row according to the value of the corresponding column G cell, why not use this in your function rather than a derived condition: '========= Function DeleteRowAllowed() As Boolean Dim Rng As Range Dim rCell As Range Dim aCell As Range Set Rng = Selection For Each rCell In Rng.Columns(1).Cells Set aCell = Application.Intersect(rCell.EntireRow, _ Rng.Parent.Columns("G:G")) If aCell.Value = "False" Then DeleteRowAllowed = False Exit Function End If Next rCell End Function '<<========= --- Regards. Norman "Doug Glancy" wrote in message ... Norman, Thanks, but that's not it. In each row, the cell value in column G could be True or not. Some rows, basically detail rows in a bid, can be deleted, others can not. If multiple rows are selected and any of the values is not True then the function must return False. I may be making it more complicated than necessary by referring to the name - I could do something like what you've done. I am interested though in keeping as much of the logic in the worksheet as possible, and so would like to use the name. Thanks, Doug "Norman Jones" wrote in message ... Hi Doug, I am not certain that I have understood your requirement, but, perhaps, try: '========= Function DeleteRowAllowed() As Boolean Dim Rng As Range Set Rng = Selection.EntireRow.Columns("G:G") DeleteRowAllowed3= _ Application.CountIf(Rng, "False") 0 End Function '<<========= --- Regards. Norman "Doug Glancy" wrote in message ... I have a spreadsheet with some hidden helper columns. One of these columns is titled "Delete Row Allowed" and contains TRUE or a blank. True means that the yes, the row can be deleted. In the spreadsheet, I've defined a name, "boolDeleteItemAllowed" which refers to "=MySheet!$G1". Thus, the name always tells whether the currently selected row can be deleted. In my code I've got the following function, which checks whether row deletion is allowed. It checks all the rows in the current selection. It activates one cell in each row to do this. I haven't used Activate in code for some time, but I'm not sure how to get the relative reference in each row without it. Function DeleteRowAllowed() As Boolean Dim cell As Range Dim rngActiveCell As Range DeleteRowAllowed = True With Workbooks("MyBook.xls").ActiveSheet 'need to set this back at end Set rngActiveCell = ActiveCell 'check one cell in each row For Each cell In Selection.Columns(1).Cells cell.Activate If CBool(.Names("boolDeleteRowAllowed").RefersToRange .Value) = False Then DeleteRowAllowed = False Exit Function End If Next cell 'set back to original ActiveCell rngActiveCell.Activate End With End Function I don't think it's a big deal in terms of efficiency. As soon as it hits a blank row it exits and I'll add ScreenUpdating = False. But I am curious if there's a way to do it without Activate and still keeping the defined names in the worksheet. Thanks in advance, Doug |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code to do edit checks when user moves out of a cell | Excel Programming | |||
How to improve my code? | Excel Programming | |||
Improve 'Windows(workbook.xls).Activate" statement | Excel Programming | |||
Improve code | Excel Programming | |||
Help with code that checks if a sheet exists | Excel Programming |