Complete a change to a cell during a macro button call

Question

I am working on a spreadsheet tool that queries a SQL Server database and populates various sheets with the results. I provide the user with a simple GUI (formatted cells) to enter their database credentials and an Excel form button which tests the connection. When the button is pressed and the connection string is formed correctly, I identify this by changing the color of a status indicator from red to green. I have added a check to the range of cells that hold the credentials using the Worksheet_Change function, which will switch the status from green back to red if any of the cells are altered.

The problem is users are entering some aspect of their connection string, maybe the last field, and then pressing the 'Test Connection' button without first pressing enter or navigating away. and actually writing the value to the cell. My 'Test Connection' macro (linked to the button) is called first, switching the status indicator to green (assuming correct credentials), but the Worksheet_Change method does not get called until after the button macro has run through. The result is that the status indicator blinks from green and then back to red despite a establishing a database connection successfully.

I have tried things like manually switching the focus away from the current cell. Before calling my 'TestConnection' function from the form button. But so far nothing has worked.

Edit: Some Code...

Private Sub Worksheet_Change(ByVal Target As Range)
    Call SetGlobals

    'Check if database criteria has changed
    If Not Intersect(Target, Target.Worksheet.Range(DB_CELL_RANGE)) Is Nothing Then
        Call UpdateDBStatus(1)
    End If

End Sub

'Connect to database using Main sheet credentials
Function TestConnection()

    'Connection vars
    Set cnn = New ADODB.Connection

    'Open the connection.
    On Error GoTo ConnectError
    cnn.Open GetConnectionString()

    'Update dependencies
    'On Error GoTo FilterError
    Call UpdateFilter("select ********", "F", "F")
    Call UpdateFilter("select *******", "E", "E")
    Call UpdateDBStatus(2)

    MsgBox "Connected successfully to '" & DBASE & "' on machine '" & SERVER & "'"
    'Cleanup
    cnn.Close
    Set cnn = Nothing

    Exit Function

ConnectError:
    Call UpdateDBStatus(1)
    MsgBox "Could not establish a connection."
    Exit Function

FilterError:
    MsgBox "Filter Update Failure."
    Exit Function

End Function

'Set the status of the database connection and mark the result
Public Function UpdateDBStatus(Status As Integer)
    If Status = 1 Then
        Sheets("Main").Range(DB_STATUS_CELL).Value = "Not Connected"
        Sheets("Main").Range(DB_STATUS_CELL).Interior.ColorIndex = 3
        DB_STATUS = False
    Else
        Sheets("Main").Range(DB_STATUS_CELL).Value = "Connected"
        Sheets("Main").Range(DB_STATUS_CELL).Interior.ColorIndex = 4
        DB_STATUS = True
    End If
End Function

Basically if someone is currently editing a cell inside of DB_CELL_RANGE and they presses the 'Test Connection' button I would like to have Worksheet_Change complete before calling 'TestConnection'.

Answer 1

Answer turned out to be a fairly simple boolean flag which I set to True when a successful database connection is established, then false after the next run of Worksheet_Change finishes. From then only check the DB connection when the flag is false. Code is as follows:

Public flag As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not flag Then
        If Not Intersect(Target, Target.Worksheet.Range(DB_CELL_RANGE)) Is Nothing Then
            UpdateDBStatus (1)
        End If
    Else
        flag = False
    End If
End Sub

'Connect to database using Main sheet credentials
Sub TestConnection()

    'Connection vars
    Set cnn = New ADODB.Connection

    'Open the connection.
    On Error GoTo ConnectError
    cnn.Open GetConnectionString()

    'Update dependencies
    On Error GoTo FilterError
    Call UpdateFilter("select ********", "F", "F")
    Call UpdateFilter("select *******", "E", "E")
    Call UpdateDBStatus(2)

    flag = True

    MsgBox "Connected successfully to '" & DBASE & "' on machine '" & SERVER & "'"
    'Cleanup
    cnn.Close
    Set cnn = Nothing

