If function or Change Event? (2002)

Topic

New Reply

I am trying to work out the following

Reply | Quote

Replies

Hi Jim,

I think you need the Worksheet_Change event for this:

– Activate the Visual Basic Editor (Alt+F11)
– Activate the Project Explorer (Ctrl+R)
– If necessary, expand your workbook until you see the worksheet in the tree view.
– Double click the worksheet.
– Type or copy the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range(“D20”)) Is Nothing Then
If LCase(Range(“D20”)) = “yes” Then
Range(“E20”) = “Yes”
End If
End If
End Sub

Comparisons in VBA are case-sensitive by default, so the code tests on LCase(Range(“D20”)) to allow “yes” and “YES” and “Yes”.

Reply | Quote

Thanks Hans. Your code does the job well and I learned a new function, Intersect. I took your code and expanded it to a range of cells (D20:D29).

I used a variable declared as object. Object can be any object so I believe it is late binding. There is no type Cell. Is there a way to change the variable to early binding? Not that this example needs it but just to learn more.

Secondly, my code used For Each

Reply | Quote

You are correct that there is no Cell object; use the Range object instead. You can use

Dim objCell As Range

With this solution, I don’t think there is an alternative to looping through the cells, but if there is one, I’m sure one of the other Loungers will point it out.

Reply | Quote

You are correct that there is no Cell object; use the Range object instead. You can use

Dim objCell As Range

With this solution, I don’t think there is an alternative to looping through the cells, but if there is one, I’m sure one of the other Loungers will point it out.

Reply | Quote

I just want to “back up” Hans that there is no better way to loop thru all the cells in the range. The only improvement could be if the range could contain something other than text. In that case you could make the “loop range” smaller with the specialcells method.

Other approaches would be to use the FIND method, but with this small of range I would think the extra coding might not be worth it and this looping (at least to me) seems more intuitive.

Steve

Reply | Quote

I just want to “back up” Hans that there is no better way to loop thru all the cells in the range. The only improvement could be if the range could contain something other than text. In that case you could make the “loop range” smaller with the specialcells method.

Other approaches would be to use the FIND method, but with this small of range I would think the extra coding might not be worth it and this looping (at least to me) seems more intuitive.

Steve

Reply | Quote

Thanks Hans. Your code does the job well and I learned a new function, Intersect. I took your code and expanded it to a range of cells (D20:D29).

I used a variable declared as object. Object can be any object so I believe it is late binding. There is no type Cell. Is there a way to change the variable to early binding? Not that this example needs it but just to learn more.

Secondly, my code used For Each

Reply | Quote

Hi Jim,

I think you need the Worksheet_Change event for this:

– Activate the Visual Basic Editor (Alt+F11)
– Activate the Project Explorer (Ctrl+R)
– If necessary, expand your workbook until you see the worksheet in the tree view.
– Double click the worksheet.
– Type or copy the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range(“D20”)) Is Nothing Then
If LCase(Range(“D20”)) = “yes” Then
Range(“E20”) = “Yes”
End If
End If
End Sub

Comparisons in VBA are case-sensitive by default, so the code tests on LCase(Range(“D20”)) to allow “yes” and “YES” and “Yes”.

Reply | Quote

You might use a deliberate circular reference.
Go to tools, options, calculation and check the iteration box.

Change the formula to (assumed it is in cell A!):

=If(D20=”Yes”,”Yes”,A1)

One culprit though: it only works if iteration is set. Since this option is a systemwide setting, you need to explicitly set it in the workbook_Open event in the thisworkbook module (and restore the previous setting in the Before_Close routine). Otherwise the user might be presented with the circ error message.

Reply | Quote

Hi Jan,

Thanks for the help. I tried out your code also and I did not need to change any options. I stored the formula in E20 but used E40 instead of A1. E40 is where I store the value “No”.

Both suggestions work. Thank you!

Reply | Quote

Hi Jan,

Thanks for the help. I tried out your code also and I did not need to change any options. I stored the formula in E20 but used E40 instead of A1. E40 is where I store the value “No”.

Both suggestions work. Thank you!

Reply | Quote

You might use a deliberate circular reference.
Go to tools, options, calculation and check the iteration box.

Change the formula to (assumed it is in cell A!):

=If(D20=”Yes”,”Yes”,A1)

One culprit though: it only works if iteration is set. Since this option is a systemwide setting, you need to explicitly set it in the workbook_Open event in the thisworkbook module (and restore the previous setting in the Before_Close routine). Otherwise the user might be presented with the circ error message.

Reply | Quote