Microsoft Office Forums - View Single Post - Here's my solution for using form to change excel from word

grNadpa · 04-03-2022, 07:25 AM

Perhaps this is obvious to those familiar with Word VBA. But this example would have saved me hours, if not days, of frustration.

Rather than build an entire word VBA subsystem, I opted to render a roster of vendor and client names and addresses as tabs in an excel spreadsheet to be brought into my word template.

It turns out that my wife, who would be using this application, is not comfortable with using Excel data features such as insert row and sort. So I thought to create a form in Excel to do what we old mainframe programmers called "CRUD" (Create, Retrieve, Update, Delete) operations.

It worked fine -- in Excel as Roster.xlsm (note module enabled).

But when I brought the document into Word, it put the form in never-never land -- well not exactly, but not intuitively accessible either.

What worked was obvious in retrospect, but not intuitive either. Here it is.
First, define and load as an Excel .xlsx file (withOUT any forms or macros)

Code:

Function OpenAWorkbook(ByVal wkFilename As String) As Boolean
Dim xlApp As Object
' Dim xlBook As Object  'moved to Declarations

    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If Err Then
        Set xlApp = CreateObject("Excel.Application")
    End If
    On Error GoTo lblFailed
    
    Set xlBook = xlApp.Workbooks.Open(FileName:=wordPath + wkFilename)
    
    Load UserFormMaintainRoster
    UserFormMaintainRoster.Show
    xlApp.Visible = True

lbl_Exit:
    Set xlApp = Nothing
    Set xlBook = Nothing
    OpenAWorkbook = True
    Exit Function
    
lblFailed:
    Set xlApp = Nothing
    Set xlBook = Nothing
    OpenAWorkbook = False
    Exit Function
End Function

Then I created the UserFormMaintainRoster form in Word. Here are a couple of snippets from the code behind the form (The comboBoxNames contains names from a sheet in the Excel Workbook)

Code:

Sub LoadComboBox()
'Based on https://stackoverflow.com/questions/29565846/how-to-populate-a-combobox
    Dim N As Long
    With Sheets(sheetName)
        N = .Cells(Rows.Count, 1).End(xlUp).Row
    End With

    With ComboBoxNames
        .Clear
        For sheetRow = 2 To N               'skip title row
            .AddItem Sheets(sheetName).Cells(sheetRow, 1).Value
        Next sheetRow
    End With
End Sub

And, to update a row in the excel sheet

Code:

Sub DoUpdate()
    With Sheets(sheetName)
        .Cells(sheetRow, 1).Value = TextBoxName.Text
        .Cells(sheetRow, 2).Value = TextBoxAddress.Text
        .Cells(sheetRow, 3).Value = TextBoxCity.Text
        If OptionButtonActive.Value = True Then
            .Cells(sheetRow, 4).Value = isActive
        Else
            .Cells(sheetRow, 4).Value = isInactive
        End If
    End With
End Sub

Thoughts, suggestions, enhancements welcome.