Help with VB code

[Den]

I’m not an expert in VB codes so please be patient

I have a Worksheet of two stages to enter measures and calculate a result.
Depending in the result obtained on Stage 1 a second Stage must be performed
so I created a VB code to open a UserForm (that contains two commandButtons)
that allows me to enter the measures of the Stage 2 when the results of the
Stage 1 does not meet the acceptance range. I have a AND function in cell
Q27 that if both acceptance range are meets in the Stage1 then Q27 = True, if
no Q27= False. When Q27=False the UserForm is showed and one of the
commandButtons allows me to continue enter data on the Stage2 but each time
that I enter a number on the Stage 2 cells the Userform is open again. I
know that is caused by a loop of the If Then statement but I have no idea how
to “break†the loop and open the UserForm only one time.



Private Sub Worksheet_Calculate()
Set Target = Range("Q27")
If Target.Value = "True" Then
Label1.Visible = True
Else
Label1.Visible = False
Label2.Visible = True
End If
UserForm5.Show
Worksheets("Sheet1").Unprotect Password:="password"
Set Target = Range("S15")
If Target.Value = "2" Then
Range("J28").NumberFormat = "0.0%"
Worksheets("Sheet1").Protect Password:="password"
Else
Worksheets("Sheet1").Unprotect Password:="password"
Range("J28").NumberFormat = "0%"
Worksheets("Sheet1").Protect Password:="password"
End If
End Sub

I will, appreciate any help from you

Thanks……

 

[Jim Cone]

There are lots of other things I would change, but...
Changes:
1. Moved the first "End If" down to the bottom of the sub
2. Added an error handler.
3. Turned off "Events" which was causing the code to recycle.
4. NOTE: Events must be turned back on before exiting the sub.
(that is what the error handler does)

Jim Cone
Portland, Oregon USA
(Special Sort... http://www.contextures.com/excel-sort-addin.html )
'--

Private Sub Worksheet_Calculate()
On Error GoTo Err_Num
Dim Target
Dim Label1
Dim Label2

Set Target = Range("Q27")
If Target.Value = "True" Then
Label1.Visible = True
Else
Application.EnableEvents = False
Label1.Visible = False
Label2.Visible = True
UserForm5.Show
Worksheets("Sheet1").Unprotect Password:="password"
Set Target = Range("S15")
If Target.Value = "2" Then
Range("J28").NumberFormat = "0.0%"
Worksheets("Sheet1").Protect Password:="password"
Else
Worksheets("Sheet1").Unprotect Password:="password"
Range("J28").NumberFormat = "0%"
Worksheets("Sheet1").Protect Password:="password"
End If
End If

Err_Num:
Application.EnableEvents = True
End Sub
'-----------


"Den" <[email protected]>
wrote in message I’m not an expert in VB codes so please be patient
I have a Worksheet of two stages to enter measures and calculate a result.
Depending in the result obtained on Stage 1 a second Stage must be performed
so I created a VB code to open a UserForm (that contains two commandButtons)
that allows me to enter the measures of the Stage 2 when the results of the
Stage 1 does not meet the acceptance range. I have a AND function in cell
Q27 that if both acceptance range are meets in the Stage1 then Q27 = True, if
no Q27= False. When Q27=False the UserForm is showed and one of the
commandButtons allows me to continue enter data on the Stage2 but each time
that I enter a number on the Stage 2 cells the Userform is open again. I
know that is caused by a loop of the If Then statement but I have no idea how
to “break†the loop and open the UserForm only one time.

Private Sub Worksheet_Calculate()
Set Target = Range("Q27")
If Target.Value = "True" Then
Label1.Visible = True
Else
Label1.Visible = False
Label2.Visible = True
End If
UserForm5.Show
Worksheets("Sheet1").Unprotect Password:="password"
Set Target = Range("S15")
If Target.Value = "2" Then
Range("J28").NumberFormat = "0.0%"
Worksheets("Sheet1").Protect Password:="password"
Else
Worksheets("Sheet1").Unprotect Password:="password"
Range("J28").NumberFormat = "0%"
Worksheets("Sheet1").Protect Password:="password"
End If
End Sub

I will, appreciate any help from you
Thanks……

 

[JLGWhiz]

You would need to use an If... Then statement with criterian that would only
apply for the first call of the user form. Since I don't know what the form
does, I can't offer any specific code, but the criteria should be something
that will only occur the first time the calculate event is triggered.
Otherwise, you are going to see the form every time.
Example:

If Range("Q27").Value = "True" Then
UserForm5.Show
End If

Then if Range("Q27") was False every time after the first calculation cycle,
the form would not show again until Q27 changed back to true. This is only
an example. You will need to find the appropriate criteria.