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

Hi,

I am currently generating a list inside a Word 2003 document based on choices made in a userform. Each item has a price so I have set up strings for each one:

VB Code:

Dim strProtectionCoversPrice As String, strChainShortenersPrice, strDetachTopBarPrice As String, _
        strFireExtingExtPrice As String, strFireExtingIntPrice As String, strFirstAidKitPrice As String, strFlipUpBarPrice As String, _
        strHazChemPrice As String, strHighTippingPrice As String, strHoldToRunPrice As String, strInCabCtrlsPrice As String, _
        strLoadCellHangerPrice As String, strLoadCellSubEquipPrice As String, strAddPrinterPrice As String, _
        strNetBoxBasic620Price As String, strNetBoxBasic880Price As String, strNetBoxDropDownPrice As String, _
        strPressureFilterPrice As String, strRearLightProtectionPrice As String, strRevBuzzerPrice As String, _
        strRevCameraPrice As String, strBeaconPrice As String, strHyTowerPrice As String, strWingSPRearBogiePrice As String, _
        strWingSRearBogiePrice As String, strWingPRearAxlePrice As String, strWingPRearBogiePrice As String, _
        strWingPRearSteerPrice As String, strToolboxPrice As String, strWinchPrice As String, _
        strWorkLightSinglePrice As String, strWorkLightTwinPrice As String, strFinishPaintEquipPrice As String
 
    strProtectionCoversPrice = "100.00"
    strChainShortenersPrice = "200.00"
    strDetachTopBarPrice = "300.00"
    strFireExtingExtPrice = "400.00"
    strFireExtingIntPrice = "500.00"
    strFirstAidKitPrice = "600.00"
    strFlipUpBarPrice = "700.00"
    strHazChemPrice = "800.00"
    strHighTippingPrice = "900.00"
    strHoldToRunPrice = "50.00"
    strInCabCtrlsPrice = "1000.00"
    strLoadCellHangerPrice = "2000.00"
    strLoadCellSubEquipPrice = "3000.00"
    strAddPrinterPrice = "300.00"
    strNetBoxBasic620Price = "100.00"
    strNetBoxBasic880Price = "110.00"
    strNetBoxDropDownPrice = "175.00"
    strPressureFilterPrice = "150.00"
    strRearLightProtectionPrice = "100.00"
    strRevBuzzerPrice = "50.00"
    strRevCameraPrice = "350.00"
    strBeaconPrice = "50.00"
    strHyTowerPrice = "1500.00"
    strWingSPRearBogiePrice = "250.00"
    strWingSRearBogiePrice = "350.00"
    strWingPRearAxlePrice = "100.00"
    strWingPRearBogiePrice = "200.00"
    strWingPRearSteerPrice = "125.00"
    strToolboxPrice = "95.00"
    strWinchPrice = "500.00"
    strWorkLightSinglePrice = "50.00"
    strWorkLightTwinPrice = "75.00"
    strFinishPaintEquipPrice = "900.00"

The contents of the list will vary depending on choices made in a userform. If the items checkbox is not selected, but is enabled, then the item will go in the list:

VB Code:

