I have several macros that I use to perform tasks in a spread sheet. This
sheet is used for several different types of data that I import. I have
generic buttons titles that describe the function the button will perform.
I'd like the title of the button to change depending on what type of data
I've imported.

For example: If cell A1 has the text Tractor House in it, then I want the
text on the button to say Print Tractor House. But if the text in cell A1 is
Truck Paper then the text on the button to say Print Truck Paper.

Can this be done in Excel with VB?

Don

the problem is if you have more than one button on a worksheet. Excel will
not know which button is associated with each cell unless you have a table in
excel indicating the button Name and the cell on the worksheet.

"Don M." wrote:

> I have several macros that I use to perform tasks in a spread sheet. This
> sheet is used for several different types of data that I import. I have
> generic buttons titles that describe the function the button will perform.
> I'd like the title of the button to change depending on what type of data
> I've imported.
>
> For example: If cell A1 has the text Tractor House in it, then I want the
> text on the button to say Print Tractor House. But if the text in cell A1 is
> Truck Paper then the text on the button to say Print Truck Paper.
>
> Can this be done in Excel with VB?
>
> Don

You mention having "generic buttons"... plural... are you asking for this
functionality on a single button? That is, is A1 associated with only one of
the several buttons you have and, as such, only that button's caption will
be changed as A1 changes? Also, where did you get the button from... the
Form's Toolbar or the Control Toolbox's Toolbar?

--
Rick (MVP - Excel)


"Don M." <(E-Mail Removed)> wrote in message
news:BD6784D4-ECBC-42E0-9575-(E-Mail Removed)...
>I have several macros that I use to perform tasks in a spread sheet. This
> sheet is used for several different types of data that I import. I have
> generic buttons titles that describe the function the button will perform.
> I'd like the title of the button to change depending on what type of data
> I've imported.
>
> For example: If cell A1 has the text Tractor House in it, then I want the
> text on the button to say Print Tractor House. But if the text in cell A1
> is
> Truck Paper then the text on the button to say Print Truck Paper.
>
> Can this be done in Excel with VB?
>
> Don

Autoshapes / Basic shapes / Bevel (looks like a double rectangle)

Format it to look like a button, no lines, fill - more colours - choose an
appropriate colour.
Font, center align vertically & horizontally.
Select the shape, put the cursor in the input bar, type an =, then the
cell-ref or click the linked cell
Assign to your macro

Regards,
Peter T

"Don M." <(E-Mail Removed)> wrote in message
news:BD6784D4-ECBC-42E0-9575-(E-Mail Removed)...
>I have several macros that I use to perform tasks in a spread sheet. This
> sheet is used for several different types of data that I import. I have
> generic buttons titles that describe the function the button will perform.
> I'd like the title of the button to change depending on what type of data
> I've imported.
>
> For example: If cell A1 has the text Tractor House in it, then I want the
> text on the button to say Print Tractor House. But if the text in cell A1
> is
> Truck Paper then the text on the button to say Print Truck Paper.
>
> Can this be done in Excel with VB?
>
> Don

Yes, there are nine cells who's text change according to the data imported. I
would like one of 9 different buttons to reference one of these nine cells.

A1 = TWE
B1 = TMW
C1 = TNE
D1 = TMA
E1 = TSE
F1 = TSC
G1 = TGL
H1 = TCE
I1 = TNC

Then the Button 1 says "Print TWE" and runs a macro assigned to it. It will
always run that macro, regardless of what the text is. The Button 2 then says
"Print TMW", etc .....

The next time I use the spread sheet the data in those nine cells will be
different text, and the buttons need to correlate to that text and will still
run the same macros that are assigned to them.

The only thing that changes is the text on the buttons.

As for where the buttons came from, I used the Forms toolbar and drew the
button out with the curser. But, I can use a different button if I have to to
make this work.

Don

"Rick (MVP - Excel)" wrote:

