ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Pick which sheet to put a wordart stamp on (https://www.excelbanter.com/excel-worksheet-functions/127285-pick-sheet-put-wordart-stamp.html)

pano
Pick which sheet to put a wordart stamp on
 
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
Pick which sheet to put a wordart stamp on
 
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

On 23 Jan 2007 04:09:58 -0800, "pano" wrote:

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....

pano
Pick which sheet to put a wordart stamp on
 

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
Pick which sheet to put a wordart stamp on
 
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

pano
Pick which sheet to put a wordart stamp on
 
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 -

Gord Dibben
Pick which sheet to put a wordart stamp on
 
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 -

pano
Pick which sheet to put a wordart stamp on
 
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 -

Gord Dibben
Pick which sheet to put a wordart stamp on
 
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 -


All times are GMT +1. The time now is 12:11 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com