Help with VB code
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€¦€¦ |
Help with VB code
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€¦€¦ |
Help with VB code
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. "Den" 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.. |
All times are GMT +1. The time now is 06:46 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com