rorobear
09-07-2021, 06:57 PM
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
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