Command Button design question

[Casey]

Hi,
I trying to create a command button, that changes part of it's tex
based on the value of a single cell named range ("InvInvoice"). I hav
tried entering a formula into the Caption property, but couldn't mak
it work.
I then tried grouping a command button and a text box together and i
works, but because the text on the command button centers itself,
have to oversize the command button to make room for the textbox. I
looks klunky.
The static text is "Save and File Invoice #" and the changing text i
the invoice number located in the named range.
Any ideas

 

[Dave Peterson]

You could use an event that looks for a change to that range and adjusts the
caption of that commandbutton's (from the Control toolbox toolbar).

Or ....

You could use a button from the forms toolbar and a helper cell.

I put:
="Save and File Invoice #" & text(InvVoice,"00000")
in a cell (say A1)

Then I added a button from the forms toolbar (and assigned the macro to that
button)

But with the button selected, I put
=a1
in the formula bar.

Buttons from the forms toolbar have a lot fewer features compared to the
commandbuttons from the control toolbox toolbar.


If your InvVoice cell changes because of a typing change, you could use
something like:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("InvVoice")) Is Nothing Then Exit Sub

Me.CommandButton1.Caption _
= "Save and File Invoice #" & Format(Target.Value, "00000")

End Sub

 

[Casey]

Dave,
Two options, thanks, and your preception is correct the Invoice
changes by manual entry so I'm using the Worksheet change event and th
commandbutton from the control toolbar.
I know you post so often, helping people like me, no way you coul
remember everbody you help, but I remember and I know we are into th
double digits with the number of answers I've received from you.
If you ever go skiing in Aspen (I live in Carbondale very close by)
e-mail me, I owe you a steak dinner at least

 

[Dave Peterson]

Thanks for the offer. I like the steak -- never skied though.