clear formatting in all commandbuttons on a worksheet (clear them as a group, not individually)

[kbishop94]

I have 41 command buttons on the main worksheet in my workbook. Whenever one of these command buttons are 'clicked', it first sets the bold attribute for all of the buttons to 'False'. Next it executes it's specific action (which is filtering or sorting data that is on that page) and then it changes the font in the specific command button that was just clicked to TRUE. This is done so that the user is able to identify which command button (because there are many) is currently selected (which would be the only one that is now BOLD.) Whenever a different command button is clicked, it first re-sets ALL the other command buttons to regular.
This is where I am trying to simplify my current code. Currently, my code sets the bold property for all 41 command buttons individually (which takes up A LOT of space for all of the command buttons):

' REMOVE ALL FORMATTING FROM THE COMMANDBUTTONS IN EXCEPT FOR THE CURRENT BUTTON THAT WAS JUST CLICKED: (cmdPackaging)

' CURRENT COMMANDBUTTON SELECTED:

cmdPackaging.Font.Bold = True

' UNFORMAT ALL OTHER DEPARTMENT COMMANDBUTTONS
cmdPack12.Font.Bold = False
cmdPack35.Font.Bold = False
cmdMilling.Font.Bold = False
cmdBlendAll.Font.Bold = False
cmdBlending.Font.Bold = False
cmdFlammableBlend.Font.Bold = False
cmdLabels.Font.Bold = False
cmdPowderAll.Font.Bold = False
cmdPowders.Font.Bold = False
cmdPolymers.Font.Bold = False
cmdMaintenance.Font.Bold = False
cmdAgAll.Font.Bold = False
cmdAgBlending.Font.Bold = False
cmdAgPackaging.Font.Bold = False
cmdLab.Font.Bold = False
cmdShipAll = False
cmdShipping.Font.Bold = False
cmdWarehouse.Font.Bold = False
cmdSupervisors.Font.Bold = False
cmdTollAll.Font.Bold = False
cmdToll.Font.Bold = False
cmdBuilding8.Font.Bold = False
cmdOilField.Font.Bold = False
cmdCustomerService.Font.Bold = False
cmdSales.Font.Bold = False
cmdQMS.Font.Bold = False
cmdHR.Font.Bold = False
cmdPurchasing.Font.Bold = False
cmdScheduling.Font.Bold = False
cmdAccounting.Font.Bold = False
cmdEHS.Font.Bold = False

' UNFORMAT ALL COMMANDBUTTONS FOR SUPERVISORS:
cmdCha.Font.Bold = False
cmdJUS.Font.Bold = False
cmdEDD.Font.Bold = False
cmdDAN.Font.Bold = False
cmdMIK.Font.Bold = False
cmdCOD.Font.Bold = False
cmdEHS.Font.Bold = False

' UNFORMAT ALL REMAINING COMMANDBUTTONS ON WORKSHEET:
cmdSalary.Font.Bold = False
cmdSalary.Font.Italic = False
cmdSalary.Font.Underline = False
cmdSalary.BackColor = &H8000000F
cmdHourly.BackColor = &H8000000F
cmdHourly.Font.Bold = False
cmdHourly.Font.Italic = False
cmdHourly.Font.Underline = False

So there is A LOT of code that I am (hoping) isnt necessary in order to achieve the desired effect I am after (setting the bold attribute to FALSE for all the command buttons on the worksheet.) Is there a way to do this?

Thank you for any help

Keith

 

[Fluff]

You could put the code to reset the formats into a sub of it's own & then call it from each button.

 

[Fluff]

You could also put the buttons into an array like

Sub ResetBtns()
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Array(cmdPackaging, cmdPack12, cmdPack35, cmdMilling, cmdBlendAll, cmdBlending, cmdFlammableBlend, _
             cmdLabels, cmdPowderAll, cmdPowders, cmdPolymers, cmdMaintenance, cmdAgAll, cmdAgBlending)
   For i = 0 To UBound(Ary)
      Ary(i).Font.Bold = False
   Next i
End Sub

and then call the resetBtns from each button.

 

[kbishop94]

You could put the code to reset the formats into a sub of it's own & then call it from each button.

Thanks. I had tried that awhile back but I couldnt figure out what was causing it to slow down to the point that it took several minutes (if it ever responded at all) to run the sub(?) Its been so long ago that I can't remember what I had in the code, but I'll have to revisit it again and see if I can get it to work this time. Thank you.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[kbishop94]

Using the Array method actually sped it up a good bit (after changing all the code for all 41 buttons) as well as making the entire worksheet code much shorter and cleaner. Thanks again.

 

[Fluff]

Glad to hear it helps & thanks for the feedback.

 

[kbishop94]

You could also put the buttons into an array like

Sub ResetBtns()
   Dim Ary As Variant
   Dim i As Long
  
   Ary = Array(cmdPackaging, cmdPack12, cmdPack35, cmdMilling, cmdBlendAll, cmdBlending, cmdFlammableBlend, _
             cmdLabels, cmdPowderAll, cmdPowders, cmdPolymers, cmdMaintenance, cmdAgAll, cmdAgBlending)
   For i = 0 To UBound(Ary)
      Ary(i).Font.Bold = False
   Next i
End Sub

and then call the resetBtns from each button.

Sorry for bringing this one back from the dead, but I'm revisiting it because I need to try to get this to work but in a module. Before I just used your array suggestion right in my worksheet code (which worked great.) But I never put the code into its own module like you suggested. Now I would like to finally see if I can get it to work but in a module of its own.

So here is the code and the resulting error I get after inserting the code into a module:

Sub ResetBtns()

   Dim Ary As Variant
   Dim i As Long
      
      Ary = Array(cmdPackaging, cmdBlending, cmdPowders)

   For i = 0 To UBound(Ary)
      Ary(i).Font.Bold = False
   Next i
End Sub

I get error:
"run-time error '424':
object required"

this line is highlighted as the code thats tripping it up:

Ary(i).Font.Bold = False

PS - I shortened what the array consists of (now just 3 command buttons instead of the 14 that was in the code back in June) just to simplify it for now as I am playing around with it, and as a way of troubleshooting this error thinking maybe I had one of the command buttons misspelled or something...) (I didn't)

 

[Fluff]

If they are activeX buttons, then the code still needs to be in the sheet module, not a standard module.

 

[kbishop94]

If they are activeX buttons, then the code still needs to be in the sheet module, not a standard module.

Gotcha. (so it turns out I had it right all along. lol)