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

[AIS4U]

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

[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