Command Button

[How_Do_I]

I enter my data via a User Form. I've noted that when I press my command button to enter my data, if I press it again while my cursor is spinning and "thinking" about entering the correct data, then the next set of data can be entered. How can I prevent this please?

 

[djreiswig]

Maybe set the command button's enabled property to false when you are "thinking" and then back to true when you are done.

 

[Ruddles]

Agreed.

 

[How_Do_I]

Well two members agree so that must be the solution however, I haven't got a clue about most things and especially haven't got a VBA clue!

 

[VoG]

Maybe like this

Private Sub CommandButton1_Click()
Dim i As Long
'
'code to submit data
'
CommandButton1.Enabled = False
For i = 1 To 100000
    DoEvents
Next i
CommandButton1.Enabled = True
End Sub

 

[How_Do_I]

Hi Peter, sorry to say that didn't do the trick...

 

[Ruddles]

Post your _Click() code for the button in question.

 

[How_Do_I]

Hi Ruddles

Private Sub CommandButton3_Click()
Dim ws As Worksheet
Dim MyForm As UserForm1
With Application
    .Calculation = xlCalculationManual
    .EnableEvents = False
    .ScreenUpdating = False
End With
Set ws = Worksheets("FT Scores")
Set MyForm = UserForm1
With MyForm
'Message Boxes
If TextBox11.Value = TextBox12.Value Then
    MsgBox "Oi Muppet, sort the teams out!"
Exit Sub
End If
If TextBox11.Value = "" Or TextBox12.Value = "" Or TextBox3.Value = "" _
        Or TextBox4.Value = "" Or TextBox7.Value = "" Then
    MsgBox "Oi Losers, sort the Team or Scores out!"
Exit Sub
End If
If TextBox1.Value > TextBox3.Value Or TextBox2.Value > TextBox4.Value Then
MsgBox "Incorrect Scores Muppet!"
Exit Sub
End If
End With
'find first empty row was here
'copy the data to the database
With ws
  'find first empty row in database
lRow = ws.Cells(Rows.Count, 3) _
  .End(xlUp).Offset(1, 0).Row
  .Cells(lRow, 3).Value = Me.TextBox11.Value
  .Cells(lRow, 5).Value = Me.TextBox1.Value
  .Cells(lRow, 6).Value = Me.TextBox2.Value
  .Cells(lRow, 4).Value = Me.TextBox12.Value
  .Cells(lRow, 7).Value = Me.TextBox7.Value
  .Cells(lRow, 8).Value = Me.TextBox3.Value
  .Cells(lRow, 9).Value = Me.TextBox4.Value
  
End With
With MyForm
'clear the data
Me.TextBox11.Value = ""
Me.TextBox12.Value = ""
Me.SpinButton1.Value = "0"
Me.SpinButton2.Value = "0"
Me.SpinButton3.Value = "0"
Me.SpinButton4.Value = "0"
Me.TextBox7.Value = Format(Date, "Medium Date")
End With
With Application
    .ScreenUpdating = True
    .EnableEvents = True
    
End With
With UserForm1.ListBox1
       If (.Value <> vbNullString) Then
    
        'If more then one data rows
        If (.ListIndex >= 0 And Range("A12").End(xlUp) > 2) Then
        
        Range("A" & .ListIndex + 1).Resize(1, 2).Delete Shift:=xlUp
        .RowSource = "Results!A1:" & Range("B12").End(xlUp).Address
                   
        'If only one data row
        ElseIf (.ListIndex = 0 And Range("A12").End(xlUp) = 1) Then
        
        Range("A1").Resize(1, 2).Delete
        
        'Update listbox
        .RowSource = "Results!A2:B2"
        
        End If
    Else
        
       MsgBox "Please Select Data"
        
    End If
End With
End Sub

 

[How_Do_I]

Actually with Peter's code I hadn't changed it to CommandButton3... However, that code doesn't do what I want...

When I open my User Form I might have more that one result to put in...

Currently I put a result in and then let my workbook do it's magic. I then might select another fixed and put another result it. My problem in the OP is say I want to put all 12 results in... The first result might be HT 0 - 0 and FT 3 - 1 if I press my command button to start entering that result and then press the button 11 times whilst that result is being entered I will get my 3 - 1 fixture entered and the other 11 fixtures will go in at 0 - 0.

 

[Ruddles]

You need to disable the button as soon as it's clicked and only re-enable it again when you've finished processing the results, i.e. when you're actually ready for it to be pressed again.