You mention having "generic buttons"... plural... are you asking for this
functionality on a single button? That is, is A1 associated with only one of
the several buttons you have and, as such, only that button's caption will
be changed as A1 changes? Also, where did you get the button from... the
Form's Toolbar or the Control Toolbox's Toolbar?

"Joel" wrote:

the problem is if you have more than one button on a worksheet. Excel will
not know which button is associated with each cell unless you have a table in
excel indicating the button Name and the cell on the worksheet.

I'm not sure what "Autoshapes" means. Is that a toolbar different from the
Forms toolbar?

"Peter T" wrote:

> Autoshapes / Basic shapes / Bevel (looks like a double rectangle)
>
> Format it to look like a button, no lines, fill - more colours - choose an
> appropriate colour.
> Font, center align vertically & horizontally.
> Select the shape, put the cursor in the input bar, type an =, then the
> cell-ref or click the linked cell
> Assign to your macro
>
> Regards,
> Peter T
>
> "Don M." <(E-Mail Removed)> wrote in message
> news:BD6784D4-ECBC-42E0-9575-(E-Mail Removed)...
> >I have several macros that I use to perform tasks in a spread sheet. This
> > sheet is used for several different types of data that I import. I have
> > generic buttons titles that describe the function the button will perform.
> > I'd like the title of the button to change depending on what type of data
> > I've imported.
> >
> > For example: If cell A1 has the text Tractor House in it, then I want the
> > text on the button to say Print Tractor House. But if the text in cell A1
> > is
> > Truck Paper then the text on the button to say Print Truck Paper.
> >
> > Can this be done in Excel with VB?
> >
> > Don
>
>
>

It's on the Drawing toolbar.

Regards,
Peter T

"Don M." <(E-Mail Removed)> wrote in message
news:6E8AA035-803D-4708-8125-(E-Mail Removed)...
> I'm not sure what "Autoshapes" means. Is that a toolbar different from the
> Forms toolbar?
>
> "Peter T" wrote:
>
>> Autoshapes / Basic shapes / Bevel (looks like a double rectangle)
>>
>> Format it to look like a button, no lines, fill - more colours - choose
>> an
>> appropriate colour.
>> Font, center align vertically & horizontally.
>> Select the shape, put the cursor in the input bar, type an =, then the
>> cell-ref or click the linked cell
>> Assign to your macro
>>
>> Regards,
>> Peter T
>>
>> "Don M." <(E-Mail Removed)> wrote in message
>> news:BD6784D4-ECBC-42E0-9575-(E-Mail Removed)...
>> >I have several macros that I use to perform tasks in a spread sheet.
>> >This
>> > sheet is used for several different types of data that I import. I have
>> > generic buttons titles that describe the function the button will
>> > perform.
>> > I'd like the title of the button to change depending on what type of
>> > data
>> > I've imported.
>> >
>> > For example: If cell A1 has the text Tractor House in it, then I want
>> > the
>> > text on the button to say Print Tractor House. But if the text in cell
>> > A1
>> > is
>> > Truck Paper then the text on the button to say Print Truck Paper.
>> >
>> > Can this be done in Excel with VB?
>> >
>> > Don
>>
>>
>>

The only reason I wanted to know where you got the button from is its origin
makes a difference on how you address it in code. Give the following a try.
Right click the tab at the bottom of the worksheet and select "View Code"
from the popup menu that appears, then copy/paste the following into the
code window that appears...

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Shapes("Button 1").TextFrame.Characters.Text = _
"Print " & Range("A1").Value
End Sub

Now, go back to the worksheet and enter something into A1... the button
caption (for the button named Button 1) should change as you wanted.

--
Rick (MVP - Excel)


