VBA & Macro Problems

Open a Workbook and Copy UserForm Values with an Excel ComboBox

Chanuka GeekiyanageChanuka Geekiyanage Nov 14, 2026 868 views

Question details

Use an Excel UserForm ComboBox to select and open a workbook, then transfer TextBox values to specified cells.

Open a Workbook and Copy UserForm Values with an Excel ComboBox
Product
Microsoft Excel
Device & OS
not provided
Scenario
A UserForm lets the user select a workbook and enter values to be written to it.
Observed behavior
The workbook path contains an extra space, and the ComboBox is referenced incorrectly.
Before you start

Confirm the folder path and make sure the ComboBox contains each workbook's full file name, including its extension. Check the names of the UserForm controls and the destination cells before updating the code.

Solution 1Recommended

Use the ComboBox value to open the workbook and write the TextBox values

Use this approach when the ComboBox contains a file name in a known folder and a UserForm button starts the transfer.

The example uses Me.ComboBox1.Value to read the selected file name without adding a space before it. It writes TextBox1 and TextBox2 to cells A1 and A2 on the first worksheet; replace those controls and cells with your own.

1
Open the UserForm code

In Excel, press Alt+F11 to open the VBA editor. In Project Explorer, open your UserForm and double-click the button that should perform the transfer.

2
Add the button code

Replace the button's Click procedure with the following example, adjusting CommandButton1 if your button has a different name: Private Sub CommandButton1_Click() Dim filePath As String Dim targetWb As Workbook If Trim(Me.ComboBox1.Value) = "" Then MsgBox "Select a workbook first." Exit Sub End If filePath = "F:\Documents\Permit Update Build\Permit Forms\" & Me.ComboBox1.Value If Dir(filePath) = "" Then MsgBox "Workbook not found: " & filePath Exit Sub End If Set targetWb = Workbooks.Open(filePath) With targetWb.Worksheets(1) .Range("A1").Value = Me.TextBox1.Value .Range("A2").Value = Me.TextBox2.Value End With targetWb.Save End Sub

3
Adjust the path and destinations

In the code, replace the example folder with your actual folder, retaining the final backslash. Change Worksheets(1), the cell addresses, and the TextBox names to match your destination.

4
Test the transfer

Save the workbook containing the UserForm as an Excel Macro-Enabled Workbook (.xlsm). Enable macros if prompted and if you trust the file, open the form, select a workbook, enter values, and click the transfer button. Check the destination cells in the opened workbook.

Use the ComboBox value to open the workbook and write the TextBox values
Saving the destination: The example saves the selected workbook after writing the values. The workbook must be writable; remove targetWb.Save if you prefer to review and save the changes manually.
Free Microsoft Office alternative

Edit Spreadsheet Workbooks with WPS Office

WPS Office is a free, lightweight alternative for opening and editing spreadsheets through a familiar interface. Its Excel format support helps you move everyday workbook tasks to WPS, but it does not fix Excel VBA code, and UserForm compatibility should be tested separately.

Open and edit spreadsheet cells in a familiar interfaceWork with Microsoft Excel formats such as XLSXUse a free, lightweight office suite for everyday documentsMove routine spreadsheet editing from Microsoft Office to WPS
microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel say the selected workbook cannot be found?

Check the folder path and the selected ComboBox value. The folder should end with a backslash, and the value should include the complete file name and extension, without an extra space between the folder and file name.

How do I write values to a named worksheet instead of the first one?

Replace targetWb.Worksheets(1) with a named reference such as targetWb.Worksheets("Sheet1"). Use the exact worksheet name shown on the destination workbook's tab.

Does the code save the destination workbook?

Yes. The targetWb.Save line saves the workbook after the values are written. Remove that line if you want to review the changes and save manually.

What format should I use for the workbook containing the UserForm?

In Excel, choose File > Save As and select Excel Macro-Enabled Workbook (.xlsm) to retain the VBA code and UserForm.