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


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

Yes / No - Goto VBA
 

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

Yes / No - Goto VBA
 

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

Yes / No - Goto VBA
 

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" wrote in
message ...

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

Yes / No - Goto VBA
 

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

Cheers,

Samuel

--
Samuel
-----------------------------------------------------------------------
SamuelT's Profile: http://www.excelforum.com/member.php...fo&userid=2750
View this thread: http://www.excelforum.com/showthread.php?threadid=54505