"Don M." <(E-Mail Removed)> wrote in message
news:C2089BCB-15C5-4A61-8CE6-(E-Mail Removed)...
> Yes, there are nine cells who's text change according to the data
> imported. I
> would like one of 9 different buttons to reference one of these nine
> cells.
>
> A1 = TWE
> B1 = TMW
> C1 = TNE
> D1 = TMA
> E1 = TSE
> F1 = TSC
> G1 = TGL
> H1 = TCE
> I1 = TNC
>
> Then the Button 1 says "Print TWE" and runs a macro assigned to it. It
> will
> always run that macro, regardless of what the text is. The Button 2 then
> says
> "Print TMW", etc .....
>
> The next time I use the spread sheet the data in those nine cells will be
> different text, and the buttons need to correlate to that text and will
> still
> run the same macros that are assigned to them.
>
> The only thing that changes is the text on the buttons.
>
> As for where the buttons came from, I used the Forms toolbar and drew the
> button out with the curser. But, I can use a different button if I have to
> to
> make this work.
>
> Don
>
> "Rick (MVP - Excel)" wrote:
>
> You mention having "generic buttons"... plural... are you asking for this
> functionality on a single button? That is, is A1 associated with only one
> of
> the several buttons you have and, as such, only that button's caption will
> be changed as A1 changes? Also, where did you get the button from... the
> Form's Toolbar or the Control Toolbox's Toolbar?
>
> "Joel" wrote:
>
> the problem is if you have more than one button on a worksheet. Excel
> will
> not know which button is associated with each cell unless you have a table
> in
> excel indicating the button Name and the cell on the worksheet.

Rick, this works if I try it on a new, blank sheet. But, when I try this in
the existing sheet it interferes with other macros thst I run. I'm not
familiar with using code on the sheet tab. I'll have to read up on how
putting code there is different from the VB Editor that I usually see.
Don

"Rick Rothstein" wrote:

> The only reason I wanted to know where you got the button from is its origin
> makes a difference on how you address it in code. Give the following a try.
> Right click the tab at the bottom of the worksheet and select "View Code"
> from the popup menu that appears, then copy/paste the following into the
> code window that appears...
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> ActiveSheet.Shapes("Button 1").TextFrame.Characters.Text = _
> "Print " & Range("A1").Value
> End Sub
>
> Now, go back to the worksheet and enter something into A1... the button
> caption (for the button named Button 1) should change as you wanted.
>
> --
> Rick (MVP - Excel)
>
>
> "Don M." <(E-Mail Removed)> wrote in message
> news:C2089BCB-15C5-4A61-8CE6-(E-Mail Removed)...
> > Yes, there are nine cells who's text change according to the data
> > imported. I
> > would like one of 9 different buttons to reference one of these nine
> > cells.
> >
> > A1 = TWE
> > B1 = TMW
> > C1 = TNE
> > D1 = TMA
> > E1 = TSE
> > F1 = TSC
> > G1 = TGL
> > H1 = TCE
> > I1 = TNC
> >
> > Then the Button 1 says "Print TWE" and runs a macro assigned to it. It
> > will
> > always run that macro, regardless of what the text is. The Button 2 then
> > says
> > "Print TMW", etc .....
> >
> > The next time I use the spread sheet the data in those nine cells will be
> > different text, and the buttons need to correlate to that text and will
> > still
> > run the same macros that are assigned to them.
> >
> > The only thing that changes is the text on the buttons.
> >
> > As for where the buttons came from, I used the Forms toolbar and drew the
> > button out with the curser. But, I can use a different button if I have to
> > to
> > make this work.
> >
> > Don
> >
> > "Rick (MVP - Excel)" wrote:
> >
> > You mention having "generic buttons"... plural... are you asking for this
> > functionality on a single button? That is, is A1 associated with only one
> > of
> > the several buttons you have and, as such, only that button's caption will
> > be changed as A1 changes? Also, where did you get the button from... the
> > Form's Toolbar or the Control Toolbox's Toolbar?
> >
> > "Joel" wrote:
> >
> > the problem is if you have more than one button on a worksheet. Excel
> > will
> > not know which button is associated with each cell unless you have a table
> > in
> > excel indicating the button Name and the cell on the worksheet.
>
>

