Can the Text on a button form be referenced from a cell?

[Don M.]

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

 

[Joel]

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 Rothstein]

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?

 

[Peter T]

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.]

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

:

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?

:

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.]

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

 

[Peter T]

It's on the Drawing toolbar.

Regards,
Peter T

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

 

[Rick Rothstein]

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.

 

[Don M.]

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]

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.

 

[Don M.]

Well Rick, I don't know what I did different today. I tried doing the same
thing I did Friday that didn't work, but it worked today. I copied you code
to the other eight buttons and it works perfectly!

Thank you very much.

Don

Here's the Worksheet Code that I ended up with:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("C1").Value = "" Then
ActiveSheet.Shapes("Button 139").TextFrame.Characters.Text = ""
Else: ActiveSheet.Shapes("Button 139").TextFrame.Characters.Text =
"Print " & Range("C1").Value
End If

If Range("E1").Value = "" Then
ActiveSheet.Shapes("Button 148").TextFrame.Characters.Text = ""
Else: ActiveSheet.Shapes("Button 148").TextFrame.Characters.Text =
"Print " & Range("E1").Value
End If

If Range("G1").Value = "" Then
ActiveSheet.Shapes("Button 149").TextFrame.Characters.Text = ""
Else: ActiveSheet.Shapes("Button 149").TextFrame.Characters.Text =
"Print " & Range("G1").Value
End If

If Range("I1").Value = "" Then
ActiveSheet.Shapes("Button 150").TextFrame.Characters.Text = ""
Else: ActiveSheet.Shapes("Button 150").TextFrame.Characters.Text =
"Print " & Range("I1").Value
End If

If Range("K1").Value = "" Then
ActiveSheet.Shapes("Button 151").TextFrame.Characters.Text = ""
Else: ActiveSheet.Shapes("Button 151").TextFrame.Characters.Text =
"Print " & Range("K1").Value
End If

If Range("M1").Value = "" Then
ActiveSheet.Shapes("Button 152").TextFrame.Characters.Text = ""
Else: ActiveSheet.Shapes("Button 152").TextFrame.Characters.Text =
"Print " & Range("M1").Value
End If

If Range("O1").Value = "" Then
ActiveSheet.Shapes("Button 153").TextFrame.Characters.Text = ""
Else: ActiveSheet.Shapes("Button 153").TextFrame.Characters.Text =
"Print " & Range("O1").Value
End If

If Range("Q1").Value = "" Then
ActiveSheet.Shapes("Button 154").TextFrame.Characters.Text = ""
Else: ActiveSheet.Shapes("Button 154").TextFrame.Characters.Text =
"Print " & Range("Q1").Value
End If

If Range("S1").Value = "" Then
ActiveSheet.Shapes("Button 155").TextFrame.Characters.Text = ""
Else: ActiveSheet.Shapes("Button 155").TextFrame.Characters.Text =
"Print " & Range("S1").Value
End If

End Sub

 

[Peter T]

Just curiosity, why not link the text directly to a cells.

Regards,
Peter T

 

[Don M.]

Peter, I set up a button like this and while it worked well, it won't let me
use the formula that I need to name the button. I wanted to use a formula
like ="Print" & C1 but it will only let me use =C1 to name the button. I get
an error that says "The text you entered is not a valid reference or defined
range".

If there is a way to get the word Print in with the cell contents then I'll
see if your technique will work for me.

Don

 

[Don M.]

Rick, for some reason I got an error this morning on the button naming code
that I put in my last reply. It stopped on the first of the nine IF
statements in the ELSE part of the code.

If Range("C1").Value = "" Then
ActiveSheet.Shapes("Button 139").TextFrame.Characters.Text = ""

Stopped here -> Else: ActiveSheet.Shapes("Button139").TextFrame._
Characters.Text ="Print " & Range("C1").Value

End If

I don't know why. It worked yesterday over and over. I hit debug and then
hit F8 to step through the code and it did. I have no idea why it would stop
in error and then step through the code without changing anything. Out of
curiousity I cleared the workbook and ran the code again and it worked fine
without any errors.

Another odd thing, I use a small macro to enter text in three cells, one of
which is the cell that the button is named from. As I run this macro, the
code for the buttons runs everytime the small macro enters code. On the small
print jobs that's five times and on the largest job that 45 times. Does this
happen because the code in the sheet is designed to run everytime something
changes due to the line Worksheet_Change in the first line? How does placing
code on the sheet differ from running a macro in the way I am used to? I
guess it's not a problem since it loops through this code in a flash I just
thought it was wasteful and takes a long time when I am stepping through the
code fixing or adding things.

Here's a copy of one of the small naming mocros with a comment at each point
the button code runs. This macro is in Module 1 with all of the other
macro's.

Sub FileHeadersController()

' ControllerFileHeaders Macro
' Macro recorded 10/1/2008 by don-mirabella

Response = MsgBox("Hit OK to continue", vbOK, "Import Machinery Trader?")
If Response = vbCancel Then End

' Prevent BulkFileRename macro
Range("U5").FormulaR1C1 = "No" 'Button code runs

' Pub Name
Range("A2").FormulaR1C1 = "Controller" 'Button code runs
Range("A4").FormulaR1C1 = "Tabloid" 'Button code runs

' Mail File

Range("C4").FormulaR1C1 = "CN" 'Button code runs
Range("C2").FormulaR1C1 = "C___" 'Button code runs
Range("C1").FormulaR1C1 = "CON" 'Button code runs
'C1 is the cell the button is named from

End Sub

 

[Peter T]

As you've noticed you can't combine a cell link with anything else, but
shouldn't be a problem -

In (say) D1
="Print" & C1
Now link the button text to D1

or maybe change C1's formula
="Print" & some_cell_ref & " more Text"
and link to C1

Regards,
Peter T

 

[Don M.]

DUH, never even thought of that. Works quite well, thank you!

 

[Rick Rothstein]

The interference is probably due to the general nature of the worksheet
Change event (again, without seeing all your code, it is kind of hard to
make a guess). Did you by any chance see my last posting where I suggested
using a global SkipButtonCode Boolean variable so that your other macros
wouldn't trigger the Change event code? Try it... I think it will solve your
problem... remember to set it to True in each of your other macros.