What happens from pressing a VBA userform button twice?

bradyboyy88

Well-known Member
Joined
Feb 25, 2015
Messages
553
So I am trying to understand what exactly happens if you press a VBA command button twice if it runs a subroutine. For instance does the first execution of the subroutine run by the button stop mid code and restart since the button was pressed again? I am trying to set up some ADO connection stuff but really need to understand this logic before I can have it foolproof

Thanks
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,564
Office Version
365
Platform
Windows
Once you click the button the code in it's click event will be executed and unless you have DoEvents statements in that code clicking it again while the code is running should do nothing.
 

bradyboyy88

Well-known Member
Joined
Feb 25, 2015
Messages
553
Once you click the button the code in it's click event will be executed and unless you have DoEvents statements in that code clicking it again while the code is running should do nothing.
So I do use DoEvents in a subroutine that the buttons click event calls. Basically the button writes to the database whats in a textbox by running the SQLOpenDatabaseConnection and SQLWriteDatabase subroutines. So clicking it twice shoots my code into the error loop of the opendatabaseconnection. I cannot seem to figure out any ways around this except for to create a variable saying code is running but that gets annoying since i create hundreds of buttons with code inside them. If I can figure out a way to DoEvents with exceptions then id be set!

Code:
'Created on workbook_open and destroyed on workbook_close
Public Sub SQLCreateDatabaseConnection()
    
    'Create Connection
    Set oConn = CreateObject("ADODB.Connection")
    oConn.Mode = 3
    oConn.CursorLocation = adUseClient


End Sub


Public Sub SQLOpenDatabaseConnection(StrDBPath As String, EngineType As Integer)
 
    'Define Connection String by inputting StrDBPath into a larger string (Works for Excel DB)
    'Define Connection String by inputting StrDBPath into a larger string
    'Access Support for engine type
    If EngineType = 0 Then
    
        sConn = "Provider=Microsoft.ACE.OLEDB.15.0;" & _
                                 "Data Source=" & StrDBPath & ";" & _
                                 "Jet OLEDB:Engine Type=5;" & _
                                 "Persist Security Info=False;Mode=Share Exclusive;"
    
    'Excel Support for engine type
    ElseIf EngineType = 1 Then
        
        sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & StrDBPath & "';" & _
             "Extended Properties=""Excel 12.0;HDR=NO;ReadOnly=0;"";"
    
    End If
    
RetryConnection:
    Sleep 100
    DoEvents
    On Error GoTo ErrorHandler
        'Connect to the database


20       oConn.Open sConn


    On Error GoTo 0


Exit Sub


ErrorHandler:
'triggered by connection error. Most likely locking type
'MsgBox "looks like we had a connection error. The error number is " & Err.Number & " and the description is " & Err.Description & " on line " & Erl


Err.Clear
Resume RetryConnection


End Sub

Public Sub SQLWriteDatabase(SQLQuery As String)
    
    Sleep 100
    DoEvents
    
    'Open Record Set by executing SQL
    oConn.Execute SQLQuery


End Sub
And I only use DoEvents to keep the program from going into not responding when multiple users are in the queue to access the database since I set mode to exclusive
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,806
Messages
5,470,890
Members
406,733
Latest member
darzu

This Week's Hot Topics

Top