End Sub

Answer 2

Untested, but you should see the general idea...

Public LastGoodConnString As String  'this in a regular module

'worksheet module
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Target.Worksheet.Range(DB_CELL_RANGE)) Is Nothing Then
        CheckConnString 'Check if database criteria has changed
    End If
End Sub



'Connect to database using Main sheet credentials
Function TestConnection()

    Set cnn = New ADODB.Connection

    'Open the connection.
    On Error GoTo ConnectError
    cnn.Open GetConnectionString()
    ShowDBStatus True 'this will also cache the connection string...

    '<snipped code>

    Exit Function

ConnectError:
    ShowDBStatus False
    MsgBox "Could not establish a connection."
    Exit Function

End Function

'update DB Status if connection string is changed from a "known good" value
Public Sub CheckConnString()
    ShowDBStatus (GetConnectionString() = LastGoodConnString) _
                     And LastGoodConnString <> ""
End Sub


'Show the status of the database connection
Public Sub ShowDBStatus(StatusOK As Boolean)

    'if connected OK, remember the connection string
    If StatusOK Then LastGoodConnString = GetConnectionString()

    With Sheets("Main").Range(DB_STATUS_CELL)
        .Value = IIf(StatusOK, "Connected", "Not Connected")
        .Interior.ColorIndex = IIf(StatusOK, 4, 3)
    End With

End Sub

Answer 3

One way to approach this is you can disable the 'test connection' button by default. But either way you're not going to get around that 'worksheet change' being activated after, so I'd just not use it and use a custom function.

Update: After reviewing your code I've included code below the demonstrates what I'm talking about.

I re-wrote your validation check and call it at the start of the test only, and to loop through the validation range.

I also removed the update Status and pasted it throughout the code with more detailed messages. (including notes about the two error sections)

Sub TestConnection()

    Call ValidateInput

    If DB_STATUS Then
        'Connection vars
        Set cnn = New ADODB.Connection

        'Open the connection.
        On Error GoTo ConnectError 
'-Have ConnectError set the DB-STATUS_Cell to 'Error' and dbstatus to False, cell to red, ect.
        cnn.Open GetConnectionString()

        'Update dependencies
        'On Error GoTo FilterError 
'-Have FilterError set the DB-STATUS_Cell to 'Error' and dbstatus to False, cell to red, ect.
        Call UpdateFilter("select ********", "F", "F")
        Call UpdateFilter("select *******", "E", "E")

        Sheets("Main").Range(DB_STATUS_CELL).Value = "Connected"


        MsgBox "Connected successfully to '" & DBASE & "' on machine '" & SERVER & "'"
        'Cleanup
        cnn.Close
        Set cnn = Nothing
    Else
        MsgBox "Please be sure that you populate all fields", vbExclamation

Exit Sub

Public Sub ValidateInput()
    Dim rCell As Range

    'assuming the named range 'DB_CELL_RANGE' contains all of the input cells you want populated
    For Each rCell In Worksheet.Range(DB_CELL_RANGE)
        If rCell.Value = "" Then
            Sheets("Main").Range(DB_STATUS_CELL).Value = "Not Connected"
            Sheets("Main").Range(DB_STATUS_CELL).Interior.ColorIndex = 3
            DB_STATUS = False
            Exit Sub
        Else
            'keep checking range
        End If

        '- If we make it here, then all of the inputs are validated
        Sheets("Main").Range(DB_STATUS_CELL).Value = "Inputs Good, Testing Connection."
        Sheets("Main").Range(DB_STATUS_CELL).Interior.ColorIndex = 4
        DB_STATUS = True

    Next rCell

End Sub

Note: Assuming that DB_STATUS is a global variable that signifies if the connection can be tested. Also, I noticed you declared these as functions, but they didn't seem to return any values so I wrote my versions as Subroutines.