help with listbox

[philipjlane]

Hi, this is my first post here so thanks in advance for any/all replies.

I'm relatively new to VBA so please humor me if i'm being a bit thick!

I have written a user form to populate document variables on a Word template.

I have a variable that needs to be filled with a string determined from a multi select listbox. I also need to modify an integer in the string depending on the selected itemin the listbox.

So far my code will alter the integer, but it applies it to all strings rather than just those for which it is relevant.

code:
'Translation Pricing
Dim strTranslation As String
Dim SourceWC As Long
Dim TargetWC As Long
Dim GrowthWC As Double
Dim j As Integer
SourceWC = txtSourceWC.Value
strTranslation = vbNewLine & "Translation."
For j = 0 To lstbLangTo.ListCount - 1
Select Case lstbLangTo.ListIndex
Case Is = "1"
GrowthWC = 1.1
Case Is = "2"
GrowthWC = 1.5
Case Else
GrowthWC = 1
End Select
TargetWC = SourceWC * GrowthWC
If lstbLangTo.Selected(j) Then
strTranslation = strTranslation & vbNewLine & lstbLangTo.List(j) & ". " & TargetWC & " words at £" & " per '000 words = £"
End If
Next j
If chkTranslation = True Then
With ActiveDocument
.Variables("varTranslation").Value = strTranslation
End With
Else
With ActiveDocument
.Variables("varTranslation").Value = ""
End With
End If
'End Traslation Pricing

Any help would be greatly appreciated.

Phil

[Tinbendr]

but it applies it to all strings rather than just those for which it is relevant. In what way? Where? What is the end result supposed to look like?
You code is sparsely commented. Can you upload an example? What version are you using?

[philipjlane]

Tinbendr, thanks for getting back to me.

Im using office 2003.

I would like the output to read something like this:

Translation.
Arabic. 1000 words at £ per '000 words
French. 1200 words at £ per '000 words

Currently the code returns something like this:

Translation.
Arabic. 1000 words at per '000 words
French. 1000 words at £ per '000 words

further to solving this problem i would also like it to change the "£" value depending on which language is selected.

The plan is for the string to display the language selected followed by the number of words for that language "TargetWC". Where TargetWC is determined as the product of SourceWC multiplied by the selected language's GrowthWC.

The code i've written doesn't cover all the cases as I wanted to get it working first. I've never been great at explaining things but hopefully you should be able to see where I'm coming from now.

Again thanks for any responses.
Phil

[fumei]

This is a complicated userform. I am going to have to really look at it, and i am not able to do so for a bit.

[fumei]

BTW: I can NOT actually use the userform, as it is so large. The commandbuttons are not accessible to me. I have to force it closed with the "X".

In other words, I can NOT click the Clear, OK or Cancel buttons when the userform is open. The best I can do to try the code is manually execute the OK command from within the VBE.

[fumei]

Some initial comments while I slug away at this...

1. May I ask why the heavy use of DOCVARIABLES?

2. I would strongly strongly strongly recommend you use Option Explicit in your code modules. Start doing this immediately.

3. I would also strongly recommend you break up the coding into more manageable chunks, and call the chunks as required. It is way too long.

4. two optionbuttons together are mutually exclusive, therefore:
Dim strReturn As String
If optReturn1 = True Then strReturn = "Email"
If optReturn2 = True Then strReturn = "FTP"

' which is used for...
.Variables("varReturn").Value = strReturn
can be written as:

Dim strReturn As String
If optReturn1 = True Then
strReturn = "Email"
Else
strReturn = "FTP"
End If
Why? Because if optReturn1 is False, then optReturn2 must be true.

As it stands, if:
If optReturn1 = True Then strReturn = "Email"

is indeed true, and strReturn is given "Email", the instruction:

If optReturn2 = True Then strReturn = "FTP"

is still executed anyway. They are two separate logic instructions, and therefore there is NO connection between them. However, because the objects are two optionbuttons, there IS a connection between them. You are just not using that connection.

Anyway, because of the sizing issue it is difficult for me to test it. Working on it.

[philipjlane]

Hi fumei thanks for your help so far, this is the first project of this size I have attempted, hence the rather sloppy layout. I'm entirely self educated on vba so haven't picked up any best practices along the way.

I also forget that not everyone uses 24" lcds as we do. So apologies for the size of the form.

Can you suggest a better alternative to the doc variables, I attempted the form with bookmarks but as they can not be repeated it became frustrating.

At the moment I my connection is down so am using my phone to view your responses. I'll be have a go at tidying the code up a little and re post.

Regards
Phil

[Tinbendr]

I would like the output to read something like this:

Translation.
Arabic. 1000 words at £ per '000 words
French. 1200 words at £ per '000 words

Currently the code returns something like this:

Translation.
Arabic. 1000 words at per '000 words
French. 1000 words at £ per '000 words
Your current example doc won't produce this output since your multipliers are only 1.1 and 1.5. So 1.1 * 1000 = 1100, and 1500 respectively.

This is wrong. It needs to be J.
'Translation Pricing
Select Case lstbLangTo.ListIndex 'Change to:
'Translation Pricing
Select Case J + 1 The List of zero based, so you have to add one to it.

I would also move the Select case inside the Test for Selected. As it is, the multiplier is always changing through he entire list, but you only need it to change if selected. Not a big deal, but makes for 'code sense'.


further to solving this problem i would also like it to change the "£" value depending on which language is selected.You could build the costs into the userform, but I would create a separate pricing table in another doc and import that data upon starting the Quote. That way, the pricing can by changed outside the userform instead of editting code over and over. But we can get to that later.

Sorry, out of time. Will try more this evening.

[fumei]

There are many ways to improve this. Your Clearing procedure is too complicated.


Can you suggest a better alternative to the doc variables, I attempted the form with bookmarks but as they can not be repeated it became frustrating.

Ah, the infamous "can not be repeated" bookmark thing, This has been the bane of many a starting VBA-er.

Bookmarks CAN be repeated. They can have content altered freely. Here is how.
Sub FillBM(strBM As String, strText As String)
Dim r As Range
Set r = ActiveDocument.Bookmarks(strBM).Range
r.Text = strText
ActiveDocument.Bookmarks.Add Name:=strBM, Range:=r
End Sub
You use it like this. Instead of:
With ActiveDocument
.Variables("varTranslation").Value = strTranslation
End With
you would use:
Call FillBm("varTranslation", strTranslation)
which fills the bookmark varTranslation with the text strTranslation. This can be repeated as many times as you like.

Call FillBm("varTranslation", "")
makes it blank - "". Executing Call FillBm("varTranslation", "Yadda blah blah") would put Yadda blah blah into the same bookmark.

There is no limits to what you put there, or how many times.