Worksheet_Change event very slow
 

Hi

I'm working on a worksheet where I have a field for "Type of
Insurance" and "Amount of Insurance". Both these cells have Validation
lists in them. I want to achieve the following:

- If the user chooses "None" for the Type of Insurance, then whatever
is in the Amount of Insurance field should immediately change to a
blank.
- Also, the user should not be allowed to change whatever is in the
Amount of Insurance cell while the Type of Insurance is "None".

I have multiple variations of the above situation on the sheet where
instead of "Insurance", it deals with "Guarantees" and so on but the
idea is the same.

I've tried using the Worksheet_Change event which works fine but it
seems to be slowing down as I add more checks. Is there an efficient
and easy way to achieve what I want?

Here's (an extract of) my code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$D$46" Then
If Range("D46").Value = "None" Then
Range("D47").Value = 0
End If
End If

If Target.Address = "$D$47" Then
If Range("D46").Value = "None" Then
Range("D47").Value = ""
End If
End If

End Sub

Basically I have a whole bunch if If Then statements following each
other in the full code and I think that's what's slowing it down.

Suggestions?

Worksheet_Change event very slow
 

You may be having re-entry problems. At the very beginning of the code enter:

Application.EnableEvents = False

At the very end of the code enter:

Application.EnableEvents = True
--
Gary''s Student - gsnu2007h


"Fayyaadh Ebrahim" wrote:

Hi

I'm working on a worksheet where I have a field for "Type of
Insurance" and "Amount of Insurance". Both these cells have Validation
lists in them. I want to achieve the following:

- If the user chooses "None" for the Type of Insurance, then whatever
is in the Amount of Insurance field should immediately change to a
blank.
- Also, the user should not be allowed to change whatever is in the
Amount of Insurance cell while the Type of Insurance is "None".

I have multiple variations of the above situation on the sheet where
instead of "Insurance", it deals with "Guarantees" and so on but the
idea is the same.

I've tried using the Worksheet_Change event which works fine but it
seems to be slowing down as I add more checks. Is there an efficient
and easy way to achieve what I want?

Here's (an extract of) my code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$D$46" Then
If Range("D46").Value = "None" Then
Range("D47").Value = 0
End If
End If

If Target.Address = "$D$47" Then
If Range("D46").Value = "None" Then
Range("D47").Value = ""
End If
End If

End Sub

Basically I have a whole bunch if If Then statements following each
other in the full code and I think that's what's slowing it down.

Suggestions?

Worksheet_Change event very slow
 

On Apr 28, 3:09 pm, Gary''s Student
wrote:
You may be having re-entry problems. At the very beginning of the code enter:

Application.EnableEvents = False

At the very end of the code enter:

Application.EnableEvents = True
--
Gary''s Student - gsnu2007h

"Fayyaadh Ebrahim" wrote:
Hi

I'm working on a worksheet where I have a field for "Type of
Insurance" and "Amount of Insurance". Both these cells have Validation
lists in them. I want to achieve the following:

- If the user chooses "None" for the Type of Insurance, then whatever
is in the Amount of Insurance field should immediately change to a
blank.
- Also, the user should not be allowed to change whatever is in the
Amount of Insurance cell while the Type of Insurance is "None".

I have multiple variations of the above situation on the sheet where
instead of "Insurance", it deals with "Guarantees" and so on but the
idea is the same.

I've tried using the Worksheet_Change event which works fine but it
seems to be slowing down as I add more checks. Is there an efficient
and easy way to achieve what I want?

Here's (an extract of) my code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$D$46" Then
If Range("D46").Value = "None" Then
Range("D47").Value = 0
End If
End If

If Target.Address = "$D$47" Then
If Range("D46").Value = "None" Then
Range("D47").Value = ""
End If
End If

End Sub

Basically I have a whole bunch if If Then statements following each
other in the full code and I think that's what's slowing it down.

Suggestions?

It works great no, thank you very much!

Would you mind explaining what the problem was?

Re-entry?

Worksheet_Change event very slow
 

In addition, you can...
Change from an If / Then to a Select Case structure.
It appears every If / Then has to be evaluated in your code.
Select Case exits after it matches a condition; other conditions are skipped.
Place the most frequent conditions first...
'--
Select Case Target.Address
Case = "$D$46"
If Target.Value = "None" Then Range("D47").Value = 0
Case = "$D$47"
If Range("D46").Value = "None" Then Target.Value = ""
'More cases
End Select
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Fayyaadh Ebrahim"

wrote in message
Hi
I'm working on a worksheet where I have a field for "Type of
Insurance" and "Amount of Insurance". Both these cells have Validation
lists in them. I want to achieve the following:

