Update Cell in a Table with Value from User Form

gmayor · #6 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

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Allow Multiple Content Control same cell of form table	DeborahBartlett	Word	1	01-04-2014 11:06 AM
Trying to update a table cell with a value based on a drop down box entry	mkasem	Word VBA	2	09-29-2013 08:36 PM
Text Form Fields - Filling the table cell	simville02	Word Tables	1	01-31-2013 11:12 PM
Update Outlook but user path changed	Ossie1972	Outlook	1	12-08-2010 08:19 PM
Text Wrapping on Fixed Lines in a Form field/Table cell	okrmjr	Word Tables	0	10-30-2009 08:52 AM