Update Cell in a Table with Value from User Form

[wlcdo2]

I have a table within Word 2013 document. I want the user to select any cell within that table to be the 'recipient' of a value from a User Form. The user then opens the User Form that displays various choices by way of OptionButtons i.e. they may select from values such as A0, A3, A5, C5. Once they have made their choice, I have an "Update" button that transfers the value of the OptionButton to the cell in the table. I tried by inserting a Bookmark into the table, like so:

Private Sub btnUpdateP_Click()

Dim PeopleChoice As String, Tbl As Table, Rng As Range

If Me.optA0 Then
    PeopleChoice = "A0"
ElseIf Me.optA3 Then
    PeopleChoice = "A3"
ElseIf Me.optA5 Then
    PeopleChoice = "A5"
Else
    PeopleChoice = "C5"
End If
    Selection.Bookmarks.Add ("bmPeople")
    ActiveDocument.Bookmarks("bmPeople").Select
    Selection.Text = PeopleChoice

When I run through the code using the debugger, it works fine, but when I run the code for real (using an ActiveX button), the Bookmark is inserted at the location of the button that calls the UserForm, instead of the Table Cell.

Can anyone assist me with this one please?

Thank you.

Corin.

[macropod]

Originally Posted by wlcdo2

When I run through the code using the debugger, it works fine, but when I run the code for real (using an ActiveX button), the Bookmark is inserted at the location of the button that calls the UserForm, instead of the Table Cell.

That's because clicking on the ActiveX button changes the selection. You might do better to have your userform solicit the cell address to work with. Do note that Word VBA doesn't use A1 referencing so, if you solicit that, your code will need to transform it into rowindex & columnindex referencing.

[wlcdo2]

Thanks for the reply Paul. I'm still a little stuck on the correct coding. The Row and column Index I understand, but I'm still stuck on the rest of the VBA to get the user form to solicit the cell address. Would you be able to share some sample code at all to help me in the right direction?
Thx.

[macropod]

The code depends on what you want the user to input - something like 'A10' in a single textbox or '1' for the column in one textbox and '10' for the column in another. Which approach are you using?

[wlcdo2]

Hi Paul,
In the context of the document, what I've got is a Work Method Statement / Job Safety Analysis whereby each line of the table is a chronological step of how an activity or project will be completed safely. Part of the work analysis is determining hazards for each step by referring to a Risk Assessment Matrix. I'm hoping to simplify completion of the WMS / JSA whereby the user displays the Risk Assessment Matrix (which was my UserForm) and simply clicks the relevant hazard rating from that UserForm; in this case I was utilising Option Buttons for each type of hazard rating i.e. A0, A3, C5.
The result from the selected OptionButton would then populate the cell of the table they had clicked in to.

Does this help describe what I'm hoping to achieve? Would some additional screen shots help perhaps?

Thank you.

[gmayor]

If instead of using an ActiveX button, which, as Paul says, becomes the selection when you click it, you were to use a ribbon button to call the userform, you wouldn't have this problem, as the selection would remain in the table cell.

With an ActiveX button you would have to add the means of indicating to the macro which cell to process. This could, for example take the form of a pair of combo-boxes to indicate the row and column respectively. This works as long as there are no split or merged cells.

You can then refer to the selection in these boxes and create a range to the corresponding cell. e.g. as follows

Option Explicit
Private Sub UserForm_Initialize()
Dim oTable As Table
Dim i As Integer
    Set oTable = ActiveDocument.Tables(1)
    With Me.ComboRow
        For i = 1 To oTable.Rows.Count
            .AddItem i
        Next i
        .ListIndex = 0
    End With
    With Me.ComboColumn
        For i = 1 To oTable.Columns.Count
            .AddItem i
        Next i
        .ListIndex = 0
    End With
End Sub


Private Sub btnUpdateP_Click()
Dim oTable As Table
Dim oCell As Range
Dim PeopleChoice As String
Dim iRow As Integer, iCol As Integer
    Set oTable = ActiveDocument.Tables(1)
    iRow = Me.ComboRow.ListIndex + 1
    iCol = Me.ComboColumn.ListIndex + 1
    
    Select Case True
        Case Is = Me.optA0.Value: PeopleChoice = "A0"
        Case Is = Me.optA3.Value: PeopleChoice = "A3"
        Case Is = Me.optA5.Value: PeopleChoice = "A5"
        Case Else: PeopleChoice = "C5"
    End Select
    Me.Hide
    Set oCell = oTable.Rows(iRow).Cells(iCol).Range
    oCell.End = oCell.End - 1
    oCell.Text = PeopleChoice
    Unload Me
End Sub

[macropod]

Another option would be to use a modeless userform, so the cell could be selected after the userform is activated.

[wlcdo2]

Thanks so much Paul and Graham. I ended up using a combination of the modeless userform and making the TakeFocusOnClick property of the form call button to False which has solved my query.
Thanks again for all your valuable feedback and direction; much appreciated.