Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Dim ans As Long
Select Case Sh.Name
Case "Programme"
If Not Intersect(Target, Range("G5:G350")) Is Nothing Then
Range("G10").Activate
ans = MsgBox "Now please fill in Component information", _
vbYesNo + vbInformation, "Next Step!"
If ans = vbYes Then Worksheets("Components").Activate
End If


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"SamuelT" wrote in
message ...

Hi all,

So I've got the following code that brings up a message box when
someone inputs something into cells G5:G350.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Select Case Sh.Name
Case "Programme"
If Not Intersect(Target, Range("G5:G350")) Is Nothing Then
Range("G10").Activate
MsgBox "Now please fill in Component information", vbInformation, "Next
Step!"
End If


End Select
End Sub

I've now been asked to extend this slightly further so that when the
message box comes up it gives a yes/no option (vbYesNo). This I can do
- what I haven't quite sussed is how, when the 'Yes' is pressed to take
the user to another worksheet entitled "Components", and to keep them in
the same worksheet when they press 'No'. Any tips on how this can be
achieved?

TIA,

SamuelT


--
SamuelT
------------------------------------------------------------------------
SamuelT's Profile:
http://www.excelforum.com/member.php...o&userid=27501
View this thread: http://www.excelforum.com/showthread...hreadid=545056