Pass Key Components

Acknowledging the need to store the old value in a cell, the client is about to update leads to the addition of the Workbook_SheetSelectionChange procedure to the Workbook code module. Of course, it is possible for the client to select multiple cells at one time. From the perspective of the application, the only time it might make sense is when the user wants to delete multiple connections. Although it might be a useful capability to have, you will leave it out of this version of the software. You therefore need to ensure that the user is not allowed to select multiple cells at the same time. The code for both the Workbook_SheetChange and the Workbook_SheetSelectionChange procedures follows this paragraph. It also contains the skeleton code to add and delete a connection (addAConnection and deleteAConnection, respectively), as well as the complete code for changing a connection (changeAConnection). The last one turns out to be easy because all it does is validate the change, protects the new value entered by the user, calls deleteAConnection, restores the protected new value, and calls addAConnection! Remember that all the following code goes into the ThisWorkbook code module:

Dim OldVal As String

Function addAConnection(Sh As Worksheet, Target As Range) End Function

Sub deleteAConnection(Sh As Worksheet, Target As Range, OldVal As String) End Sub

Sub changeAConnection(Sh As Worksheet, Target As Range, OldVal As String) Dim Conn2 As ExcelCoords Dim SavedVal As String

Conn2 = WiringToExcelCoords(Target.Value) If Conn2.Row = 0 Then

MsgBox Target.Value & " is not an acceptable value" Application.EnableEvents = False Target.Value = OldVal Application.EnableEvents = True Exit Sub '<<<<< End If

SavedVal = Target.Value deleteAConnection Sh, Target, OldVal Application.EnableEvents = False Target.Value = SavedVal Application.EnableEvents = True addAConnection Sh, Target End Sub

Private Sub Workbook_SheetChange( _

ByVal Sh As Object, ByVal Target As Range) If Not TypeOf Sh Is Worksheet Then Exit Sub

If Not (Sh.Range(cWSWiringTagAddr).Value = cWSWiringTagID) Then _

If Target.Cells.Count > 1 Then

MsgBox "Version 1 of the software doesn't support" & vbNewLine _ & "concurrent changes to multiple connections" & vbNewLine _ & "This change should not have happened." & vbNewLine _ & "The worksheet is probably corrupt! " & vbNewLine _ & "No automatic (complementary) changes have been made" Exit Sub End If

If OldVal = Target.Value Then Exit Sub '<<<<< If Target.Font.ColorIndex = cUnusedColorIdx Then addAConnection Sh, Target ElseIf IsEmpty(Target) Then deleteAConnection Sh, Target, OldVal

Else changeAConnection Sh, Target, OldVal End If End Sub

Private Sub Workbook_SheetSelectionChange( _ ByVal Sh As Object, ByVal Target As Range) If Not TypeOf Sh Is Worksheet Then Exit Sub

0 0

Post a comment