- If the user chooses "None" for the Type of Insurance, then whatever
is in the Amount of Insurance field should immediately change to a
blank.
- Also, the user should not be allowed to change whatever is in the
Amount of Insurance cell while the Type of Insurance is "None".

I have multiple variations of the above situation on the sheet where
instead of "Insurance", it deals with "Guarantees" and so on but the
idea is the same.

I've tried using the Worksheet_Change event which works fine but it
seems to be slowing down as I add more checks. Is there an efficient
and easy way to achieve what I want?

Here's (an extract of) my code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$D$46" Then
If Range("D46").Value = "None" Then
Range("D47").Value = 0
End If
End If

If Target.Address = "$D$47" Then
If Range("D46").Value = "None" Then
Range("D47").Value = ""
End If
End If

End Sub

Basically I have a whole bunch if If Then statements following each
other in the full code and I think that's what's slowing it down.

Suggestions?

Worksheet_Change event very slow
 

If you change a cell, the macro is entered. If the macro itself changes a
cell, the macro is entered another time. To stop this from happening, we
turn off the event while the macro runs. We turn it back on after the macro
finishes.
--
Gary''s Student - gsnu2007h


"Fayyaadh Ebrahim" wrote:

On Apr 28, 3:09 pm, Gary''s Student
wrote:
You may be having re-entry problems. At the very beginning of the code enter:

Application.EnableEvents = False

At the very end of the code enter:

Application.EnableEvents = True
--
Gary''s Student - gsnu2007h

"Fayyaadh Ebrahim" wrote:
Hi

I'm working on a worksheet where I have a field for "Type of
Insurance" and "Amount of Insurance". Both these cells have Validation
lists in them. I want to achieve the following:

- If the user chooses "None" for the Type of Insurance, then whatever
is in the Amount of Insurance field should immediately change to a
blank.
- Also, the user should not be allowed to change whatever is in the
Amount of Insurance cell while the Type of Insurance is "None".

I have multiple variations of the above situation on the sheet where
instead of "Insurance", it deals with "Guarantees" and so on but the
idea is the same.

I've tried using the Worksheet_Change event which works fine but it
seems to be slowing down as I add more checks. Is there an efficient
and easy way to achieve what I want?

Here's (an extract of) my code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$D$46" Then
If Range("D46").Value = "None" Then
Range("D47").Value = 0
End If
End If

If Target.Address = "$D$47" Then
If Range("D46").Value = "None" Then
Range("D47").Value = ""
End If
End If

End Sub

Basically I have a whole bunch if If Then statements following each
other in the full code and I think that's what's slowing it down.

Suggestions?

It works great no, thank you very much!

Would you mind explaining what the problem was?

Re-entry?

Worksheet_Change event very slow
 

On Apr 28, 3:24 pm, "Jim Cone" wrote:
In addition, you can...
Change from an If / Then to a Select Case structure.
It appears every If / Then has to be evaluated in your code.
Select Case exits after it matches a condition; other conditions are skipped.
Place the most frequent conditions first...
'--
Select Case Target.Address
Case = "$D$46"
If Target.Value = "None" Then Range("D47").Value = 0
Case = "$D$47"
If Range("D46").Value = "None" Then Target.Value = ""
'More cases
End Select
--
Jim Cone
Portland, Oregon USAhttp://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

"Fayyaadh Ebrahim"

wrote in message
Hi
I'm working on a worksheet where I have a field for "Type of
Insurance" and "Amount of Insurance". Both these cells have Validation
lists in them. I want to achieve the following:

- If the user chooses "None" for the Type of Insurance, then whatever
is in the Amount of Insurance field should immediately change to a
blank.
- Also, the user should not be allowed to change whatever is in the
Amount of Insurance cell while the Type of Insurance is "None".

I have multiple variations of the above situation on the sheet where
instead of "Insurance", it deals with "Guarantees" and so on but the
idea is the same.

I've tried using the Worksheet_Change event which works fine but it
seems to be slowing down as I add more checks. Is there an efficient
and easy way to achieve what I want?

Here's (an extract of) my code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$D$46" Then
If Range("D46").Value = "None" Then
Range("D47").Value = 0
End If
End If

If Target.Address = "$D$47" Then
If Range("D46").Value = "None" Then
Range("D47").Value = ""
End If
End If

End Sub

Basically I have a whole bunch if If Then statements following each
other in the full code and I think that's what's slowing it down.

Suggestions?

But I don't want it to exit after it finds a true case, I want it to
check every single condition, change things if the condition is true
and then move on to checking the next condition.