Modifying Data in Spreadsheets with VBA

While it’s usually obvious to a user how to add data in a spreadsheet, modifying data is often less obvious and an afterthought for many developers. By providing a clean and inutitive interface to modify data, inputs can be validated and controlled, ensuring datasets remain clean.

Time Est.80-110 Minutes
Template Files2016-10-25-modifying-data-with-vba-template
Solution Files2016-10-25-modifying-data-with-vba-solution

Spreadsheet Design Exercise 10

This exercise within the Spreadsheet Design series, continues the focus on Data Management. The steps below contain a large number of code snippets. If you would like to jump to the final consolidated code, click here.

Although an interface for modifying data can be built independent of any other functionality, this exercise will build on the solutions from two previous exercises (Adding New Data to Datasets in Excel, and Building a Custom Listbox in Excel with VBA). The solution will incorporate a user-form that allows users to modify data outside of the dataset. The challenge here is that the user must be able to modify the data while interacting with the listbox, only.

This posts’ exercise implements a technique that allows the user to select a entry row from the listbox, which will then populate an entry form. To help streamline the form and add some elegance, we will combine the “add” and “modify” forms with proper visual cues to the user. The user will also be given an opportunity to delete an entry.

Step 1: Set up workbook.

This exercise will use the solutions from Building Custom Listboxes in Excel with VBA, which can be downloaded here: 2016-10-25-modifying-data-with-vba-template. The layout of the document has been changed slightly. Most obvious, there is now a light grey box titled “Information” above the listbox. This will serve as the centralized information center, providing users with text regarding validation errors and actions they are about to take. The background has added a white fill, making the gridlines invisible. This makes the worksheet look cleaner; most of today’s exercise will be in the VBA editor, so gridlines are not needed.

Step 2: Add radio buttons to worksheet.

As previously mentioned, the approach we use today will allow the user to both add data and modify data using the same input form. This is a streamlined and user friendly approach, as the users only need to familiarize themselves with one form.

Given that the form will provide two actions, we want to provide the user with visual cues as to the action they are taking. They need to know whether hitting the button will “add” or “modify” data. One of a few visual cues we’ll provide the user is a radio button with two states: Modify or Add. To add radio buttons, click on (1) Developer > (2) Insert, then (3) click the radio button icon. Make sure to grab the ActiveX Control and NOT the Form control. If you do not see the Developer tab, read about activating it here.

Add a radio button ActiveX Control.
Add a radio button ActiveX Control.

Drag the radio button right above the Purchase ID header, in cell B9. Add a 2nd radio button and place it directly above the first. It should look as follows:

Place two radio buttons above the Purchase ID header.
Place two radio buttons above the Purchase ID header.

Next change the caption of both radio buttons so OptionButton2 changes to Add and OptionButton1 says Modify. To change the captions, (1) right click on the radio button, click (2) select Properties, then (3) change the Caption field in the dialogue box.

Create radio buttons for adding and modifying data.
The properties dialog for the radio button.

Step 3: Add shape for “Delete” button

Place a delete button under the modify button. Click on (1) Insert > (2) Shapes, then (3) click on the square shape. Move it under the Add button. Reshape to a similar size as the Add button, then type “Delete” while the shape is selected.

Use a shape to create a "Delete" button.
Use a shape to create a “Delete” button.

Step 4: Set up new named ranges.

We’ll move away from the radio buttons for a few minutes and set up a named range in for the entire listbox. This will help activate a subroutine which the user can use to select specific rows to modify. Name the range B12:I20 as Listbox_Purchase_ID, then name the cell B11 Input_Purchase_ID

Named ranges will help handle VBA scripts related to modifying data.
Set up named ranges in the workdsheet.

Step 5: Create a worksheet selection event for listbox.

Using a Worksheet_Selection event, a user will be able to query an entry for modification by clicking on the entry’s row. Add the following code to the Interface worksheet object.

Step 6: Create script to toggle “Add Transaction” state.

The input form will contain two scripts which define a “state” for the workbook. The workbook as currently in an “Add Transaction” state where the form functions to add lines to the dataset. We’ll need to create a “Modify Transaction” state to define how the form looks when users are modifying data. We’ll also need a script to revert the form back to its “Add Transaction” state when the user is done modifying data.

The following script can go in any module, but for the sake of keeping things organized, it may make sense to add a 3rd module called Application_State.

