What happens from pressing a VBA userform button twice?

bradyboyy88

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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,633
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
554
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,101,935
Messages
5,483,781
Members
407,410
Latest member
catherinejoy

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top