worksheet_Change

[Leslieac]

Hi,

I have written the following to open up a different user form when certain
cells are changed (The User answers "Y" to the question). This part is
working. My problem is when I reset the worksheet to the default values of
"N", the worksheet views this as a change and is popping up all the user
forms. I've been trying to add the first section (which isn't working), that
if any of the cells in the range b18:p28 are changed to "N", then do nothing.

I would appreciate any help... Leslie

Private Sub Worksheet_Change(ByVal Target As Range)


If Range("b14:p26").Cells = "N" Then Exit Sub



If Not Intersect(Target, Me.Range("g18:g26")) Is Nothing Then
With Target
frmBeltBuff.Show
End With
End If

If Not Intersect(Target, Me.Range("h18:h26")) Is Nothing Then
With Target
frmBevel.Show
End With
End If



End Sub

[Bob Phillips]

Don't get it.

You fire up a form if a value is entered in any og G18:G26, or any of
H18:H26. but what has B18:P28 got to do with it? Do you not want to fire the
form if the selected cell is N. If so then

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Me.Range("g18:g26")) Is Nothing Then
If Target.Value <> "N" Then
With Target
frmBeltBuff.Show
End With
End If

ElseIf Not Intersect(Target, Me.Range("h18:h26")) Is Nothing Then
If Target.Value <> "N" Then
With Target
frmBevel.Show
End With
End If

End If

End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Leslieac" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I have written the following to open up a different user form when certain
> cells are changed (The User answers "Y" to the question). This part is
> working. My problem is when I reset the worksheet to the default values
of
> "N", the worksheet views this as a change and is popping up all the user
> forms. I've been trying to add the first section (which isn't working),
that
> if any of the cells in the range b18:p28 are changed to "N", then do
nothing.
>
> I would appreciate any help... Leslie
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
>
> If Range("b14:p26").Cells = "N" Then Exit Sub
>
>
>
> If Not Intersect(Target, Me.Range("g18:g26")) Is Nothing Then
> With Target
> frmBeltBuff.Show
> End With
> End If
>
> If Not Intersect(Target, Me.Range("h18:h26")) Is Nothing Then
> With Target
> frmBevel.Show
> End With
> End If
>
>
>
> End Sub
>

[Leslieac]

Thanks so much. That seems to be working. The larger range of B18:P26 is
because I have that many more User Forms to create in that range. I was just
trying to combine 2 to see if it would work.

"Bob Phillips" wrote:

> Don't get it.
>
> You fire up a form if a value is entered in any og G18:G26, or any of
> H18:H26. but what has B18:P28 got to do with it? Do you not want to fire the
> form if the selected cell is N. If so then
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> If Not Intersect(Target, Me.Range("g18:g26")) Is Nothing Then
> If Target.Value <> "N" Then
> With Target
> frmBeltBuff.Show
> End With
> End If
>
> ElseIf Not Intersect(Target, Me.Range("h18:h26")) Is Nothing Then
> If Target.Value <> "N" Then
> With Target
> frmBevel.Show
> End With
> End If
>
> End If
>
> End Sub
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Leslieac" <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> >
> > I have written the following to open up a different user form when certain
> > cells are changed (The User answers "Y" to the question). This part is
> > working. My problem is when I reset the worksheet to the default values
> of
> > "N", the worksheet views this as a change and is popping up all the user
> > forms. I've been trying to add the first section (which isn't working),
> that
> > if any of the cells in the range b18:p28 are changed to "N", then do
> nothing.
> >
> > I would appreciate any help... Leslie
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> >
> >
> > If Range("b14:p26").Cells = "N" Then Exit Sub
> >
> >
> >
> > If Not Intersect(Target, Me.Range("g18:g26")) Is Nothing Then
> > With Target
> > frmBeltBuff.Show
> > End With
> > End If
> >
> > If Not Intersect(Target, Me.Range("h18:h26")) Is Nothing Then
> > With Target
> > frmBevel.Show
> > End With
> > End If
> >
> >
> >
> > End Sub
> >
>
>
>