Code efficiency

Av8tordude

Well-known Member
Joined
Oct 13, 2007
Messages
1,074
Office Version
  1. 2019
Platform
  1. Windows
[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]
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Re: Code effieciency

Where are the command buttons located?
 
Upvote 0
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?
 
Upvote 0
Re: Code effieciency

no...but you could use textbox1, textbox2, textbox3, etc. Thank you for looking this.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top