Pick which sheet to put a wordart stamp on

[pano]

Hi again, I wish to put a macro one button on menu sheet which would
allow me to select which day of the week sheet I have a wordart Rec Day
stamped on.
At the moment I have 6 buttons with macros attached and the user
selects which day. But I think it would be more polished if they just
pressed one button and selected which day to have it put on. Can this
be done??
The code I have attached to the button is as follows.

TueREC Macro
' Macro recorded 1/23/2007 by *
'
Application.ScreenUpdating = False
Sheets("Tuesday").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("menu").Select

End Sub

Once again a big thankyou to who ever answers....

 

[Gord Dibben]

pano

Option Compare Text
Sub pick_day()
' Macro recorded 1/23/2007 by *
Dim whichsht As String
Application.ScreenUpdating = False
whichsht = InputBox("type in day")
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("menu").Select

End Sub


Gord Dibben MS Excel MVP

 

[pano]

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

 

[Gord Dibben]

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

 

[pano]

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.

 

[Gord Dibben]

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

 

[pano]

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

 

[Gord Dibben]

Happy to assist although it took a while<g>

Gord