Dim strProtectionCovers As String, strChainShorteners, strDetachTopBar As String, _
        strFireExtingExt As String, strFireExtingInt As String, strFirstAidKit As String, strFlipUpBar As String, _
        strHazChem As String, strHighTipping As String, strHoldToRun As String, strInCabCtrls As String, _
        strLoadCellHanger As String, strLoadCellSubEquip As String, strAddPrinter As String, _
        strNetBoxBasic620 As String, strNetBoxBasic880 As String, strNetBoxDropDown As String, _
        strPressureFilter As String, strRearLightProtection As String, strRevBuzzer As String, _
        strRevCamera As String, strBeacon As String, strHyTower As String, strWingSPRearBogie As String, _
        strWingSRearBogie As String, strWingPRearAxle As String, strWingPRearBogie As String, _
        strWingPRearSteer As String, strToolbox As String, strWinch As String, strWorkLightSingle As String, _
        strWorkLightTwin As String, strFinishPaintEquip As String, strAvailableOptions As String
        
    If chkAddProtectionCovers.Enabled = True And chkAddProtectionCovers.Value = False Then
        strProtectionCovers = vbCr & chkAddProtectionCovers.Caption & vbTab & "£" & vbTab & strProtectionCoversPrice
    End If
    
    If chkAddChainShorteners.Enabled = True And chkAddChainShorteners.Value = False Then
        strChainShorteners = vbCr & chkAddChainShorteners.Caption & vbTab & "£" & vbTab & strChainShortenersPrice
    End If
    
    If chkAddDetachTopBar.Enabled = True And chkAddDetachTopBar.Value = False Then
        strDetachTopBar = vbCr & chkAddDetachTopBar.Caption & vbTab & "£" & vbTab & strDetachTopBarPrice
    End If
    
    If chkAddFireExtingExt.Enabled = True And chkAddFireExtingExt.Value = False Then
        strFireExtingExt = vbCr & chkAddFireExtingExt.Caption & vbTab & "£" & vbTab & strFireExtingExtPrice
    End If
    
    If chkAddFireExtingInt.Enabled = True And chkAddFireExtingInt.Value = False Then
        strFireExtingInt = vbCr & chkAddFireExtingInt.Caption & vbTab & "£" & vbTab & strFireExtingIntPrice
    End If
    
    If chkAddFirstAidKit.Enabled = True And chkAddFirstAidKit.Value = False Then
        strFirstAidKit = vbCr & chkAddFirstAidKit.Caption & vbTab & "£" & vbTab & strFirstAidKitPrice
    End If
    
    If chkAddFlipUpBar.Enabled = True And chkAddFlipUpBar.Value = False Then
        strFlipUpBar = vbCr & chkAddFlipUpBar.Caption & vbTab & "£" & vbTab & strFlipUpBarPrice
    End If
    
    If chkAddHazChem.Enabled = True And chkAddHazChem.Value = False Then
        strHazChem = vbCr & chkAddHazChem.Caption & vbTab & "£" & vbTab & strHazChemPrice
    End If
    
    If chkAddHighTipping.Enabled = True And chkAddHighTipping.Value = False Then
        strHighTipping = vbCr & chkAddHighTipping.Caption & vbTab & "£" & vbTab & strHighTippingPrice
    End If
    
    If chkAddHoldToRun.Enabled = True And chkAddHoldToRun.Value = False Then
        strHoldToRun = vbCr & chkAddHoldToRun.Caption & vbTab & "£" & vbTab & strHoldToRunPrice
    End If
    
    If chkAddInCabCtrls.Enabled = True And chkAddInCabCtrls.Value = False Then
        strInCabCtrls = vbCr & chkAddInCabCtrls.Caption & vbTab & "£" & vbTab & strInCabCtrlsPrice
    End If 
and so on.....

The list is inserted at a bookmark using another bookmark, that is all the choices, although any null strings will be ignored.

VB Code:

strAvailableOptions = strProtectionCovers & strChainShorteners & strDetachTopBar & _
        strFireExtingExt & strFireExtingInt & strFirstAidKit & strFlipUpBar & strHazChem & _
        strHighTipping & strHoldToRun & strInCabCtrls & strLoadCellHanger & strLoadCellSubEquip & _
         strAddPrinter & strNetBoxBasic620 & strNetBoxBasic880 & strNetBoxDropDown & _
        strPressureFilter & strRearLightProtection & strRevBuzzer & strRevCamera & strBeacon & _
        strHyTower & strWingSPRearBogie & strWingSRearBogie & strWingPRearAxle & strWingPRearBogie & _
        strWingPRearSteer & strToolbox & strWinch & strWorkLightSingle & strWorkLightTwin & strFinishPaintEquip

The method works, but this seems a very long winded and disorganised way of doing things. With a previous and similar problem DKenny kindly provided a solution "building a table on the fly." However, in this instance I have to assign unique price to each item as well so can't see how I can apply that solution.

Any suggestions or pointers would be most welcome. :thumb:

New2vba:

You are my new hero!

I can't imagine anyone having the persistence and perseverance to write this code.

I don't really know what you are doing or trying to do, but it sure looks to me like there certainly MUST be a better way.

I don't know how you will ever maintain your code and what will you do about price changes or name changes.

You have a worklightsingle and a worklighttwin. What happens when someone decides to change the name of worklighttwin to worklightdouble?

Isn't it time you moved your data into some kind of database (Access, SQL, even an old copy of dBase III) and created some kind of front end application in Visual Basic 6.0 or .Net.

I would think that even writing your complete application in Access would be much better than what you are currently doing.

Like I said, I don't really know what you are doing or tyring to do, but you are my new HERO!

Good Luck

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 :confused: )

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 :sick:

hello,

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

VB Code:

'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

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. :)

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

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.

VB Code:

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

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?

This should do the trick

Works perfectly Killazz (although it'll probably take a while for me to fully appreciate what it's doing :blush: )

Thanks a lot for your help. :thumb: