×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

if-then program hangs
2

if-then program hangs

if-then program hangs

(OP)
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

RE: if-then program hangs

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

RE: if-then program hangs

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.

RE: if-then program hangs

Code to do what was described above is as follows:

CODE

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.

RE: if-then program hangs

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.

RE: if-then program hangs

(OP)
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.

RE: if-then program hangs

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

CODE

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources