Happy to assist although it took a while<g

Gord

On 23 Jan 2007 17:30:24 -0800, "pano" wrote:

Thanks Gord, thats it by golly perfect thanks for your help....

On Jan 24, 12:17 pm, Gord Dibben <gorddibbATshawDOTca wrote:
pano

Leave it me to leave something out<g

Just above the line whoops: insert Exit Sub

Will look like.........

Sheets("starta").Select
Exit Sub
whoops:
MsgBox "You have cancelled or entered an invalid day"
End Sub

That will stop the message coming up unless there is an error.

Gord

On 23 Jan 2007 16:45:20 -0800, "pano" wrote:



Hmmm, I have tried your amended code unfortunately as I am not a guru I
cant work out whats going on, (CANCEL works and so does if you enter
numbers) but when you enter text lime Monday the message box still
comes up afterwards (You have cancelled or entered an invalid day) and
you have to click out of it, mind you it does the rest of the routine.

On Jan 24, 11:11 am, Gord Dibben <gorddibbATshawDOTca wrote:
pano

Edited version...........trap for Cancel, no entry or numeric entry.

Also added a line feed for your InputBox message.

Sub pick_day_Rec_Leave()
' Macro recorded 1/23/2007 by *
Dim whichsht As String
Application.ScreenUpdating = False

whichsht = InputBox("Type in Day to have text placed on DWS" & vbLf _
& "Monday - Monback Tuesday -Tuesback")

If whichsht = "" Or IsNumeric(whichsht) Then GoTo whoops

Sheets(whichsht).Select
ActiveSheet.Shapes.AddTextEffect(msoTextEffect1, "REC LEAVE", _
"Arial Black", _
44#, msoFalse, msoFalse, 324#, 274.5).Select
Selection.ShapeRange.IncrementLeft -177.75
Selection.ShapeRange.IncrementTop -190.5
Selection.ShapeRange.ScaleWidth 1.33, msoFalse, msoScaleFromTopLeft
Application.CommandBars("WordArt").Visible = False
Range("G20").Select
Sheets("starta").Select
whoops:
MsgBox "You have cancelled or entered an invalid day"

End Sub

Gord

On 23 Jan 2007 14:08:38 -0800, "pano" wrote:

Gordon, I wonder if you could help with the code you gave me when I
press cancel and dont want to enter a day, it goes to subscript out of
range and goes to debug it highlights

Sheets(whichsht).select
apart from that if you enter the day it works well thanks so much
regards
stephen

Sub pick_day_Rec_Leave()
' Macro recorded 1/23/2007 by *
Dim whichsht As String
Application.ScreenUpdating = False
whichsht = InputBox("Type in Day to have text placed on DWS Monday
- Monback Tuesday -Tuesback")
Sheets(whichsht).Select
ActiveSheet.Shapes.AddTextEffect(msoTextEffect1, "REC LEAVE", _
"Arial Black", _
44#, msoFalse, msoFalse, 324#, 274.5).Select
Selection.ShapeRange.IncrementLeft -177.75
Selection.ShapeRange.IncrementTop -190.5
Selection.ShapeRange.ScaleWidth 1.33, msoFalse, msoScaleFromTopLeft
Application.CommandBars("WordArt").Visible = False
Range("G20").Select
Sheets("starta").Select
End Sub- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -