Link data to a combo box and update underlying data in Excel 2003/XP/2000/97

Question: In Excel 2003/XP/2000/97, I want to be able to use a combo box (on another sheet) to display data from Sheet1 and then be able to edit that data and save the changes back to Sheet1. How can I do this?

Answer: This can be done with macro code.

Let's take a look at an example.

In our spreadsheet, our data is on Sheet1.

On Sheet2, we have our combo box which is linked to the data on Sheet1. When the user selects a customer in the combo box, the address and phone number will be automatically populated. The population of this data is done by a macro called PopulateData (which is the macro assigned to the combo box).

A user can then update the data on this sheet. In our example, we've changed Betty's address from 454 11th to 78 Granite St.

Next, we click on the Save Changes button. This calls a macro called SaveChanges.

The macro will update the corresponding data on Sheet1 and display the message above when it has completed.

Now if we go to Sheet1, we can see that the address for Betty has been updated.

You can press Alt-F11 to view the VBA code. Select the module called Module1 in the left window.

Macro Code

The macro code looks like this:

The macro code for the Save Changes button:

Sub SaveChanges()

'Update data on Sheet1 based on changes made to data on Sheet2

Dim LProject As Integer

Dim LAddress As String
Dim LPhoneNbr As String

Dim LRow As Long
Dim LFound As Boolean

'Retrieve project number number
LProject = Range("H3").Value

'Retrieve new address and phone number information
LAddress = Range("E5").Value
LPhoneNbr = Range("H8").Value

'Move to Sheet1 to save the changes
Sheets("Sheet1").Select

LFound = False

LRow = 2

Do While LFound = False

'Found matching project, now update address and phone number information

If Range("A" & LRow).Value = LProject Then
LFound = True
Range("C" & LRow).Value = LAddress
Range("D" & LRow).Value = LPhoneNbr

'Encountered a blank project number (assuming end of list on Sheet1)
ElseIf IsEmpty(Range("A" & LRow).Value) = True Then

MsgBox ("No match was found. Changes were not made.")
Exit Sub
End If

LRow = LRow + 1
Loop

'Reposition back on Sheet2
Sheets("Sheet2").Select
Range("E5").Select

MsgBox ("Changes were successfully saved.")

End Sub

The macro code for the customer combo box:

Sub PopulateData()

Dim LProject As Integer

Dim LAddress As String

Dim LPhoneNbr As String

Dim LRow As Long

Dim LFound As Boolean

'Retrieve project number number

LProject = Range("H3").Value

'Move to Sheet1
Sheets("Sheet1").Select

LFound = False

LRow = 2

Do While LFound = False
'Found matching project, now update address and phone number information on Sheet2

If Range("A" & LRow).Value = LProject Then

LFound = True

LAddress = Range("C" & LRow).Value

LPhoneNbr = Range("D" & LRow).Value

Sheets("Sheet2").Select
Range("E5").Value = LAddress
Range("H8").Value = LPhoneNbr

'Encountered a blank project number (assuming end of list on Sheet1)

ElseIf IsEmpty(Range("A" & LRow).Value) = True Then

MsgBox ("No match was found for combo box selection.")

Exit Sub

End If

LRow = LRow + 1
Loop

End Sub