Processes can be simplified for the user in some circumstances by using the Double_click event. This event can call macros, depending on the cursor position in the spreadsheet, and write the information to a database.
To take this article to its natural conclusion will require a database and a knowledge on how to connect to, and update, it. It is assumed the reader has the Developer ribbon displayed, and is familiar with the VBA Editor. If not, please Google Excel Developer Tab or Excel Code Window.
Why use Double_Click?
Consider the graphic below using Excel as a user interface to a database. When the spreadsheet opens it might be automatically populated from the database using a VBA Auto_Open macro. The user might then wish to write changes back to the database. One way of doing this is to copy a selected row into an editing area, make the changes, and save the information from that area to the database.
Typically, the application below would write to a database, either local or in the cloud, using Structured Query Language (SQL). This is beyond the scope of this exercise, which demonstrates simply how to select a row for update. Using Excel to Read and Write to a Database is another article in this series.
- Open a blank workbook. Enter information similar to the graphic.
The Save button is added by choosing the Developer tab on the ribbon, and selecting Insert in the Controls
We will use the Double_click event to determine what was been selected.
- Select B4 and choose the Formula tab in the ribbon. Select Define Name and call it “ID”.
Similarly, name C4 to E4 as “Manager_Name”, “Comm_Rate” and Work_Phone respectively.
This will enable us to refer to the cells by name in the code.
- Alt + F11 will open the VBA editor.
Place the code below in the code window BEHIND THE SPREADSHEET, i.e. not in a new module. In our exercise, the code is particular to Sheet 1.
Option Explicit 'i.e. all variables used must be declared. Dim strItem As Variant 'in this case as numeric or alphabetic. Dim nID as integer Dim r as integer Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Call Initialise ‘A sub-routine below r = ActiveCell.Row If r < 8 Then Exit Sub 'if out of range, exit. If Cells(r, 2) > "" Then nID = Cells(r, 2) 'find the ID. Range("ID") = nID ‘move it to the appropriate cell. Range("Manager_Name") = Cells(r, 3) ‘etc Range("Comm_Rate") = Cells(r, 4) Range("Work_Phone") = Cells(r, 5) Range("Manager_Name").Select End If End Sub Private Sub Initialise() Range("ID") = "" Range("Manager_Name") = "" Range("Comm_Rate") = "" Range("Work_Phone") = "" End Sub Sub Save() If Range("ID") = "" Then Exit Sub Dim msg As String msg = "ID: " & Range("ID") & ", Manager: " & Range("Manager_Name") & ", " & _ "Comm: " & Range("Comm_Rate") & ", Phone: " & Range("Work_Phone") & " " & _ "is available for update." MsgBox msg End Sub
- Right_click on the Save Button, and assign it to the “Save” code
- Double_click a row in the spreadsheet. If it is row 8 or higher, and is populated, the data will be copied into the editing boxes.
- Press Save to see values to be written to the database ( a message box only in this case. Write your own code to add to, and read from, an existing database).
- Protect your macro from being viewed or changed: Tools>Properties>Protection.
Excel File errors
Large Excel files are sometimes liable to corruption on saving back to disk. The application’s attempt to recover the data is seldom, in my experience, successful, and the workbook remains open, unsaved.
If you have an effective tool to fix xlsx damage, you can recover from such disasters.
Felix Hooker is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair rar damage and sql recovery software products. For more information visit www.datanumen.com