Need help with VB to quickly populate a letter

[jarvster]

UPDATE: See my 2nd post below to see my first stab at coding

Firstly, I apologise if any of this has been covered before - I tried a search but my query is rather specific so I thought I would post and see if anyone can help!

Just as background - I am familiar with recording Word macros and back when I used to have Word 1995 I used to know how to use dialog editor to create custom dialog boxes to make certain tasks happen. I am not familiar with VB but I have been dabbling to learn my way around.

The task I am trying to automate is the population of a letter. I use a template letter to send to customers but there are many sections which need to be adapted for the particular customer. What I envisage happening is having a dialog box with lots of checkboxes. Depending on what checkboxes are ticked, the letter is populated with the correct info. The most basic example would be a check box for male and one for female (thus inserting he or she through as appropriate) or a check box for me or my collegaue (thus inserting my name at the end or hers).

I assume that the best way to proceed is using fields for the blanks throughout my letter.

I have dabbled with the userform and created a dialog box with loads of checkboxes which can be ticked (obivously I don't want male and female to both be ticked (perhaps an option button would be better).

Anyway - does all of this sound do-able? Is there a better way of doing this which doesn't use fields?

Does anyone have some example code they could provide me so I can play with it?

If anyone can give me tips or assistance with this it would be greatly appreciated

EDIT: I forgot to say - I am playing around with Word 07 but the PCs i intend to use it on run Word 03

EDIT: Further thoughts - I could avoid using fields/forms and have the letter with [NAME] at the end and have a "jarv" checkbox use Autocorrect to replace NAME with "jarv". Does this seem more sensible? I suppose I could also use checkboxes to insert entire paragraphs in my letter

[si_the_geek]

Thread moved to 'Office Development/VBA' forum (note that the "VB Editor" in Office programs is actually VBA rather than VB, so the 'VB6' forum is not really apt)

[jarvster]

Thread moved to 'Office Development/VBA' forum (note that the "VB Editor" in Office programs is actually VBA rather than VB, so the 'VB6' forum is not really apt)

Ah, sorry, excuse my ignorance! I am learning quick however!

I thought maybe I should put in a bit more legwork and try to meet you experts half way. I designed a userform (simple one at first) and the following is the result. I started my using my template letter and inserting boomarks rather than fields.

Option Explicit

Private Sub UserForm_Initialize()


End Sub

 Private Sub cmdOK_Click()
     Dim strFee As String
    If optFee1 = True Then strFee = "Jarv"
    If optFee2 = True Then strFee = "Sarah"
   Application.ScreenUpdating = False
   With ActiveDocument
      .Bookmarks("RecipientName").Range.Text = txtRecipientName.Value
      .Bookmarks("SenderName").Range.Text = strFee
    End With
   Application.ScreenUpdating = True
   Unload Me
End Sub

I am not sure about the Option Explicit thing and why that is needed but I saw it somewhere else.

Anyway, although the above worked I have some questions:
1) I created a macro to run this so I can attach to a toolbar - I presume this only needs to be this simple:

Sub Rule2()
'
' Rule2 Macro
'

frmRule2.Show

End Sub

2) I note I had to save my letter as a ".docm". Presumably I wouldn't have to do this with Word 03 - just enable macros in the template?

So does this seem like Im going down the wrong path or doing things the long way? Any help much appreicated

[sparbag]

Hello,

Option Explicit, when set as you have done, specifically demands that all the variables that are used within the program must be dimensioned within the code.

Dim strName As String

This is good programming practice because if you do not do this the programmer may make spelling mistakes for variables. This will cause the compiler to create Variant variables to handle the information for you, which is bad.

Automating Word documents is not as difficult as you would think. When you are starting out you will quickly realise that you will use the same methods over and over again.
Personally I would create a function in a module for any information that is going to be repetitive as this will make the code more portable.
Secondly as you are creating code for Word 2003 then the document must use the .doc Word type. Create the function in a Word document that has been saved as a 2003 Word file.

As there is very little information to go on as of yet so post back when you know that any macro or function that you have built will work in 2003. As this is most important.

Kind regards

Steve

[jarvster]

Ok This is only half done but my compiler doesn't like it. Keeps telling me "Block If without End If". At the moment it is highlighting the End Sub and telling me that!

Thanks for your help

