[RESOLVED] Help with arrays - Almost there!

Back again with cap in hand... :)

Below is code kindly provided by killazzz. Attached is the document that contains the code.

Within the document is a userform that contains a series of comboboxes, some of which are disabled. Each combobox represents an item that has a unique price.

When the code is executed, any comboboxes that are enabled, but false will be added to the document in the form:

checkbox.caption <TAB SPACE> £ <TAB SPACE> Price

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) = "150.00"       'Chain Shorteners
    MainPriceControlArray(2, 1) = "100.00"        'Protection Covers
    MainPriceControlArray(3, 1) = "2000.00" '     'Load Cells
    MainPriceControlArray(4, 1) = "400.00" '    strCheckBox4= "400.00"
    MainPriceControlArray(5, 1) = "500.00" '    strCheckBox5 = "500.00"
    
    MainPriceControlArray(1, 2) = "chkChainShorteners"
    MainPriceControlArray(2, 2) = "chkProtectionCovers"
    MainPriceControlArray(3, 2) = "chkLoadCells"
    MainPriceControlArray(4, 2) = "strCheckBox4" '    strFireExtingExt
    MainPriceControlArray(5, 2) = "chktest124578986532" '    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 LCase(Left(chkMyCheck.Name, 3)) = "chk" And InStr(1, MainPriceControlArray(x, 2), chkMyCheck.Name, vbTextCompare) <> 0 Then
                If chkMyCheck.Enabled = True And chkMyCheck.Value = False Then
                    ActiveDocument.Bookmarks("Test").Range.Text = vbCr & chkMyCheck.Caption & vbTab & "£" & vbTab & MainPriceControlArray(x, 1)
                Exit For
                End If
            End If
        Next chkMyCheck
    Next
    
End Sub

I am now attempting to alter the code slightly so that the items to be added to the document are done so using a table.

What I have attempted to do is create another array that has 3 columns and the number of rows is equal to the number of items to be added. I than want to create a table of equivalent dimensions and add that to the document.

For example, if there were 2 items to be added to the document (in reality the list is much larger), I would get a 2 row, 3 column table, which would contain the following items.

(1,1) = checkbox1.caption
(1, 2) = "£"
(1, 3) = checkbox1 price

(2,1) = checkbox2.caption
(2,2) = "£"
(2, 3) = checkbox2 price

I also want to set column sizes and word alignment, but can probably work that out later. I have made numerous attempts at the above, but am having real difficulty. I have referenced arrays in the help files in vba, but can't seem to apply the information correctly (obviously it is the help files at fault :rolleyes: )

Any help appreciated. :)

Here you go

Thanks again killazzz, but I am afraid this is not exactly what I meant.

I want to add my values to a table, which I will then insert at a bookmark. I have some code from DKenny (seems all the decent code I have was provided by one of the esteemed members of vbforums :) ) for creating a table on the fly, but am having trouble adapting it.

I was guesing that first an additional array would be needed to store the values for entry into the table, but maybe this isn't the case?

I know that each cell in column 2 will always contain "£" and the table will always have 3 columns.

To clarify, if say checkboxes 1, 3, and 5 are enabled and false, then the table would be 3 rows by 3 columns and would contain:

(1,1) = Item 1 caption
(1,2) = "£"
(1,3) = Item 1 price

(2,1) = Item 3 caption
(2,2) = "£"
(2,3) = Item 3 price

(3,1) = Item 5 caption
(3,2) = "£"
(3,3) = Item 5 price

I also wanted to right align the entries in column 2 and 3 and set particular column widths.

Any help or even pointers in the right direction would be appreciated.

Here's an "attempt" at achieving the above, but I get error 9 at Redim Preserve.

VB Code:

Option Explicit
 
Private Sub CommandButton1_Click()
 
'you define an array of all your prices and string variables
Dim x As Integer
Dim AddOption() As String
Dim chkMyCheck As Control
Dim MainPriceControlArray(1 To 5, 1 To 2) As String
 
Dim myRange As Range
Dim tblMyTable As Table
Dim nItem As Long
Dim nRowCount As Long
 
ReDim AddOption(1 To 1, 1 To 3) As String
 
    MainPriceControlArray(1, 1) = "chkChainShorteners"
    MainPriceControlArray(2, 1) = "chkProtectionCovers"
    MainPriceControlArray(3, 1) = "chkLoadCells"
    MainPriceControlArray(4, 1) = "strCheckBox4"
    MainPriceControlArray(5, 1) = "chktest"
    
    MainPriceControlArray(1, 2) = "150.00" '        Chain  Shorteners
    MainPriceControlArray(2, 2) = "100.00" '        Protection Covers
    MainPriceControlArray(3, 2) = "2000.00" '       Load Cells
    MainPriceControlArray(4, 2) = "400.00" '        CheckBox4
    MainPriceControlArray(5, 2) = "500.00" '        chktest
    
'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 = LBound(MainPriceControlArray, 1) To UBound(MainPriceControlArray, 1)
        For Each chkMyCheck In Me.Controls
            If LCase(Left(chkMyCheck.Name, 3)) = "chk" And MainPriceControlArray(x, 1) = chkMyCheck.Name Then
                If chkMyCheck.Enabled = True And chkMyCheck.Value = False Then
                        AddOption(UBound(AddOption), 1) = Me.Controls(MainPriceControlArray(x, 1)).Caption
                        AddOption(UBound(AddOption), 2) = "£"
                        AddOption(UBound(AddOption), 3) = MainPriceControlArray(x, 2)
                        ReDim Preserve AddOption(UBound(AddOption, 1) + 1) As String
                End If
            End If
        Next chkMyCheck
    Next
    
  nRowCount = UBound(AddOption, 1)
    
    Set myRange = ActiveDocument.Bookmarks("Test").Range
        If nRowCount > 0 Then
            Set tblMyTable = ActiveDocument.Tables.Add(Range:=myRange, NumRows:=nRowCount, NumColumns:=3)
        End If
    
UserForm1.Hide
    
End Sub

Can anyone please tell me what I'm doing wrong?

EDIT:

I've since discovered that you can only change the last dimension of an array when using Preserve, as I'm sure many people already know! :blush:

I have now got some working code that uses 2 single dimension arrays (don't know if this is the best thing to do, but it works! :)

If there are any critiques out there, please check the code in the attached document and let me know where I can improve. :wave: