Sub CreateButtonOnMyNewSheet()
dim myBTN as Button
set mybtn = ActiveSheet.Buttons.Add(460, 75, 140, 30)
mybtn.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.



ArielZusya wrote:

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!

"Dave Peterson" wrote:

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("realhomeworksheet namehere").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 wrote:

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!

--

Dave Peterson


--

Dave Peterson