Private Sub cmdOK_Click()
   Dim strFee
   Dim strConvo As String
   Dim strClient As String
   Dim strPurchaseSale As String
   Dim strPurchaseSale1 As String
   Dim strPurchaseSale2 As String
   Dim strClient2 As String
   Dim strGS As String
   Dim strCoholder As String
   If optFee1 = True Then
    strFee = "Dave" _
      & vbCrLf & "Advisor" _
      & vbCrLf & "UK"
   End If
   If OptFee2 = True Then
    strFee = "Sarah" _
      & vbCrLf & "Advisor" _
      & vbCrLf & "UK"
   End If
   If optConvo1 = True Then
    strConvo = "***"
   If optConvo2 = True Then
    strConvo = "***"
   If optConvo3 = True Then
     strConvo = "***"
   End If
   If optClient1 = True Then
    strClient = "you"
   If optClient2 = True Then
    strClient = "you"
   If optClient3 = True Then
    strClient = "the Group"
   If optClient4 = True Then
    strClient = "the Group"
   End If
   If optClient1 = True Then
    strPurchaseSale = "the sale"
   If optClient2 = True Then
    strPurchaseSale = "the purchase"
   If optClient3 = True Then
    strPurchaseSale = "the sale"
   If optClient4 = True Then
    strPurchaseSale = "the purchase"
   End If
   If optClient1 = True Then
    strPurchaseSale1 = "the sale"
   If optClient2 = True Then
    strPurchaseSale1 = "the purchase"
   If optClient3 = True Then
    strPurchaseSale1 = "the sale"
   If optClient4 = True Then
    strPurchaseSale1 = "the purchase"
   End If
   If optClient1 = True Then
    strPurchaseSale1 = "the sale"
   If optClient2 = True Then
    strPurchaseSale1 = "the purchase"
   If optClient3 = True Then
    strPurchaseSale1 = "the sale"
   If optClient4 = True Then
    strPurchaseSale1 = "the purchase"
   End If
   If optClient1 = True Then
    strClient2 = "you"
   If optClient2 = True Then
    strClient2 = "you"
   If optClient3 = True Then
    strClient2 = "the group"
   If optClient4 = True Then
    strClient2 = "the group"
   End If
   If optGS1 = True Then
    strGS = "***"
   If optGS2 = True Then
    strGS = "***"
   If optGS3 = True Then
    strGS = "**"
   End If
   If optClient1 = True Then
    strCoholder = txtOtherside.Value
   If optClient2 = True Then
    strCoholder = "your"
   If optClient3 = True Then
    strCoholder = txtOtherside.Value
   If optClient4 = True Then
    strCoholder = "your"
   End If
   Application.ScreenUpdating = False
   With ActiveDocument
      .Bookmarks("RecipientName").Range.Text = txtRecipientName.Value
      .Bookmarks("PName").Range.Text = txtPName.Value
      .Bookmarks("PName1").Range.Text = txtPName.Value
      .Bookmarks("PName2").Range.Text = txtPName.Value
      .Bookmarks("PName3").Range.Text = txtPName.Value
      .Bookmarks("Ma").Range.Text = txtMa.Value
      .Bookmarks("Client").Range.Text = strClient
      .Bookmarks("PurchaseSale").Range.Text = strPurchaseSale
      .Bookmarks("Convo").Range.Text = strConvo
      .Bookmarks("SenderName").Range.Text = strFee
      .Bookmarks("PurchaseSale1").Range.Text = strPurchaseSale1
      .Bookmarks("PurchaseSale2").Range.Text = strPurchaseSale2
      .Bookmarks("Client2").Range.Text = strClient2
      .Bookmarks("GS").Range.Text = strGS
      .Bookmarks("Client3").Range.Text = txtOtherside.Value
      .Bookmarks("Coholder").Range.Text = strCoholder
   End With
   Application.ScreenUpdating = True
   Unload Me
End Sub

[westconn1]

i am not surprised, there is a heap of missing end if

If optClient2 = True Then
    strPurchaseSale1 = "the purchase"

each one like this requires end if
for simple if statements
If optClient2 = True Then strPurchaseSale1 = "the purchase"
on one line do not require end if

[jarvster]

i am not surprised, there is a heap of missing end if

If optClient2 = True Then
    strPurchaseSale1 = "the purchase"

