Displaying a User Form
 

I have a shared file where people enter a number in column B8 through 228. In
column K8 through 228 are formulas that look at the numbers in column B and
deliver a "Yes" or a "No." I want Excel to show user-form1 whenever a formula
delivers a "No." When the user-form is displayed, the user will click the OK
button to close the user form and allow the next user to repeat the process.

I'm really spinning my wheels on this. Your help would be greatly appreciated!
Thanks in advance. - DN

Displaying a User Form
 

Dan,

If each cell (B8:B228) is entered individually the following code will show
UserFrom1 whenever a change in column B results in a No in column K.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target.Cells, Range("B8:B228")) Is Nothing Then
If Target.Offset(0, 9).Value = "No" Then UserForm1.Show
End If
End Sub

If the cells (B8:B228) may be entered in groups then a modification will
need to be made - perhaps a For Loop - to accomplish the same. Let me know
if you need help with that.

Mike


"Dan N" wrote:

I have a shared file where people enter a number in column B8 through 228. In
column K8 through 228 are formulas that look at the numbers in column B and
deliver a "Yes" or a "No." I want Excel to show user-form1 whenever a formula
delivers a "No." When the user-form is displayed, the user will click the OK
button to close the user form and allow the next user to repeat the process.

I'm really spinning my wheels on this. Your help would be greatly appreciated!
Thanks in advance. - DN

Displaying a User Form
 

Mike - Thank you for your response.
Your code did exactly what I need but because I already had some code on
that sheet that started with"Private Sub Worksheet_Change(ByVal Target As
Range)" it caused this error -- "Ambiguous name detected:Worksheet_Change."
Should I put your code in a module instead of on the sheet with the formulas?

I'm also having a problem getting my commandbutton2 to cancel the user-form.
- Dan

"crazybass2" wrote:

Dan,

If each cell (B8:B228) is entered individually the following code will show
UserFrom1 whenever a change in column B results in a No in column K.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target.Cells, Range("B8:B228")) Is Nothing Then
If Target.Offset(0, 9).Value = "No" Then UserForm1.Show
End If
End Sub

If the cells (B8:B228) may be entered in groups then a modification will
need to be made - perhaps a For Loop - to accomplish the same. Let me know
if you need help with that.

Mike


"Dan N" wrote:

I have a shared file where people enter a number in column B8 through 228. In
column K8 through 228 are formulas that look at the numbers in column B and
deliver a "Yes" or a "No." I want Excel to show user-form1 whenever a formula
delivers a "No." When the user-form is displayed, the user will click the OK
button to close the user form and allow the next user to repeat the process.

I'm really spinning my wheels on this. Your help would be greatly appreciated!
Thanks in advance. - DN

Displaying a User Form
 

Dan,

Since you already have a Worksheet_Change event just put the three lines of
code within that proceedure. Just make sure it is not nested within other
commands. To ensure that is the case insert the three lines directly below
the Private Sub Worksheet_Change line as shown below:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target.Cells, Range("B8:B228")) Is Nothing Then
If Target.Offset(0, 9).Value = "No" Then UserForm1.Show
End If
....
the other code
....
End Sub

That should do the trick.

Mike

"Dan N" wrote:

Mike - Thank you for your response.
Your code did exactly what I need but because I already had some code on
that sheet that started with"Private Sub Worksheet_Change(ByVal Target As
Range)" it caused this error -- "Ambiguous name detected:Worksheet_Change."
Should I put your code in a module instead of on the sheet with the formulas?

I'm also having a problem getting my commandbutton2 to cancel the user-form.
- Dan

"crazybass2" wrote:

Dan,

If each cell (B8:B228) is entered individually the following code will show
UserFrom1 whenever a change in column B results in a No in column K.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target.Cells, Range("B8:B228")) Is Nothing Then
If Target.Offset(0, 9).Value = "No" Then UserForm1.Show
End If
End Sub

If the cells (B8:B228) may be entered in groups then a modification will
need to be made - perhaps a For Loop - to accomplish the same. Let me know
if you need help with that.

Mike


"Dan N" wrote:

I have a shared file where people enter a number in column B8 through 228. In
column K8 through 228 are formulas that look at the numbers in column B and
deliver a "Yes" or a "No." I want Excel to show user-form1 whenever a formula
delivers a "No." When the user-form is displayed, the user will click the OK
button to close the user form and allow the next user to repeat the process.

I'm really spinning my wheels on this. Your help would be greatly appreciated!
Thanks in advance. - DN

Displaying a User Form
 

Without seeing the code for commandbutton2 I can't be of much help there.



"Dan N" wrote:

Mike - Thank you for your response.
Your code did exactly what I need but because I already had some code on
that sheet that started with"Private Sub Worksheet_Change(ByVal Target As
Range)" it caused this error -- "Ambiguous name detected:Worksheet_Change."
Should I put your code in a module instead of on the sheet with the formulas?

I'm also having a problem getting my commandbutton2 to cancel the user-form.
- Dan

"crazybass2" wrote:

Dan,

If each cell (B8:B228) is entered individually the following code will show
UserFrom1 whenever a change in column B results in a No in column K.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target.Cells, Range("B8:B228")) Is Nothing Then
If Target.Offset(0, 9).Value = "No" Then UserForm1.Show
End If
End Sub

If the cells (B8:B228) may be entered in groups then a modification will
need to be made - perhaps a For Loop - to accomplish the same. Let me know
if you need help with that.

Mike


"Dan N" wrote:

I have a shared file where people enter a number in column B8 through 228. In
column K8 through 228 are formulas that look at the numbers in column B and
deliver a "Yes" or a "No." I want Excel to show user-form1 whenever a formula
delivers a "No." When the user-form is displayed, the user will click the OK
button to close the user form and allow the next user to repeat the process.

I'm really spinning my wheels on this. Your help would be greatly appreciated!
Thanks in advance. - DN

Displaying a User Form
 

Mike,

That did it! Everything is working great. You not only solved my problem but
you also taught me something new. Thank you so much!!
- Dan

"crazybass2" wrote:

Without seeing the code for commandbutton2 I can't be of much help there.



"Dan N" wrote:

Mike - Thank you for your response.
Your code did exactly what I need but because I already had some code on
that sheet that started with"Private Sub Worksheet_Change(ByVal Target As
Range)" it caused this error -- "Ambiguous name detected:Worksheet_Change."
Should I put your code in a module instead of on the sheet with the formulas?

I'm also having a problem getting my commandbutton2 to cancel the user-form.
- Dan

"crazybass2" wrote:

Dan,

If each cell (B8:B228) is entered individually the following code will show
UserFrom1 whenever a change in column B results in a No in column K.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target.Cells, Range("B8:B228")) Is Nothing Then
If Target.Offset(0, 9).Value = "No" Then UserForm1.Show
End If
End Sub

If the cells (B8:B228) may be entered in groups then a modification will
need to be made - perhaps a For Loop - to accomplish the same. Let me know
if you need help with that.

Mike


"Dan N" wrote:

I have a shared file where people enter a number in column B8 through 228. In
column K8 through 228 are formulas that look at the numbers in column B and
deliver a "Yes" or a "No." I want Excel to show user-form1 whenever a formula
delivers a "No." When the user-form is displayed, the user will click the OK
button to close the user form and allow the next user to repeat the process.

I'm really spinning my wheels on this. Your help would be greatly appreciated!
Thanks in advance. - DN