[Mike S[_5_]]

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

[GS[_6_]]

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

[Mike S[_5_]]

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.

[GS[_6_]]

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

[Mike S[_5_]]

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.

[GS[_6_]]

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

[GS[_6_]]

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

[Mike S[_5_]]

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