Bulleting New Paragraphs

[clvestin]

I.m using a userform to collect text comments. I would then like to add these comments to a word document and add bullets to the comments. The problem is: I only want those paragraphs with text to be bulleted. Question: Does the bulleting remain in effect until I Un-bullet; in which case how do I clear the bulletization before forming another paragraph?

[VBA]
Public Sub Memos()

Dim WordApp As Object
Set WordApp = CreateObject("Word.Application")
SaveAsName = ThisWorkbook.Path & "\" & "wren" & Format(Date, "mmddyyyy") & ".doc"
' Send commands to Word
With WordApp
.Documents.Add
With .Selection
.Font.Size = 14
.Font.Bold = True
.ParagraphFormat.Alignment = 1
.typetext Text:="WRENSHALL LNG REPORT"
.typeparagraph
.typeparagraph
.Font.Size = 12
.ParagraphFormat.Alignment = 0
.Font.Bold = False
.typetext Text:="Date:" & vbTab & _
Format(Date, "mmmm d, yyyy")
.typeparagraph
.typetext Text:="To:" & vbTab & Region & " Manager"
.typeparagraph
.typetext Text:="From:" & vbTab & _
Application.UserName
.typeparagraph
.typeparagraph
'.TypeText Message
.typeparagraph
.typeparagraph
.typetext Text:="Liquid Inventory:" & vbTab & vbTab & Format(volvalue, "#.###")
.typeparagraph
.typetext Text:="Liquifaction Rate Y'Day:" & vbTab & Format(liqvalue, "##.###")
.typeparagraph
End With

Load UserForm1
UserForm1.Show

c = .activedocument.paragraphs.Count
For j = 0 To UBound(alphastr)

With .Selection
.typeparagraph
.typetext Text:=alphastr(j)
End With

c = .activedocument.paragraphs.Count
If alphastr(j) <> "" Then _
.activedocument.paragraphs(c).Range.listformat.Applybulletdefault

Next j

.activedocument.SaveAs Filename:=SaveAsName
.ActiveWindow.Close

End With
WordApp.Quit
Set WordApp = Nothing


End Sub


[/VBA]

[fumei]

1. My usual rant....

Could you please use the underscore character to make your code lines shorter. This prevents the code window from having to scroll left/right. This is a pain for those of use who do not use big monitors and high resolution. Your code:
[vba] .typetext Text:="Liquid Inventory:" & vbTab & vbTab & Format(volvalue, "#.###")
.typeparagraph [/vba]
would look like:
[vba] .typetext Text:="Liquid Inventory:" & vbTab & _
vbTab & Format(volvalue, "#.###")
.typeparagraph [/vba]

2. The issue of only having paragraphs with text be bulleted could be very easily solved by the proper use of styles. Word is designed to use styles. You are putting "extra" paragraphs (all those .typeparagraph) for no reason at all - except, I assume, to make space between paragraphs.

If you used styles you would never have to have those "extra" paragraphs. The paragraphs with text would have the space built in.

[mdmackillop]

Hi Gerry,
I was playing around with this as I can see my own uses for it. I'd like to set and clear tabs by passing a value to another sub, rather than repetious code in the main routine. I tried
[VBA] '.....
With WordApp
.Documents.Add
With .Selection
DoTab 2.5
'......

Sub DoTab(Pos As Double)
.ParagraphFormat.TabStops.Add Position:=(Pos * 28.35) _
, Alignment:=wdAlignTabLeft, Leader:=wdTabLeaderSpaces
End Sub
[/VBA]
but I'm getting "Invalid or unqualified reference" on .ParagraphFormat. Is there something more that needs to be passed to the sub?
Regards
Malcolm

[fumei]

I am assuming this is not all the code. Also I assume that WordApp is still in scope when your instructions are passed to the other Sub. So WHAT is the object you are using with
[vba].ParagraphFormat[/vba]

[mdmackillop]

I was using the code as above. amended as follows.
[VBA]Public Sub Memos()

Dim WordApp As Object
Set WordApp = CreateObject("Word.Application")
SaveAsName = ThisWorkbook.Path & "\" & "wren" & _
Format(Date, "mmddyyyy") & ".doc"
' Send commands to Word
With WordApp
.Documents.Add
With .Selection
'This works
.ParagraphFormat.TabStops.Add Position:=(2.5 * 28.35) _
, Alignment:=wdAlignTabLeft, Leader:=wdTabLeaderSpaces

'This doen't work
DoTab 2.5

.Font.Size = 14
.Font.Bold = True

'etc.[/VBA]

[fumei]

