Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #108751 > unrolled thread

Embed drawing toolbar textbox or word doc in sheet, how to refer to it?

Back to article view | Back to microsoft.public.excel.programming

Contents

  Embed drawing toolbar textbox or word doc in sheet, how to refer to it? Mike S <mscir@yahoo.com> - 2016-04-15 16:40 -0700
    Re: Embed drawing toolbar textbox or word doc in sheet, how to refer to it? GS <gs@v.invalid> - 2016-04-15 21:07 -0400
      Re: Embed drawing toolbar textbox or word doc in sheet, how to refer to it? Mike S <mscir@yahoo.com> - 2016-04-15 22:37 -0700
        Re: Embed drawing toolbar textbox or word doc in sheet, how to refer to it? GS <gs@v.invalid> - 2016-04-16 02:01 -0400
          Re: Embed drawing toolbar textbox or word doc in sheet, how to refer to it? Mike S <mscir@yahoo.com> - 2016-04-15 23:39 -0700
            Re: Embed drawing toolbar textbox or word doc in sheet, how to refer to it? GS <gs@v.invalid> - 2016-04-16 06:34 -0400
    Re: Embed drawing toolbar textbox or word doc in sheet, how to refer to it? GS <gs@v.invalid> - 2016-04-16 07:21 -0400
      Re: Embed drawing toolbar textbox or word doc in sheet, how to refer to it? Mike S <mscir@yahoo.com> - 2016-04-16 22:14 -0700

#108751 — Embed drawing toolbar textbox or word doc in sheet, how to refer to it?

I need to generate a report that will work in Excel 2000 and later,  I'd 
like to embed a rich textbox control (with scrollbars), or a Word 
document if that isn't possible, where I can write the report and format 
it.

I can embed a textbox control from the drawing toolbar or a word 
document control in a worksheet, but I don't know how to refer to either 
one of them to write text and format it inside them, where can I find 
the name of the control so I can see the properties and work with it?

Also is there a way to get scrollbars to show on the rich textbox 
control? I read that there was not.

And is there any advantage to doing this stuff in a form vs embedding 
the control in a worksheet? The reports have to be both saved and 
printed. It seems like that should be possible from the worksheet just 
as easily as doing it with a form. I avoid Excel forms because they seem 
clunky to work with after being spoiled working with VB6.

TIA,
Mike

[toc] | [next] | [standalone]

#108752

Is there any reason you can't use a worksheet to format your report?

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#108758

On 4/15/2016 6:07 PM, GS wrote:
> Is there any reason you can't use a worksheet to format your report?

I will have multiple pages and I don't know how to paginate an Excel 
worksheet. Can I embed pagebreaks in the worksheet? If so that might work.

[toc] | [prev] | [next] | [standalone]

#108759

> On 4/15/2016 6:07 PM, GS wrote:
>> Is there any reason you can't use a worksheet to format your 
>> report?
>
> I will have multiple pages and I don't know how to paginate an Excel 
> worksheet. Can I embed pagebreaks in the worksheet? If so that might 
> work.

Yes, you absolutely can place manual pagebreaks. In v2003< it's on the 
Insert menu. In v2007> it's on the Page Layout tab. One caveat is you 
need to pay attention that your manual pagebreaks aren't screwed up by 
the default ones, meaning you may have to remove some so your page 
'flow' goes how you want. Once you PrintPreview, the print_area and 
pagebreaks appear on your worksheet and so can use these as guides for 
setting pagination.

For viewing in the worksheet you can turn off gridlines so your page 
appears like a word processor (sort of).

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#108760

On 4/15/2016 11:01 PM, GS wrote:
>> On 4/15/2016 6:07 PM, GS wrote:
>>> Is there any reason you can't use a worksheet to format your report?
>>
>> I will have multiple pages and I don't know how to paginate an Excel
>> worksheet. Can I embed pagebreaks in the worksheet? If so that might
>> work.
>
> Yes, you absolutely can place manual pagebreaks. In v2003< it's on the
> Insert menu. In v2007> it's on the Page Layout tab. One caveat is you
> need to pay attention that your manual pagebreaks aren't screwed up by
> the default ones, meaning you may have to remove some so your page
> 'flow' goes how you want. Once you PrintPreview, the print_area and
> pagebreaks appear on your worksheet and so can use these as guides for
> setting pagination.
>
> For viewing in the worksheet you can turn off gridlines so your page
> appears like a word processor (sort of).

Thank you for the detailed explanation. I'll look into this and see if 
this is easier than automating Word outside of the workbook.

[toc] | [prev] | [next] | [standalone]

#108762

> On 4/15/2016 11:01 PM, GS wrote:
>>> On 4/15/2016 6:07 PM, GS wrote:
>>>> Is there any reason you can't use a worksheet to format your 
>>>> report?
>>>
>>> I will have multiple pages and I don't know how to paginate an 
>>> Excel
>>> worksheet. Can I embed pagebreaks in the worksheet? If so that 
>>> might
>>> work.
>>
>> Yes, you absolutely can place manual pagebreaks. In v2003< it's on 
>> the
>> Insert menu. In v2007> it's on the Page Layout tab. One caveat is 
>> you
>> need to pay attention that your manual pagebreaks aren't screwed up 
>> by
>> the default ones, meaning you may have to remove some so your page
>> 'flow' goes how you want. Once you PrintPreview, the print_area and
>> pagebreaks appear on your worksheet and so can use these as guides 
>> for
>> setting pagination.
>>
>> For viewing in the worksheet you can turn off gridlines so your 
>> page
>> appears like a word processor (sort of).
>
> Thank you for the detailed explanation. I'll look into this and see 
> if this is easier than automating Word outside of the workbook.

I find it much easier, but it took some getting used to initially. 
Figuring out page width will be your biggest challenge, followed by 
whether to use a single wide column for text, or let it run over 
several columns.

I find designing forms (static or interactive) is easier with a graph 
paper grid layout so cells are square. Doing userguides/manuals using 
multiple fixed col widths works well if there's lots of images to 
arrange with text. Graphs and charts are easier too since their source 
data can be outside the Print_Area boundaries, and they update 
automatically without any OLE trickery between documents involved.

Output (for me) is usually PDF for distribution as read-only reports, 
but direct hard copy printouts are also easy. I find Excel outputs 
smaller PDF files than Word does, generally speaking. (Multi-page PDFs 
usually get treeview style navigation added via Adobe Acrobat)

The largest page layout I've done is 2-sided tabloid size paper for 
making 4 or 6 page brochures after folding.

Another benefit is the ability to store series/period reports in a 
single workbook on single or separate sheets. (Not to mention more 
easily share data between them without external refs!)

Over the years I've made a deliberate effort to eliminate need for 
using Word for anything, and so far haven't found any exceptions to 
using Excel for just about everything!

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#108763

> I need to generate a report that will work in Excel 2000 and later,  
> I'd like to embed a rich textbox control (with scrollbars), or a Word 
> document if that isn't possible, where I can write the report and 
> format it.
>
> I can embed a textbox control from the drawing toolbar or a word 
> document control in a worksheet, but I don't know how to refer to 
> either one of them to write text and format it inside them, where can 
> I find the name of the control so I can see the properties and work 
> with it?

Most controls include a properties dialog and/or userguide (hlp file) i 
addition to what lists in the Properties window.
>
> Also is there a way to get scrollbars to show on the rich textbox 
> control? I read that there was not.

Set the Scrollbars property to suit...

  0 = rtfNone
  1 = rtfHorizontal
  2 = rtfVertical
  3 = rtfBoth
>
> And is there any advantage to doing this stuff in a form vs embedding 
> the control in a worksheet? The reports have to be both saved and 
> printed. It seems like that should be possible from the worksheet 
> just as easily as doing it with a form. I avoid Excel forms because 
> they seem clunky to work with after being spoiled working with VB6.

Only VB6 thing I miss is the ability to use control arrays. One 
advantage I like is that chk/opt controls can be grouped outside of (or 
without) a container via their GroupName property.

FWIW
It's a bit of a chore getting userforms to display min/max controls and 
make them resizeable, but still doable nevertheless. (Once done, the 
form is reusable thereafter via 'Import...'!)

Making inter-dependant dropdowns via formulas on a worksheet is easier 
than doing same on a form which requires having to code. Worksheet 
dropdowns have data validation; userform dropdowns require code for 
this.

Cells can be disguised to look/feel/act like btn/chk/opt controls quite 
easily so they scroll/position properly when rows/cols visibility is 
toggled. Event code handles this quite easily! Adjacent cell content 
can auto-change via formulas depending on chk/opt cell choices.

Worksheet forms can be as interactive as you want, and are much easier 
to scroll/use than a long userform! Using 'helper' cols/rows outside 
the form area is a big bonus as these can be hidden at runtime. You can 
also toggle display of col/row headers and gridlines so your worksheet 
doesn't look like a worksheet.

HTH

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#108767

On 4/16/2016 4:21 AM, GS wrote:
>> I need to generate a report that will work in Excel 2000 and later,
>> I'd like to embed a rich textbox control (with scrollbars), or a Word
>> document if that isn't possible, where I can write the report and
>> format it.
>>
>> I can embed a textbox control from the drawing toolbar or a word
>> document control in a worksheet, but I don't know how to refer to
>> either one of them to write text and format it inside them, where can
>> I find the name of the control so I can see the properties and work
>> with it?
>
> Most controls include a properties dialog and/or userguide (hlp file) i
> addition to what lists in the Properties window.
>>
>> Also is there a way to get scrollbars to show on the rich textbox
>> control? I read that there was not.
>
> Set the Scrollbars property to suit...
>
>   0 = rtfNone
>   1 = rtfHorizontal
>   2 = rtfVertical
>   3 = rtfBoth
>>
>> And is there any advantage to doing this stuff in a form vs embedding
>> the control in a worksheet? The reports have to be both saved and
>> printed. It seems like that should be possible from the worksheet just
>> as easily as doing it with a form. I avoid Excel forms because they
>> seem clunky to work with after being spoiled working with VB6.
>
> Only VB6 thing I miss is the ability to use control arrays. One
> advantage I like is that chk/opt controls can be grouped outside of (or
> without) a container via their GroupName property.
>
> FWIW
> It's a bit of a chore getting userforms to display min/max controls and
> make them resizeable, but still doable nevertheless. (Once done, the
> form is reusable thereafter via 'Import...'!)
>
> Making inter-dependant dropdowns via formulas on a worksheet is easier
> than doing same on a form which requires having to code. Worksheet
> dropdowns have data validation; userform dropdowns require code for this.
>
> Cells can be disguised to look/feel/act like btn/chk/opt controls quite
> easily so they scroll/position properly when rows/cols visibility is
> toggled. Event code handles this quite easily! Adjacent cell content can
> auto-change via formulas depending on chk/opt cell choices.
>
> Worksheet forms can be as interactive as you want, and are much easier
> to scroll/use than a long userform! Using 'helper' cols/rows outside the
> form area is a big bonus as these can be hidden at runtime. You can also
> toggle display of col/row headers and gridlines so your worksheet
> doesn't look like a worksheet.
>
> HTH

Thank you for all of your thoughts... time to dig into this now.

Best Regards,
Mike

[toc] | [prev] | [standalone]

Back to top | Article view | microsoft.public.excel.programming

csiph-web