UserForm Button That Loops Through Various Table Styles on the Table Design Ribbon

[rorobear]

Hello Everyone,

I would love some help condensing my VBA code. As always, if anyone can help, I would be most grateful. I have a userform with a button on it. When click, it cycles through some of the different table styles in the Table Design Ribbon. The code works, but it’s a little lengthy and I'm reluctant to add more styles and make it even longer. I want to know if there is any way to loop through the table styles and use less code. The sample file is included and thank you again.


Private Sub ChooseTableStyle_Click()
With ChooseTableStyle
Select Case .Caption

Case "TableStyleLight1" '<---must be on form button
Call TS1
.Caption = "TableStyleLight2"

Case "TableStyleLight2"
Call TS2
.Caption = "TableStyleLight3"

Case "TableStyleLight3"
Call TS3
.Caption = "TableStyleLight4"

Case "TableStyleLight4"
Call TS4
.Caption = "TableStyleLight5"

Case "TableStyleLight5"
Call TS5
.Caption = "TableStyleMedium1"

Case "TableStyleMedium1"
Call TS6
.Caption = "TableStyleMedium2"

Case "TableStyleMedium2"
Call TS7
.Caption = "TableStyleMedium3"

Case "TableStyleMedium3"
Call TS8
.Caption = "TableStyleMedium4"

Case "TableStyleMedium4"
Call TS9
.Caption = "TableStyleMedium5"

Case "TableStyleMedium5"
Call TS10

.Caption = "TableStyleLight1" '<---return when styles are finished
MsgBox "End of TableStyles. For Additional TableStyles Refer to Table Design on Spreadsheet", _
vbInformation, "Table Styles"
End Select
End With

End Sub
'these are the light tables
Sub TS1()
ActiveSheet.ListObjects(1).TableStyle = "TableStyleLight1"
End Sub

Sub TS2()
ActiveSheet.ListObjects(1).TableStyle = "TableStyleLight2"
End Sub

Sub TS3()
ActiveSheet.ListObjects(1).TableStyle = "TableStyleLight3"
End Sub

Sub TS4()
ActiveSheet.ListObjects(1).TableStyle = "TableStyleLight4"
End Sub

Sub TS5()
ActiveSheet.ListObjects(1).TableStyle = "TableStyleLight5"
End Sub

'these are the medium tables
Sub TS6()
ActiveSheet.ListObjects(1).TableStyle = "TableStyleMedium1"
End Sub

Sub TS7()
ActiveSheet.ListObjects(1).TableStyle = "TableStyleMedium2"
End Sub

Sub TS8()
ActiveSheet.ListObjects(1).TableStyle = "TableStyleMedium3"
End Sub

Sub TS9()
ActiveSheet.ListObjects(1).TableStyle = "TableStyleMedium4"
End Sub

Sub TS10()
ActiveSheet.ListObjects(1).TableStyle = "TableStyleMedium5"
End Sub

[arnelgp]

i think the code becomes bigger?
https://www.dropbox.com/scl/fi/jbi50ci0n9jo02w9j0kgh/SamplwWorkbook-3.xlsm?dl=0&rlkey=ke297991bephw8ixamf5ssjch

[rorobear]

lol, yes the code did get bigger especially when i add more styles. but admittedly, i think it looks better visually with the radio buttons than the one button click. thank you kindly for your help and support on this project. now i can move to the next phase.

[arnelgp]

lol, yes the code did get bigger especially when i add more styles. but admittedly, i think it looks better visually with the radio buttons than the one button click. thank you kindly for your help and support on this project. now i can move to the next phase.
:friends: