[RESOLVED] There's got to be a better way...

[New2vba]

Unfortunately AIS4U the application must be created solely in Word/vba (not that I would know how to do it with VB or in a dbase anyway )

I am creating a series of quotations for equipment that is technical in nature. The items in the list will vary depending on what type of equipment has been chosen and what options are included with the equipment. Each quotation template employs a userform containg a series of textboxes, comboboxes and checkboxes.

To aid with price changes, I have created a price list containing strSomeItemPrice for each item so updating prices (may occur once or twice a year) can be done in the VBE. Changing of names is highly unlikely. To explain, take for example, a car - a windscreen is called a basic windscreen and no one is likely to change it anytime soon. The total number of options is also unlikely to increase anytime soon.

One possible direction (kindly shown to me by Dkenny) would be to use an array that would be populated with items that meet my conditions, i.e. enabled and false. However, I don't know how to assign the price to each item in the array.

Still clueless

[Killazzz]

hello,

I've been looking at your code and it could be made a lot more efficient.

'you define an array of all your prices and string variables
dim x as integer
Dim MainPriceControlArray(5, 2) As String
    MainPriceControlArray(1, 1) = "100.00" '    strProtectionCoversPrice = "100.00"
    MainPriceControlArray(2, 1) = "200.00" '    strChainShortenersPrice = "200.00"
    MainPriceControlArray(3, 1) = "300.00" '    strDetachTopBarPrice = "300.00"
    MainPriceControlArray(4, 1) = "400.00" '    strFireExtingExtPrice = "400.00"
    MainPriceControlArray(5, 1) = "500.00" '    strFireExtingIntPrice = "500.00"
    MainPriceControlArray(1, 2) = "strProtectionCovers" '    strProtectionCovers
    MainPriceControlArray(2, 2) = "strChainShorteners" '    strChainShorteners
    MainPriceControlArray(3, 2) = "strDetachTopBar" '    strDetachTopBar
    MainPriceControlArray(4, 2) = "strFireExtingExt" '    strFireExtingExt
    MainPriceControlArray(5, 2) = "strFireExtingInt" '    strFireExtingInt
'then you loop through your array and controls in the document
' you check each  control for it's anabled and value property and take the appropriate action
 
    For x = 1 To UBound(MainPriceControlArray)
        For Each ctl In doc.Controls 'doc is the document you're working in
            If ctl.Enabled = True And ctl.Value = False Then
                    doc.Controls(MainPriceControlArray(x, 2)) = vbCr & ctl.Caption & vbTab & "$" & vbTab & MainPriceControlArray(x, 1)
            End If
        Next ctl
    Next

I think this will get you on your way

[New2vba]

Thanks for the help Killazzz, although I can't get the code to work.

Just to clarify, all my controls are on a userform and when OK is pressed, my final list (amongst other things) is inserted into the document.

The insertion point for the list is a bookmark and it ends up like this:

Item1 £ 100.00
Item2 £ 200.00
Item3 £ 400.00
Item4 £ 900.00
Item5 £ 150.00
etc...

Any additional pointers would be most welcome.

[Killazzz]

Hello,

Can you post the doc ? That way I can alter your code and you can see how it can be done ?

kind regards,
Jason

[New2vba]

I've not yet tried the code in my actual document. Before implementing it I added it to a document on it's own to try and understand it a little better.

The code has been slightly modified from original because I could'nt get it to work, although it still doesn't work - I'm simply adding Array(5,2) to my document.

Any help would be appreciated.

[Killazzz]

Private Sub CommandButton1_Click()
 
'you define an array of all your prices and string variables
Dim A As Variant
Dim x As Integer
Dim chkMyCheck As Control
Dim MainPriceControlArray(5, 2) As String
 
    MainPriceControlArray(1, 1) = "100.00" '    strCheckBox1 = "100.00"
    MainPriceControlArray(2, 1) = "200.00" '    strCheckBox2 = "200.00"
    MainPriceControlArray(3, 1) = "300.00" '    strCheckBox3 = "300.00"
    MainPriceControlArray(4, 1) = "400.00" '    strCheckBox4= "400.00"
    MainPriceControlArray(5, 1) = "500.00" '    strCheckBox5 = "500.00"
    
    [B]MainPriceControlArray(1, 2) = "textbox1" '    strProtectionCovers[/B]
    MainPriceControlArray(2, 2) = "strCheckBox2" '    strChainShorteners
    MainPriceControlArray(3, 2) = "strCheckBox3" '    strDetachTopBar
    MainPriceControlArray(4, 2) = "strCheckBox4" '    strFireExtingExt
    MainPriceControlArray(5, 2) = "strCheckBox5" '    strFireExtingInt
    
'then you loop through your array and controls in the document
' you check each  control for it's anabled and value property and take the appropriate action
 
    For x = 1 To UBound(MainPriceControlArray)
        For Each chkMyCheck In Me.Controls
            If [B]LCase(Left(chkMyCheck.Name, 3)) = "che"[/B] Then
                If chkMyCheck.Enabled = True And chkMyCheck.Value = False Then
                    [B]Me.Controls(MainPriceControlArray(x, 2)) [/B] = vbCr & chkMyCheck.Caption & vbTab & "$" & vbTab & MainPriceControlArray(x, 1)
                Exit For
                End If
            End If
        Next chkMyCheck
    Next
    
 
 
    With ActiveDocument
    .Bookmarks("Test").Range.Text = MainPriceControlArray(5, 2)
    End With
End Sub

the bold sections I've changed

[New2vba]

Killazzz,

I don't know if it's something I'm doing, but this doesn't seem to work. Unless I check checkbox 5, I simply get an error.

And if I do check checkbox5, only that value appears at my bookmark.

What I am trying to do is generate a list at my bookmark, which contains all items that are enabled yet false. For example, if checkbox1 and checkbox2 were the only boxes left that were both enabled and unchecked, then I would get:

Checkbox1.caption "£" Checkbox1 item price
Checkbox2.caption "£" Checkbox2 item price

And there would be tabs in between the caption, "£" and the price (the tab positions are built into the style in the word document).

Any ideas how I might resolve this?