reset command button colour vba Hello, I have a userform with 15 command buttons

Question

reset command button colour vba

Hello,

I have a userform with 15 command buttons. When one is pressed it changes colour (to red from black) and reset the last pressed button to its default colour black. I cannot remember the correct way of doing the EXButton (see highlighted). I found a class code on the net but I am not familiar with that yet.

Many Thanks

...

...

...

Private Sub cmdPlates_Click()
     Call processButton(cmdPlates, "Plates")
    
End Sub

All buttons call this sub:

Sub processButton(buttonName As CommandButton, mycriteria As String)

    Dim targetCell As String
    Dim tgSheet As Worksheet
    Dim dataBase As ListObject
   Dim EXButton As CommandButton
   
    targetCell = SheetSettings.Range("E3")
    Set tgSheet = sheetDatabase
    Set dataBase = tgSheet.ListObjects("TableDatabase")
   
   
    EXButton = "cmd" & targetCell
   
    buttonName.BackColor = vbRed     'the button pressed now
    EXButton.BackColor = vbBlack 'the button that was pressed before
  
       
        tgSheet.ListObjects("TableDatabase").Range.AutoFilter Field:=4, Criteria1:=mycriteria   'filters the table
         targetCell.Value = mycriteria
     
        Me.listBoxDisplay.List = tgSheet.ListObjects("TableDatabase").DataBodyRange.SpecialCells(xlCellTypeVisible).Value        'refreshes listbox

    SheetSettings.Range("E3").Value = mycriteria
   
End Sub

Thanks help !!

Explanation / Answer

Hi..

say that you have three cmd buttons on userform1 .

named: (not caption name)

cmdBtn1.

cmdBtn2.

cmdBtn3.

try this sample code :

[edit..]

Private Sub UserForm_Initialize()
For Each obj In Me.Controls
If obj.Name Like "cmd*" Then obj.BackColor = vbYellow
Next
End Sub

Private Sub cmdBtn1_Click()
For Each obj In Me.Controls
If obj.Name Like "cmd*" Then obj.BackColor = vbYellow
Next
cmdBtn1.BackColor = vbRed
MsgBox cmdBtn1.Object.Caption & ": red color"
End Sub

Private Sub cmdBtn2_Click()
For Each obj In Me.Controls
If obj.Name Like "cmd*" Then obj.BackColor = vbYellow
Next
cmdBtn2.BackColor = vbRed
MsgBox cmdBtn2.Object.Caption & ": red color"
End Sub

Private Sub cmdBtn3_Click()
For Each obj In Me.Controls
If obj.Name Like "cmd*" Then obj.BackColor = vbYellow
Next
cmdBtn3.BackColor = vbRed
MsgBox cmdBtn3.Object.Caption & ": red color"
End Sub

Related Questions

Navigate

• Browse (All)
• Browse R
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.