You would need to show us the code for the macros that are being interfered
with as well as any existing Change event code you might have before we can
tell you how to integrate my code into your project. I'm thinking (off the
top of my head) that you may be able to solve the macro interference problem
by doing something like this. Add a Module to your project (Insert/Module
from VBA editor's menu bar) and put this line of code in it...

Public SkipButtonCode As Boolean

Then change my suggested Change event code to this...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not SkipButtonCode Then
ActiveSheet.Shapes("Button 1").TextFrame.Characters.Text = _
"Print " & Range("A1").Value
SkipButtonCode = False
End If
'
' Put any other Change event code you have here
'
End Sub

Finally, put this code line at the beginning of all your macros...

SkipButtonCode = True

Doing all of the above should make the button caption changing code work the
same as it does in a new project.

--
Rick (MVP - Excel)


"Don M." <(E-Mail Removed)> wrote in message
news:C43F2ABA-56A8-48A6-A2AA-(E-Mail Removed)...
> Rick, this works if I try it on a new, blank sheet. But, when I try this
> in
> the existing sheet it interferes with other macros thst I run. I'm not
> familiar with using code on the sheet tab. I'll have to read up on how
> putting code there is different from the VB Editor that I usually see.
> Don
>
> "Rick Rothstein" wrote:
>
>> The only reason I wanted to know where you got the button from is its
>> origin
>> makes a difference on how you address it in code. Give the following a
>> try.
>> Right click the tab at the bottom of the worksheet and select "View Code"
>> from the popup menu that appears, then copy/paste the following into the
>> code window that appears...
>>
>> Private Sub Worksheet_Change(ByVal Target As Range)
>> ActiveSheet.Shapes("Button 1").TextFrame.Characters.Text = _
>> "Print " & Range("A1").Value
>> End Sub
>>
>> Now, go back to the worksheet and enter something into A1... the button
>> caption (for the button named Button 1) should change as you wanted.
>>
>> --
>> Rick (MVP - Excel)
>>
>>
>> "Don M." <(E-Mail Removed)> wrote in message
>> news:C2089BCB-15C5-4A61-8CE6-(E-Mail Removed)...
>> > Yes, there are nine cells who's text change according to the data
>> > imported. I
>> > would like one of 9 different buttons to reference one of these nine
>> > cells.
>> >
>> > A1 = TWE
>> > B1 = TMW
>> > C1 = TNE
>> > D1 = TMA
>> > E1 = TSE
>> > F1 = TSC
>> > G1 = TGL
>> > H1 = TCE
>> > I1 = TNC
>> >
>> > Then the Button 1 says "Print TWE" and runs a macro assigned to it. It
>> > will
>> > always run that macro, regardless of what the text is. The Button 2
>> > then
>> > says
>> > "Print TMW", etc .....
>> >
>> > The next time I use the spread sheet the data in those nine cells will
>> > be
>> > different text, and the buttons need to correlate to that text and will
>> > still
>> > run the same macros that are assigned to them.
>> >
>> > The only thing that changes is the text on the buttons.
>> >
>> > As for where the buttons came from, I used the Forms toolbar and drew
>> > the
>> > button out with the curser. But, I can use a different button if I have
>> > to
>> > to
>> > make this work.
>> >
>> > Don
>> >
>> > "Rick (MVP - Excel)" wrote:
>> >
>> > You mention having "generic buttons"... plural... are you asking for
>> > this
>> > functionality on a single button? That is, is A1 associated with only
>> > one
>> > of
>> > the several buttons you have and, as such, only that button's caption
>> > will
>> > be changed as A1 changes? Also, where did you get the button from...
>> > the
>> > Form's Toolbar or the Control Toolbox's Toolbar?
>> >
>> > "Joel" wrote:
>> >
>> > the problem is if you have more than one button on a worksheet. Excel
>> > will
>> > not know which button is associated with each cell unless you have a
>> > table
>> > in
>> > excel indicating the button Name and the cell on the worksheet.
>>
>>