each one like this requires end if
for simple if statements
If optClient2 = True Then strPurchaseSale1 = "the purchase"
on one line do not require end if

Ahh, having spotted some code online I assumed that all the option buttons in the same group only required one "End If" at the end.

Orignally the code looked like it does below and I had those statements on one line but I broke it up onto different lines since I was getting the same message when it looked like this:

Private Sub cmdOK_Click()
   Dim strFee
   Dim strConvo As String
   Dim strClient As String
   Dim strPurchaseSale As String
   Dim strPurchaseSale1 As String
   Dim strPurchaseSale2 As String
   Dim strClient2 As String
   Dim strGS As String
   Dim strCoholder As String
   If optFee1 = True Then strFee = "Dave" _
      & vbCrLf & "Advisor" _
      & vbCrLf & "UK"
   End If
   If OptFee2 = True Then strFee = "Sarah" _
      & vbCrLf & "Advisor" _
      & vbCrLf & "UK"
   End If
   If optConvo1 = True Then strConvo = "***"
   If optConvo2 = True Then strConvo = "***"
   If optConvo3 = True Then strConvo = "***"
   End If
   If optClient1 = True Then strClient = "you"
   If optClient2 = True Then strClient = "you"
   If optClient3 = True Then strClient = "the Group"
   If optClient4 = True Then strClient = "the Group"
   End If
   If optClient1 = True Then strPurchaseSale = "the sale"
   If optClient2 = True Then strPurchaseSale = "the purchase"
   If optClient3 = True Then strPurchaseSale = "the sale"
   If optClient4 = True Then strPurchaseSale = "the purchase"
   End If
   If optClient1 = True Then strPurchaseSale1 = "the sale"
   If optClient2 = True Then strPurchaseSale1 = "the purchase"
   If optClient3 = True Then strPurchaseSale1 = "the sale"
   If optClient4 = True Then strPurchaseSale1 = "the purchase"
   End If
   If optClient1 = True Then strPurchaseSale1 = "the sale"
   If optClient2 = True Then strPurchaseSale1 = "the purchase"
   If optClient3 = True Then strPurchaseSale1 = "the sale"
   If optClient4 = True Then strPurchaseSale1 = "the purchase"
   End If
   If optClient1 = True Then strClient2 = "you"
   If optClient2 = True Then strClient2 = "you"
   If optClient3 = True Then strClient2 = "the group"
   If optClient4 = True Then strClient2 = "the group"
   End If
   If optGS1 = True Then strGS = "***"
   If optGS2 = True Then strGS = "***"
   If optGS3 = True Then strGS = "**"
   End If
   If optClient1 = True Then strCoholder = txtOtherside.Value
   If optClient2 = True Then strCoholder = "your"
   If optClient3 = True Then strCoholder = txtOtherside.Value
   If optClient4 = True Then strCoholder = "your"
   End If
   Application.ScreenUpdating = False
   With ActiveDocument
      .Bookmarks("RecipientName").Range.Text = txtRecipientName.Value
      .Bookmarks("PName").Range.Text = txtPName.Value
      .Bookmarks("PName1").Range.Text = txtPName.Value
      .Bookmarks("PName2").Range.Text = txtPName.Value
      .Bookmarks("PName3").Range.Text = txtPName.Value
      .Bookmarks("Ma").Range.Text = txtMa.Value
      .Bookmarks("Client").Range.Text = strClient
      .Bookmarks("PurchaseSale").Range.Text = strPurchaseSale
      .Bookmarks("Convo").Range.Text = strConvo
      .Bookmarks("SenderName").Range.Text = strFee
      .Bookmarks("PurchaseSale1").Range.Text = strPurchaseSale1
      .Bookmarks("PurchaseSale2").Range.Text = strPurchaseSale2
      .Bookmarks("Client2").Range.Text = strClient2
      .Bookmarks("GS").Range.Text = strGS
      .Bookmarks("Client3").Range.Text = txtOtherside.Value
      .Bookmarks("Coholder").Range.Text = strCoholder
   End With
   Application.ScreenUpdating = True
   Unload Me
End Sub

What is the problem in this code? Do I only need one End If at the end of all those "If"s?

Thanks for your help!

[westconn1]

in this code, as far as i could see, you do not need any end if at all, as all if are on 1 line

