Code efficiency

[Av8tordude]

[FONT=Helvetica Neue, Helvetica, Arial, sans-serif]I have 10 command buttons named cbTC#. The code below works good, however Is there a better way to code this without having to write the code below 10x (1 for each command button)? Thanks[/FONT]

[COLOR=#000000][FONT=&quot]Private Sub cbTC1_Click()[/FONT][/COLOR][COLOR=#000000][FONT=&quot]If Len(tbx800) > 0 Then
    If MsgBox("Do you want to delete data?", vbYesNo) = vbYes Then
        tbx800.Value = ""
    End If
End If
End Sub
[COLOR=#222222][FONT=Verdana]

[/FONT][/COLOR]
[/FONT][/COLOR]

 

[Norie]

Re: Code effieciency

Where are the command buttons located?

 

[Av8tordude]

Re: Code effieciency

userform

 

[Norie]

Re: Code effieciency

You could add a class module to handle the click events of the buttons.

What are their names?

Is the textbox i.e. tbx800 the same for each button?

 

[Av8tordude]

Re: Code effieciency

no...but you could use textbox1, textbox2, textbox3, etc. Thank you for looking this.

 

[Norie]

Re: Code effieciency

Try this.

1 Create a new class module and name it CmdBtn.

2 Paste this code in the class module.

Option Explicit

Public WithEvents cb As MSForms.CommandButton

Private Sub cb_Click()
Dim strTextBox As String
Dim Res As VbMsgBoxResult

    strTextBox = cmd.Tag
    
    If strTextBox <> "" Then
    
        Res = MsgBox("Do you want to delete data?", vbYesNo)
        
        If Res = vbYes Then
            cb.Parent.Controls(strTextBox).Value = ""
        End If
        
    End If

End Sub

3 Set the Tag property of the command buttons cbTC1, cbTc2 etc. to the name of the textbox associated with each command button.

4 Add this declaration at the top of the userform module.

Dim TCButtons(1 To 10) As CmdBtn

5 Add this code to the Initialize event of the userform.

Dim I As Long

    For I = 1 To 10
        Set TCButtons(I) = New CmdBtn
        Set TCButtons(I).cb = Me.Controls("cbTC" & I)        
    Next I