VBA to create button on sheet after copy and to change the caption

[ArielZusya]

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!

 

[Per Jessen]

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

 

[Dave Peterson]

How about an alternative?

Create a workbook that has that sheet nicely formatted and with the button
already added. Give it a nice caption and assign it the macro in this new
workbook. Save this new workbook as a nice template file (*.xlt in xl2003 and
below, *.xltm (I _think_) in xl2007 in a nice location.

Now instead of creating the button and messing around with the code that copies
code, you can just use this template file. Just put all your csv data on this
worksheet, do your work and save it.

One of the nice things about this is that you can protect the workbook's
project--and not have to worry about macro security problems.

Dim NewWkbk as workbook
dim CSVWks as worksheet

workbooks.opentext filename:="C:\somefolder\filename.csv"
set csvwks = activesheet

'do anything you want to the csvwks worksheet
'(or wait until later and do it in the template workbook's worksheet

'create a new workbook based on the template
set newwkbk = workbooks.add(template:="C:\my documents\mytemplate.xlt")

csvwks.usedrange.copy _
destination:=newwkbk.worksheets("realhomeworksheetnamehere").range("a1")

csvwks.parent.close savechanges:=false

'do more things with the newwkbk's worksheet

newwkbk.saveas filename:="C:\whateveryouwanthere.xls", _
fileformat:=xlworkbooknormal

newwkbk.close savechanges:=false

=======
The more stuff you put into that template (row and column headers, print layout,
formatting...), the less you'll have to do in code.

 

[ArielZusya]

Thanks for your response. trouble is "Selection.Caption" doesn't work. It
returns:

Runtime Error '1004':
Unable to set the Caption property of the Button class

any other thoughts? Note, that this is a button on a worksheet not on a
userform or a menu or something else. thanks!

 

[Dave Peterson]

ps. I bet this would work better if you use a commandbutton from the control
toolbox toolbar. The code associated with this commandbutton would be under
that sheet.

If you assign a macro to that button from the forms toolbar, you may have to
reassign it in code. That assignment could (will!) be pointing to the *.xlt
file--not the newly created file.

 

[ArielZusya]

I've played with this option. As for security... it's a closed system so I
don't have to worry about outside attacks and as far as I've been told I'm
the only person on the inside writing vba of any sort so I'm not particularly
concerned about security. The trouble is the user who will be using this is
a bit of a technophob and the less I add to her computer the better. Adding
templates (even if she doesn't ultimately know what that's about) is likely
to spook her. I'd prefer to keep this in one file which does all the heavy
lifting. As a side benefit, this lets me update the system by replacing the
one file rather than by messing with two or more (there are a number of files
being exported depending on the user task). It also lets me distribute this
file to others in our system. So... back to the original question...
changing the text on the button... ".caption" doesn't seem to work for
buttons on worksheets. Any ideas? Thanks!

 

[ArielZusya]

I think you're missing my intention. I'm assigning all of this in code. I'm
not doing anything with buttons or toolboxes. I'm trying to use vba to
dynamically create all of this on the fly. I just need someone to help me
figure out the proper vba to change the text on the button I've added. I
personally don't care if it is an activex command button or just a regular
button... so if you have some code that will allow me to reassign the
commandbutton's text to something else, I'm happy to hear it (though I'm not
sure why I can't just use the button object). So... I can't just do a
"Selection.Caption" for the command button... any idea what it would be?
Thanks!

ps. I bet this would work better if you use a commandbutton from the control
toolbox toolbar. The code associated with this commandbutton would be under
that sheet.

If you assign a macro to that button from the forms toolbar, you may have to
reassign it in code. That assignment could (will!) be pointing to the *.xlt
file--not the newly created file.

 

[Dave Peterson]

Sub CreateButtonOnMyNewSheet()
dim myBTN as Buttonmybtn.caption = "what do you want here"

And the security isn't about outside attacks--it's excel's security that allows
macros to run or that allows your code to change code.