Yes / No - Goto VBA

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

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> SamuelT
> ------------------------------------------------------------------------
> SamuelT's Profile:
http://www.excelforum.com/member.php...o&userid=27501
> View this thread: http://www.excelforum.com/showthread...hreadid=545056
>

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

Cheers,

SamuelT