"ArielZusya" skrev i en meddelelse
...
I've got an excel file that, through code attached to a userform, it
imports
data from a text file, cleans that data up, sorts that data, removes some
of
it, and exports that data (via the worksheet to which it was imported) and
a
couple of other worksheets to a new file, saves that file, and emails that
file to a specified addy. Right now the file that is being emailed is
code-less. I'd like to change that. I'd like to add a button to one of
the
worksheets that will allow a user to click that button and have it do a
task.
I've figured out how to copy a module from my original excel file into the
newly created excel file and I've figured out how to create a button on
the
worksheet in the new file and I've figured out how to have that button
point
to the sub in the module I transfered. What I can't seem to figure out is
how to change the caption on the button. My create button code, which is
called after I've already moved onto the correct sheet in the new
workbook,
and which code is located in the original excel file, looks like this:

Sub CreateButtonOnMyNewSheet()
ActiveSheet.Buttons.Add(460, 75, 140, 30).Select
Selection.OnAction = "btnDeleteAndUpdateSeatingChart"
Selection.Name = "btnDeleteAndUpdate"
ActiveSheet.Shapes("btnDeleteAndUpdate").Select
Selection.ShapeRange.AlternativeText = "Delete and Update Charts and
Lists"
ActiveSheet.Shapes("btnDeleteAndUpdate").Select
btnDeleteAndUpdate.Caption = "Delete and Update Charts and Lists"
With Selection.Characters(Start:=1, Length:=50).Font
.Name = "Calibri"
.FontStyle = "Regular"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = 2
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
End Sub

It appears as though I can't just do "btnDeleteAndUpdate.Caption =" but
I'm
not sure what to do instead. Thinking I need to tell it to select the
text
on the button and then replace it I've tried:

Selection.Characters.Text = "Button 1"
Selection.Characters.Text = "Delete and Update Charts and Lists"

I've also tried doing the same without selecting first:

Selection.Characters.Text = "Delete and Update Charts and Lists"

but I get errors for both and it doesn't do what I need it to. Your help
will be greatly appreciated. Thanks!

Hi

Sub CreateButtonOnMyNewSheet()
ActiveSheet.Buttons.Add(460, 75, 140, 30).Select
Selection.OnAction = "btnDeleteAndUpdateSeatingChart"
Selection.Name = "btnDeleteAndUpdate"
Selection.Caption = "Delete and Update Charts and Lists"
ActiveSheet.Shapes("btnDeleteAndUpdate").Select
Selection.ShapeRange.AlternativeText = "Delete and Update Charts and
Lists "
ActiveSheet.Shapes("btnDeleteAndUpdate").Select
'btnDeleteAndUpdate.Caption = "Delete and Update Charts and Lists"
With Selection.Characters(Start:=1, Length:=50).Font
.Name = "Calibri"
.FontStyle = "Regular"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = 2
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
End Sub

Regards,

Per