VBA Excel Using an array element to reference a cell in a Word document's table

Question

I'm using an Excel userform to push data entered on the form into a Word Document. The Word document has several tables and the data from the form textbox controls needs to go into specific cells on the Word document. There are 15 checkboxes with 15 matching textboxes on the Excel form, so I figured the easiest way to handle it is with an array so I could loop through it.

Addressing the checkboxes (the Program array) isn't an issue because they're named "chkbox1-chkbox15" and there are matching Content Controls in the Word table tagged chkbox1-chkbox15, so that loop works properly.

The problem is with the textboxes' text. The cells the data needs to go in is kind of all over the place and I don't have the luxury of altering the table layout. So I figured I'd create a 15-element array that contains the row & column location of the target cell.

Here's the relevant part of my code:

Dim ImpactCells(15) As String

'Specify Form cell locations for specific programs

ImpactCells(0) = "4,2"      'Program 1
ImpactCells(1) = "5,2"      'Program 2
ImpactCells(2) = "6,2"      'Program 3
ImpactCells(3) = "4,4"      'Program 4
ImpactCells(4) = "5,4"      'Program 5
ImpactCells(5) = "6,4"      'Program 6
ImpactCells(6) = "7,2"      'Program 7
ImpactCells(7) = "10,2"     'Program 8
ImpactCells(8) = "11,2"     'Program 9
ImpactCells(9) = "12,2"     'Program 10
ImpactCells(10) = "13,2"    'Program 11
ImpactCells(11) = "10,4"    'Program 12
ImpactCells(12) = "11,4"    'Program 13
ImpactCells(13) = "12,4"    'Program 14
ImpactCells(14) = "13,4"    'Program 15

For i = 0 To 14                                                                 'Count of Programs Array
        If Programs(i) <> "" Then                                               'If array element is not blank
            Set cc = .SelectContentControlsByTag("Program" & i + 1).Item(1)     'Select the Content Control with the Program(array index) tag
            cc.Checked = True                                                   'Check it
        End If
        If ImpactCount(i) <> "" Then
            'Code to populate cells here
            .Tables(9).Cell(ImpactCells(i)).Range.Text = Me.Controls("txtImpact" & i + 1).Text
        End If
    Next

The line thats causing an error is:

.Tables(9).Cell(ImpactCells(i)).Range.Text = Me.Controls("txtImpact" & i + 1).Text

It says: Compile Error: Argument not optional and highlights the word cell. The code works when I put specific cell locations instead of ImpactCells(i).

Any help would be appreciated. Suggestions for a more efficient way of doing this would also be great, keeping in mind that I can't modify the way the Word table is laid out.

You are only providing 1 argument (#, # as a string) to Cell whereas it ask for 2 arguments, both of which are Long type. If you do not want to change too much of your code - You can try making an array (say splitArr) and assign to Split(ImpactCell(i),",") then use it like .Tables(9).Cell(splitArr(0),splitArr(1)).Range.Text — Raymond Wu
– Raymond Wu, Commented Aug 12, 2021 at 16:27

Tim Williams · Accepted Answer · 2021-08-12 16:24:54Z

1

Cell requires a row and a column argument (both numeric). You're passing a single String argument.

You could try something like this:

Dim arr
'...
'...
If ImpactCount(i) <> "" Then
    'Code to populate cells here
    arr = split(ImpactCells(i),",") 'convert to array
    .Tables(9).Cell(arr(0), arr(1)).Range.Text = _
                  Me.Controls("txtImpact" & i + 1).Text
End If

answered Aug 12, 2021 at 16:24

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Anthony Over a year ago

Would making changes to the ImpactCells array be more efficient? It seems strange to create the array only to split it farther down?

Raymond Wu Over a year ago

It's just 2 lines of code to resolve this issue, I think it's quite reasonable. You probably have to make ImpactCells into a 2D Long array if you want to change which doesn't seems to be more efficient @Anthony

Collectives™ on Stack Overflow

VBA Excel Using an array element to reference a cell in a Word document's table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related