Microsoft Office Forums - View Single Post - [Solved] Update Cell in a Table with Value from User Form

gmayor · 06-21-2016, 09:16 PM

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

Code:

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