What happens from pressing a VBA userform button twice?

bradyboyy88

Well-known Member
Joined
Feb 25, 2015
Messages
562
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,299
Office Version
  1. 365
Platform
  1. 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
562
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,133,651
Messages
5,660,133
Members
418,551
Latest member
rambo17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top