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"
wrote in message ...
Im 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€¦€¦