this makes no sense as you check same option buttons, several times

[jarvster]

in this code, as far as i could see, you do not need any end if at all, as all if are on 1 line

this makes no sense as you check same option buttons, several times

Ok Ill give that a go (deleting the End Ifs)

What Im trying to achieve with checking of the same option button, is that the option will have several effects throughout my letter inserting different words into different bookmarks. In the following extract optClient will tell me whether one of four options (Buyer - individual, Seller - individual, Buyer - group, Seller - group). Accordingly, I can populate several bookmarks with that knowledge:

   If optClient1 = True Then strClient = "you"
   If optClient2 = True Then strClient = "you"
   If optClient3 = True Then strClient = "the Group"
   If optClient4 = True Then strClient = "the Group"
   End If
   If optClient1 = True Then strPurchaseSale = "the sale"
   If optClient2 = True Then strPurchaseSale = "the purchase"
   If optClient3 = True Then strPurchaseSale = "the sale"
   If optClient4 = True Then strPurchaseSale = "the purchase"
   End If
   If optClient1 = True Then strPurchaseSale1 = "the sale"
   If optClient2 = True Then strPurchaseSale1 = "the purchase"
   If optClient3 = True Then strPurchaseSale1 = "the sale"
   If optClient4 = True Then strPurchaseSale1 = "the purchase"
   End If
   If optClient1 = True Then strPurchaseSale2 = "the sale"
   If optClient2 = True Then strPurchaseSale2 = "the purchase"
   If optClient3 = True Then strPurchaseSale2 = "the sale"
   If optClient4 = True Then strPurchaseSale2 = "the purchase"

I get what you are saying about removing the End Ifs so Ill remove all of these. Should I attempt to stack commands for the options? Eg:

If optClient1 = True Then strClient = "you" & strPurchaseSale = "the sale" & strPurchaseSale1 = "the sale" & strPurchaseSale2 = "the sale"

If optClient2 =...

Thanks for your help!

[westconn1]

if optclinet1 then
    strClient = "you"
    strPurchaseSale = "the sale"
    strPurchaseSale1 = "the sale"
    strPurchaseSale2 = "the sale"end if

only loop through the buttons once at any time, set all values at 1 time

i would do like

select case true
  case opt1
    strClient = "you"
    strPurchaseSale = "the sale"
    strPurchaseSale1 = "the sale"
    strPurchaseSale2 = "the sale"
  case opt2
    strClient = "you2"
    strPurchaseSale = "the sale2"
    strPurchaseSale1 = "the sale2"
    strPurchaseSale2 = "the sale2" 
  case opt3
end select

[jarvster]

i would do like

select case true
  case opt1
    strClient = "you"
    strPurchaseSale = "the sale"
    strPurchaseSale1 = "the sale"
    strPurchaseSale2 = "the sale"
  case opt2
    strClient = "you2"
    strPurchaseSale = "the sale2"
    strPurchaseSale1 = "the sale2"
    strPurchaseSale2 = "the sale2" 
  case opt3
end select

GENIUS! This worked like a charm I missed the End Select on my first run. Anyway, now my code looks a lot tidier - didn't know about the case function but prefer it to the If function! I ran a trial run of my code and it work - inputting the info in the right places.

I should now be able to complete the code although I have some checkboxes to incorporate.

I have a couple of queries - is it possible (I am sure it is) to mix two variables for the case to be "true" - ie an option must be selected AND a tick box for event A to happen? Eg If just one of two options is selected you get either A or B, if a tick box is selected you get either C or D on the same two options. I gave an example above with optClient (buyer - individual, buyer - group, etc) I would prefer just to have (Buyer and Seller options) and a tick box for group (with the default being an individual). Is this possible?

Thanks you all your help guys

