Yes / No - Goto VBA

[SamuelT]

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

 

[Bob Phillips]

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]

Hi Bob,

Thanks for that. Unfortunately, it doesn't seem to be working. I've
copied and pasted the code in and the following (red) sections seem to
be problem areas.

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

End Select
End Sub

Any ideas?

Thanks,

SamuelT

 

[Bob Phillips]

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
End Sub

--
HTH

Bob Phillips

(replace xxxx in email address with googlemail if mailing direct)

 

[SamuelT]

Thanks Bob - left an 'End Select' out on the end, but apart from tha
perfect.

Cheers,

Samuel