MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Button on worksheet with explicit CR/LF in caption

  Asked By: Ella    Date: Sep 14    Category: MS Office    Views: 1712

When I am designing a VBA form, I can edit the caption of a button from directly
inside the button, as well as from a properties window.

One of the things I can do when editing the caption directly inside the button
is insert an explicit line break to split the caption onto two or more lines
where I want it, rather than where Excel wants to do it. I insert the line
break with Shift-Enter.

Now, if I put a command button directly onto a worksheet, I don't seem to be
able to edit the caption from directly inside the button. (Shift-Enter in the
caption in a properties window doesn't insert a line break.)

I've also tried copy/paste of the break information and of the whole caption
from the caption of the button on the form to the button on the worksheet, but
the paste is ignored or I get just the first line of the caption.

I've also tried to copy/paste the whole button from the form to the worksheet,
but the paste is ignored.

Two questions:

(1) does anyone know how to put a line break into a caption of a button that is
directly in a worksheet?

(2) does anyone know how to copy a button off a VBA form and paste it directly
onto a worksheet?

(3) (yeah, I know I said 2) does anyone know of any other way of doing this at
design time (not run time) short of adding spaces until you get a line overflow?



6 Answers Found

Answer #1    Answered By: Elliot Evans     Answered On: Sep 14

Both Shift + Enter and Ctrl + Enter work for me to insert  a line  break
in the caption  of a button  on a worksheet.

I've tried copying a button from a userform to a sheet and that
doesn't work for me.

Is the line break  a version thing maybe? I'm using Excel 2000.

Answer #2    Answered By: Bes Massri     Answered On: Sep 14

I wasn't able to get into edit  mode on the face of the button  to change the
caption. I'd completely forgotten about CommandButton Object / Edit from
the right click. :-( Works fine for me now in 2003 and 2000.

Answer #3    Answered By: Naba Malik     Answered On: Sep 14

I also have tried copying controls from a userform to a worksheet. Have never
succeeded. However, it shouldn't be hard to write a macro which would store the
properties of a userform command button  in an array, then create a new button on
worksheet  and format it with the stored property values.

Answer #4    Answered By: Elizabeth Hughes     Answered On: Sep 14

Instead of displaying the Properties window, right-click on your new button  and
select Commandbutton Object >> edit  from the menu that appears. Now you can edit
the button text directly on the button. Shift-Enter and Ctrl-Enter both work to
insert a line  break (I tested in Excel 2002 & 2003).

As an aside, the command  button from the Forms toolbar uses a lot less
resources than the command button from the Control Toolbox (but it doesn't have
all the features of the Control Toolbox version).

Answer #5    Answered By: Arlene Harvey     Answered On: Sep 14

That would certainly work. I haven't been able to work out how to do it at
design time, though, which was my aim.

however, I can now fix my captions in
place and I'm back on track.

Answer #6    Answered By: Caroline Bowman     Answered On: Sep 14

This was exactly where I was going astray. I've used this option in the past
but had completely forgotten about it and confused myself by the fact that it is
not needed to edit  a caption  in a button  on a form.

I never use the old forms command  button - always the control toolbox one. This
is more for consistency with the other controls I use from the control toolbox
than for any other reason, I suppose.

Didn't find what you were looking for? Find more on Button on worksheet with explicit CR/LF in caption Or get search suggestion and latest updates.