[RESOLVED] Help with arrays - Almost there!

[New2vba]

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

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 )

Any help appreciated.

[Killazzz]

Here you go

[New2vba]

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.

[New2vba]

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

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!

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.