Command Button design question

[Casey]

Hi,
I trying to create a command button, that changes part of it's text based on the value of a single cell named range ("InvInvoice"). I have tried entering a formula into the Caption property, but couldn't make it work.
I then tried grouping a command button and a text box together and it works, but because the text on the command button centers itself, I have to oversize the command button to make room for the textbox. It looks klunky.
The static text is "Save and File Invoice #" and the changing text is 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 wrote:
>
> Hi,
> I trying to create a command button, that changes part of it's text
> based on the value of a single cell named range ("InvInvoice"). I have
> tried entering a formula into the Caption property, but couldn't make
> it work.
> I then tried grouping a command button and a text box together and it
> works, but because the text on the command button centers itself, I
> have to oversize the command button to make room for the textbox. It
> looks klunky.
> The static text is "Save and File Invoice #" and the changing text is
> the invoice number located in the named range.
> Any ideas?
>
> --
> Casey
>
> ------------------------------------------------------------------------
> Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
> View this thread: http://www.excelforum.com/showthread...hreadid=522740

--

Dave Peterson

[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 the commandbutton from the control toolbar.
I know you post so often, helping people like me, no way you could remember everbody you help, but I remember and I know we are into the 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.



Casey wrote:
>
> Dave,
> Two options, thanks, and your preception is correct the Invoice #
> changes by manual entry so I'm using the Worksheet change event and the
> commandbutton from the control toolbar.
> I know you post so often, helping people like me, no way you could
> remember everbody you help, but I remember and I know we are into the
> 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.
>
> --
> Casey
>
> ------------------------------------------------------------------------
> Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
> View this thread: http://www.excelforum.com/showthread...hreadid=522740

--

Dave Peterson