Code efficiency

Av8tordude

Well-known Member
Joined
Oct 13, 2007
Messages
743
[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]

Code:
[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]
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,466
Office Version
365
Platform
Windows
Re: Code effieciency

Where are the command buttons located?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,466
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Oct 13, 2007
Messages
743
Re: Code effieciency

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

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,466
Office Version
365
Platform
Windows
Re: Code effieciency

Try this.

1 Create a new class module and name it CmdBtn.

2 Paste this code in the class module.
Code:
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.
Code:
Dim TCButtons(1 To 10) As CmdBtn
5 Add this code to the Initialize event of the userform.
Code:
Dim I As Long

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

Watch MrExcel Video

Forum statistics

Threads
1,089,901
Messages
5,411,110
Members
403,342
Latest member
faizanhamied

This Week's Hot Topics

Top