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

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

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

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

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