EDIT: Hmm, there seems to be a minor problem - Although Im only halfway through coding all the events, I notice that some of the commands aren't being carried out (as if I hasn't chosen an option for optGS). If I just run the userform and only select an option for optGS, then it inserts the wording. Do you have to get the order right (ie order of bookmarks on the Word doc? or order of the option buttons as they appear (in frame order on the dialog box userform?)

[westconn1]

Do you have to get the order right (ie order of bookmarks on the Word doc? or order of the option buttons as they appear (in frame order on the dialog box userform?)

mostly no

yes you can combine options, though sometimes better not to

select case true
        case opt1
          strClient = "you"
          strPurchaseSale = "the sale"
          strPurchaseSale1 = "the sale"
          strPurchaseSale2 = "the sale"
          if chkgroup then strclient = "group"
        case opt2
          strClient = "you2"
          strPurchaseSale = "the sale2"
          strPurchaseSale1 = "the sale2"
          strPurchaseSale2 = "the sale2"
        case opt3
      end select

[jarvster]

Cheers - Ill give the chkbox thing a whirl when Ive sorted this problem.

Ive got this far and my coding works but it seems as though some functions aren't being carried out. OptClient1 has a lot of work to do populating several bookmarks but seems to give up doing some of them. optGS doesn't do its job at all unless OI only choose an option in that group before clicking OK. Any ideas? Can you see a reason why any of these functions shouldn't be carried out?

Option Explicit

Private Sub cmdOK_Click()
   Dim strFee As String
   Dim strConvo As String
   Dim strClient As String
   Dim strPS As String
   Dim strPS1 As String
   Dim strPS2 As String
   Dim strClient2 As String
   Dim strGP As String
   Dim strCoholder As String
   Dim strGP1 As String
   Dim strClient3 As String
   Dim strGP2 As String
   Dim strGP3 As String
   Dim strGP4Special As String
   Dim strOtherF As String
   Dim strOtherF1 As String
   Dim strPS3 As String
   Dim strRts As String
   Dim strRts1 As String
   Dim strOtherF2 As String
   Dim strPS4 As String
   Dim strExConf1 As String
   Select Case True
   Case optFee1
    strFee = "DAVID" _
      & vbCrLf & "Advisor"
    strOtherF = "Sarah J"
    strOtherF1 = "Sarah"
    strRts = "220"
    strRts1 = "210"
    strOtherF2 = "Sarah's"
   Case OptFee2
    strFee = "SARAH " _
      & vbCrLf & "Advisor"
    strOtherF = "David P"
    strOtherF1 = "David"
    strRts = "210"
    strRts1 = "220"
    strOtherF2 = "David's"
   Case optConvo1
    strConvo = "***"
   Case optConvo2
    strConvo = "***"
   Case optConvo3
    strConvo = "***"
   Case optClient1
    strClient = "you"
    strPS = "the purchase"
    strPS1 = "the purchase"
    strPS2 = "the purchase"
    strPS3 = "the purchase"
    strClient2 = "you"
    strCoholder = txtOtherside.Value
    strClient3 = "your name"
    strPS4 = "purchase"
    strExConf1 = "E Agreement (optional)" _
      & vbCrLf & "******"
   Case optClient2
    strClient = "you"
    strPS = "the sale"
    strPS1 = "the sale"
    strPS2 = "the sale"
    strPS3 = "the sale"
    strClient2 = "you"
    strCoholder = "your"
    strClient3 = "the Buyer's name"
    strPS4 = "sale"
    strExConf1 = "C Agreement (optional)" _
      & vbCrLf & "*****"
   Case optClient3
    strClient = "the Group"
    strPS = "the purchase"
    strPS1 = "the purchase"
    strPS2 = "the purchase"
    strPS3 = "the purchase"
    strClient2 = "the Group"
    strCoholder = txtOtherside.Value
    strClient3 = "your names"
    strPS4 = "purchase"
    strExConf1 = "E Agreement (optional)" _
      & vbCrLf & "******"
   Case optClient4
    strClient = "the Group"
    strPS = "the sale"
    strPS1 = "the sale"
    strPS2 = "the sale"
    strPS3 = "the sale"
    strClient2 = "the Group"
    strCoholder = "your"
    strClient3 = "the Buyer's names"
    strPS4 = "sale"
    strExConf1 = "C Agreement (optional)" _
      & vbCrLf & "*****"
   Case optGP1
    strGP = "and that there is a G Contract"
    strGP1 = "G Contract"
    strGP2 = "G Contract"
    strGP3 = "G Contract"
    strGP4Special = "**** G Contract"
   Case optGP2
    strGP = "and that there is a P Agreement"
    strGP1 = "P Agreement"
    strGP2 = "P Agreement"
    strGP3 = "P Agreement"
    strGP4Special = "****** P Agreement"
   End Select
   Application.ScreenUpdating = False
   With ActiveDocument
      .Bookmarks("RecipientName").Range.Text = txtRecipientName.Value
      .Bookmarks("PName").Range.Text = txtPName.Value
      .Bookmarks("PName1").Range.Text = txtPName.Value
      .Bookmarks("PName2").Range.Text = txtPName.Value
      .Bookmarks("PName3").Range.Text = txtPName.Value
      .Bookmarks("Ma").Range.Text = txtMa.Value
      .Bookmarks("Client").Range.Text = strClient
      .Bookmarks("PS").Range.Text = strPS
      .Bookmarks("Convo").Range.Text = strConvo
      .Bookmarks("SenderName").Range.Text = strFee
      .Bookmarks("PS1").Range.Text = strPS1
      .Bookmarks("PS2").Range.Text = strPS2
      .Bookmarks("Client2").Range.Text = strClient2
      .Bookmarks("GP").Range.Text = strGP
      .Bookmarks("Client3").Range.Text = txtOtherside.Value
      .Bookmarks("Coholder").Range.Text = strCoholder
      .Bookmarks("GP1").Range.Text = strGP1
      .Bookmarks("Client3").Range.Text = strClient3
      .Bookmarks("GP2").Range.Text = strGP2
      .Bookmarks("GP3").Range.Text = strGP3
      .Bookmarks("GP4Special").Range.Text = strGP4Special
      .Bookmarks("OtherF").Range.Text = strOtherF
      .Bookmarks("OtherF1").Range.Text = strOtherF1
      .Bookmarks("PS3").Range.Text = strPS3
      .Bookmarks("Rts").Range.Text = strRts
      .Bookmarks("Rts").Range.Text = strRts1
      .Bookmarks("OtherF2").Range.Text = strOtherF2
      .Bookmarks("PS4").Range.Text = strPS4
      .Bookmarks("ExConf1").Range.Text = strExConf1
   End With
   Application.ScreenUpdating = True
   Unload Me
End Sub

[westconn1]

optGS doesn't do its job at all unless OI only choose an option in that group before clicking OK

maybe because there is no code for optgs?

which bookmarks are not being populated?
do the values get assigned to the variables, then mot to the bookmarks or are the variables still empty?

[jarvster]

maybe because there is no code for optgs?

which bookmarks are not being populated?
do the values get assigned to the variables, then mot to the bookmarks or are the variables still empty?

Sorry my fault - I meant optGP. I haven't assigned default values so the options are all unchecked when I run the userform. If I fill all the txt fields and select an option in each group - it doesn't seem to put anything in the bookmarks for some options (optGS and a couple of others). If I just select an option for optGS and leave all other txt boxes and option groups unselected - it then works for optGS.

It's not just optGS either - happens on Exconf1 which is populated by the optClient1, 2, 3 and 4. It seems like it can't cope with multiple tasks for one option!

I haven't assigned coding for all the option groups on the userform yet but that shouldn't disrupt the ones I have coded shoud it?

In respect of your query - I am not certain I understand about the value/variable bit but I can confirm some bookmarks are not being filled. I can feeback with more specific info on which bookmarks if needed.

EDIT: Ok the problem is this - it appears only one option group will work at a time and the others are ignored. If I choose an option in optGP, an option in optClient and an option in optFee - only optFee will work.
If I choose just options in optGP and optClient - only optClient will work.
Accordingly the order of preference is optGP<optClient<optFee. None work together!

[westconn1]

sorry i did not read your code well enough

select case will only work for the first case true in each select
you need to have separate select case for each option button group

[jarvster]

Aha! Excellent advice - that sorted it. Now the options are working

A further query - in the fuller code I posted about, a have an option which inserts some text on two lines (I may expand to 3):

Case optFee1
    strFee = "DAVID" _
      & vbCrLf & "Advisor"

I want to bold the words DAVID. I did a record macro and found out that it used "Selection.Font.Bold = wdToggle" but I can't seem to fit the code either side of David without angering the debugger. How do I bold just a word on text being inserted. I even tried it without the "& vBCrLf" and tried "Selection.TypeParagraph" but it didn't work. Any ideas?

[westconn1]

you may need to find DAVID then set to bold after it is in the document
you can not make a string variable content (or part of) bold

or you may be able to format the bookmark, if that is where it is going