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
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.
"Those things we must learn to do, we must learn by doing" (or hope somebody else will take pity and help out )
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!
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
"Those things we must learn to do, we must learn by doing" (or hope somebody else will take pity and help out )
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.
"Those things we must learn to do, we must learn by doing" (or hope somebody else will take pity and help out )
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:
[RESOLVED] There's got to be a better way...
)
Reply With Quote
)
)