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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Norie

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

Where are the command buttons located?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,459
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,459
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
 

Forum statistics

Threads
1,089,284
Messages
5,407,374
Members
403,139
Latest member
MrRadioNumbers

This Week's Hot Topics

Top