if-then program hangs 2

[nrguades]

Hi..

I'm trying to write a simple vba for my excel spreadsheets. I'm wondering why this will cause my spreadsheet to hang when I input a value of 1 on B2.

I'm a structural engineer and novice on vba. I appreciate for your help.

Here's the code:

Sub Worksheet_Change(ByVal Target As Range)
If Range("a1").Value = 1 Then
Range("B1").Value = "-"
End If
If Range("a2").Value = 1 Then
Range("B2").Value = "-"
End If
End Sub


Thanks,
Noel

 

[CajunCenturion]

Check the value of Target so see which range change is triggering the event.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[electricpete]

I agree. When you change the b1 and b2 cells you are retriggering the loop and it keeps on going and changing and triggering. Only execute the if-clause if the changed-cell (target) is the one you are testing (a1).

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

Code to do what was described above is as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" And Range("a1").Value = 1 Then
        Range("B1").Value = "-"
    End If
    If Target.Address = "$A$2" And Range("a2").Value = 1 Then
        Range("B2").Value = "-"
    End If
End Sub

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

By the way, maybe you want to turn off the - if something other than 1 is in the a1 and a2 cells? You can put that in.

Also you can accomplish same thing without vba using if formula in the b1 and b2 cells which check the value of a1 and a2 and adjust their output .

Of course if you're just tryign to learn vba I understand why you might want to use vba instead for the educational value.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[nrguades]

Thank you Electricpete & CajunCenturion.

This solves my problem. Actually, I gonna tweak this a little bit and add this to my spreadsheet about lateral loads. Yeah, this conditions can be done without vba but what I'm really planning to do is to lock or unlock cells with a value of a cell equals to a certain value. I'm hoping this code will be a good way to start with additional "else" condition and adding something like Range("B2").Locked=true.

Thanks again guys. Hope I can still ask your help as problems come along the way.

 

[brengine]

In this type of situation, I often times I create a flag to ensure the loop only occurs once...

Dim bProcessing As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
If (bProcessing) Then Exit Sub

    bProcessing = True
    
    If Range("A1").Value = 1 Then
        Range("B1").Value = "-"
    End If
    
    If Range("A2").Value = 1 Then
        Range("B2").Value = "-"
    End If
    
    bProcessing = False
End Sub