The script begins by modifying various elements of the form and interface. Details are noted in the form comments, but in summary: (1) the purchase ID selected is inserted into cell B11, (2) the “Modify” radio button is activated, (3) the submit button text changes from “Add” to “Modify”, (4) a delete button is made visible, (5) a helpful alert is provided in the information window, and (6) the entire selected row is highlighted as orange. These element changes provide visual cues to the user that they are modifying data, as opposed to adding data.

The next part of the script (Form Staging) pulls values from the dataset and places them in the import form. This will allow the user to see the results they are modifying. This is done by looping through the dataset and searching for the selected purchase ID. Once the loop finds a matching value, each data point is inserted into the input form. It is at this point where a user can begin directly modifying data on the form.

Step 7: Create script to toggle “Modifying Data” state.

The application will also need a script to revert to the “Add” state. This subroutine is very similar to the Add_State subroutine.

This subroutine begins with six element changes which reverse changes made by the modify script. The last part of the script clears all the contents from the form.

Step 8: Assign scripts to radio buttons.

While the modify state scripts were created in the last two steps, we have not yet assigned those to the radio buttons. An OptionButton_Click event can be created in VBA by double clicking on the radio control in Design mode. Go to (1) Developer > (2) Design Mode, then (3) double click on both radio buttons. The VBA module will appear and the OptionButton_Click subroutines will be loaded by default.

If you recall, the script created in Step 4 actually relies on users clicking in the listbox to activate the form’s “Modify” state. By including a modify radio button, we’re giving the user two methods of changing to modify mode: either (1) click any of the rows within the list box, or (2) click on the Modify radio button. Clicking on the radio button will move the cell cursor to the first row of the listbox (the Listbox_Anchor named range), activating the modify state.

For the “Add” radio button, add a line invoking the Add_State subroutine.

Step 9: Add modify data behavior in Add_Stock_Purchase subroutine.

The final step is to make modification to the Add_Stock_Purchase subroutine created in the previous Adding New Data to Datasets in Excel post. An excerpt of the section of the subroutine requiring changes is below, with the new lines either added or modified highlighted. You can also see the entire Add_Stock_Purchase subroutine in the Full Script section below.

The following updates were made to the existing code:

  1. The calcPurchaseID calculation is now nested in a conditional to run only if the “Add” radio button is selected. If “Modify” is selected, the calcPurchaseID value is taken from the Input_Purchase_ID input field, as it already exists.
  2. The new row insert (formally for row 2) is nested within a conditional to run only if the “Add” radio button is selected. If “Modify” is selected, a Do loop searches column A for a matching Purchase ID, then assigns the row value to the new long variable, k. Note that k should be declared with the rest of the variable declarations.
  3. For entry into the actual datasets, the former row value of 2 is now replaced with long variable k. Data entered into the form by the user (to modify data) will replace old values.

As an alternative to modifying the existing code, you could write a new subroutine for modifications. However, this would require a new parent subroutine to direct the button action, and most of the code would need to be duplicated regardless. With the slight modifications made in this exercise, all data, whether it is modified or added, is pushed through the validation process.

Step 10: Create delete subroutine.

The final subroutine will allow uses to delete a purchase from the record. If you recall from the Add_State and Modify_State subroutines, we hide/unhide a delete button. This way, the user is not shown the delete button when attempting to add a new transaction.

This script works somewhat similarly to the modify data action, omitting any data validation and data input operations. It also asks the user to confirm deletion with a Yes or No message box. If confirmed, a Do loop searches for the row with the purchase ID, then deletes the entire row. Details are then passed to the information screen.

Full VBA Scripts

Videos/Screen Captures

Screen capture of the modify data solution.
Screen capture of final solution.

Other Notes

  • An alternative would be to have a separate form handle data modification. This is strictly developer (user) preference, and the technique used in this exercise poses a slightly larger challenge.


  • As previously disclosed, ActiveX controls can be quite buggy, especially when part of hidden/unhidden ranges (such as navigation techniques) or when changing monitor resolutions.
  • Many users may have a motor reaction to hit the enter key when completing the form. If this is done after the last field is completed (Commission), the cell cursor will move to the listbox activating the modify state, and subsequently clearing the input form. One work-around would be to add a row between the form and the listbox.

Leave a Reply