Uh,
[vba]Sub DoTab(Pos As Double)
.ParagraphFormat.TabStops.Add Position:=(Pos * 28.35) _
, Alignment:=wdAlignTabLeft, Leader:=wdTabLeaderSpaces
End Sub [/vba]
does not have a referenced object....if that is the entire code. It is an unqualified reference. Once the instruction passes to the other Sub, the original Selection (using the With) is not in scope. There must be more code.
[vba] With .Selection
'This works
' because the With Selection is in scope
.ParagraphFormat.TabStops.Add Position:=(2.5 * 28.35) _
, Alignment:=wdAlignTabLeft, Leader:=wdTabLeaderSpaces

'This doen't work
' because the With Selection is not passed in scope to _
' the Sub - if there is no other code in that Sub _
'...which is why I asked.
DoTab 2.5 [/vba]
Am I missing something???

[mdmackillop]

Hi Gerry,
The "Not in Scope" comment claifies things. It doesn't seem worthwhile calling another sub for this limited change.
Thanks
Malcolm

[fumei]

Not really. However, you could, in the first sub, create a Selection object, and pass that to the second.

[clvestin]

Sorry for the long lines..I guess the relevent code for my question would be below. It seems that applying the bulleting does not apply only to the first instance: that is the next empty line is bulleted, and then by reapplying the bulleting the next text filled paragraph is left bulletless. My first response was to assign bullets before the loop so that the apply would take effect. You're probably right about using a template or styles, but Word is not my forte. This routine is called from Excel and forms a fairly simple memo, so the loose code should do.




[VBA]
c = .activedocument.paragraphs.Count
For j = 0 To UBound(alphastr)

With .Selection
.typeparagraph
.typetext Text:=alphastr(j)
End With

c = .activedocument.paragraphs.Count
If alphastr(j) <> "" Then _
.activedocument.paragraphs(c).Range.listformat.Applybulletdefault

Next j
[/VBA]

[clvestin]

ok-I'm following the styles idea. I've played around in Word directly and think i'm getting a handle. From Excel however, I'm having some issues(probably with focus). Thusly

[VBA]
Public Sub MakeMemos()
Dim WordApp As Object

Set WordApp = CreateObject("Word.Application")

SaveAsName = ThisWorkbook.Path & "\" & "wren" & Format(Date, "mmddyyyy") & ".doc"
With WordApp
.documents.Add
With .Selection
.typeparagraph
.typeparagraph 'along with some other stuff;see 1st post for detail


c = WordApp.activedocument.paragraphs.Count
.paragraphs(c).Style = wdstylelistbullet4
.typetext Text:="Liquid Inventory:" & Format(volvalue, "#.###")
.typeparagraph

End With

set myrange = .activedocument.Range(Start:=.activedocument.paragraphs(9).Range.Start, End:=.activedocument.paragraphs(10).Range.End)
myrange.Style = wdstylelistbullet2

WordApp.Quit
Set WordApp = Nothing
End Sub

[/VBA]


Neither one of the style settings seems to work for me from Excel. Either an error of a "value out of range" or "object required". Any ideas?

[clvestin]

This doesn't work:

[VBA]
Set myrange = .activedocument.Range(Start:=.activedocument.paragraphs(9).Range.Start, End:=.activedocument.paragraphs(10).Range.End)
myrange.Style = wdstylelistbullet2

[/VBA]

this does:
[VBA]
Set myrange = .activedocument.Range(Start:=.activedocument.paragraphs(9).Range.Start, End:=.activedocument.paragraphs(10).Range.End)
myrange.Style = -55

[/VBA]

So.....How do I specify the enum and use the wdstylelistbullet2(which,strangely enough is easier to remember)?

[fumei]

Aaaahhhh, PLEASE use the underscore character.
[vba]Set myrange = .activedocument.Range _
(Start:=.activedocument.paragraphs(9).Range.Start, _
End:=.activedocument.paragraphs(10).Range.End)
myrange.Style = wdstylelistbullet2 [/vba]
See, isn't that better? It does not streeetttttttttttttttttttttttttttttttttttttttttttttttcccccccccccccccccccccc cccccccccchhhhhhhhhhhhhhhhhh out so far. And, in a real VBE you can control the tabs, and it makes the reading MUCH easier. The Start and End can be on separate lines for easier scanning. Using the underscore is not just for my benefit - although I admitedly whine a lot about it - but it DOES make for easier to read code.

As for your problem, try:
[vba]myrange.Style = .activedocument.styles wdstylelistbullet2 [/vba]
You are declaring Wordapp as an Object, not an Application, so you are using late binding: the wdType must be specified.