Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > Pick which sheet to put a wordart stamp on
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 84
Default 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....

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 22,906
Default 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....

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 84
Default 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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 22,906
Default 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

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 84
Default 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 -

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 22,906
Default 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 -

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 84
Default 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 -

Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 22,906
Default 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 -

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
IF statement tom Excel Discussion (Misc queries) 6 November 16th 06 11:57 AM
macro unouwanme Excel Discussion (Misc queries) 9 August 31st 06 09:38 PM
Sum up columns in different sheet with error check zeyneddine Excel Discussion (Misc queries) 13 July 10th 06 01:21 PM
Using a relative SHEET reference for source data in a chart James Charts and Charting in Excel 6 August 16th 05 05:07 PM
Does excel recognise names rather than cells? Sue Excel Worksheet Functions 9 May 22nd 05 04:51 AM


All times are GMT +1. The time now is 05:58 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement