Apply Table Style To Range Of Worksheets

[Al Del]

Would like to apply a table style to a range of worksheets, I get a "Compile error: no sub or function not defined." error when I run this code.

The tables have identical number of columns with varying rows. The all start at A1.

Sub ChangeStyle()
Dim x As Integer

 For x = 6 To Worksheets.Count
    With Workheets(x)
      .ListObjects.Add(xlSrcRange, .Range("$A$1"), , xlYes).Name = "Table" & x
      .ListObjects("Table" & x).TableStyle = "TableStyleMedium10"

    End With
 Next x

End Sub

 

[Al Del]

Can pressing Cancel leave the original Table Style and entering nothing then OK set the Table Style to nothing?

 

[My Aswer Is This]

Can pressing Cancel leave the original Table Style and entering nothing then OK set the Table Style to nothing?

If you do not want to change the Table style then why run the script to begin with?

 

[Al Del]

Can pressing Cancel maintain original table style and entering nothing set the table style to nothing? If not the code you provided is great, thanks.

 

[My Aswer Is This]

Can pressing Cancel maintain original table style and entering nothing set the table style to nothing? If not the code you provided is great, thanks.

You did not answer my question.
Why run the script if you do not want to change the style?
And I also wonder why anyone would want to change the table style to nothing.

 

[Al Del]

Putting the workbook together for a friend and I'm guessing there would be uncertainty on the colors they like. Rather than emailing the workbook back and forth, they can pick whatever they like.

Not a big issue, if the user were to run the macro, then change their mind before pressing the OK, they probably wouldn't remember the table style they had previously.

Once again the macro works great, thanks much, appreciate the help.

 

[My Aswer Is This]

Try this:
They will get a question
Are you sure you want to do this?
If they click No the script will stop:

Sub Table_Style()
'Modified 6/29/18 2:10 PM EDT
Dim lb As ListObject
On Error GoTo M
Dim i As Long
Dim ans As String
'ans = Range("O1").Value ' InputBox("Enter style you want", "To clear table style press Cancel")
ans = InputBox("Enter style you want", "To clear table style press Cancel")
qq = MsgBox("Are you sure you want to do this", vbYesNo)
If qq = vbNo Then Exit Sub
For i = 1 To Sheets.Count
    For Each lb In Sheets(i).ListObjects
     If ans <> "" Then
     lb.TableStyle = "TableStyle" & ans
    Else
    lb.TableStyle = ""
    End If
    
    Next
Next
Exit Sub
M:
MsgBox "Your Table Style of  " & ans & vbNewLine & "Does not Exist"
End Sub

 

[Al Del]

I get Compile Error variable not defined, qq is highlighted.

qq = MsgBox("Are you sure you want to do this", vbYesNo)

 

[My Aswer Is This]

Try this:

Sub Table_Style()
'Modified 6/29/18 2:35 PM EDT
Dim lb As ListObject
On Error GoTo M
Dim i As Long
Dim ans As String
Dim qq As String
ans = InputBox("Enter style you want", "To clear table style press Cancel")
qq = MsgBox("Are you sure you want to do this", vbYesNo)
If qq = vbNo Then Exit Sub
    For i = 1 To Sheets.Count
    
        For Each lb In Sheets(i).ListObjects
            If ans <> "" Then
                lb.TableStyle = "TableStyle" & ans
            Else
                lb.TableStyle = ""
            End If
    
        Next
    Next
Exit Sub
M:
MsgBox "Your Table Style of  " & ans & vbNewLine & "Does not Exist"
End Sub

 

[My Aswer Is This]

Here is something you may like:

This script will put a list of all style names in column T of your sheet.

If you don't like column T modify part of script marked in red

Sub List_Of_Table_Styles()
'Modified 6/29/18 2:45 PM EDT
Dim i As Long
Dim e As Long
Dim s As Long
    For i = 1 To 60
    
        MyVal = i
        With Cells(i, "[COLOR=#ff0000]T[/COLOR]")
            Select Case MyVal
            
                Case 1 To 21
                    .Value = "Light" & i
                 Case 22 To 49
                    s = s + 1: .Value = "Medium" & s
                Case 50 To 60
                    e = e + 1: .Value = "Dark" & e
            
            End Select
        End With
    Next
End Sub

 

[Al Del]

Both scripts work to perfection. Thank again, needed to add Dim MyVal As String to List Of Table Styles.