How to Bring up the Table Design Dialogbox (VBA) for the User to Select Table Designs

[rorobear]

Hello Everyone,

I tried using the macro record for this, but failed to get the desired outcome. I want to change my table styles but I don’t want to hard wire all the different styles into the code. What I’d like is a button that will bring up the Table Design Dialogbox and let the user pick and choose their own table design. Couldn’t find anything on the internet. It's probably requires some long hard Class Module, which I know nothing of so I thought I’d look to the forum. If anyone can help, I’d appreciate it (see sample workbook) and thank you as always.


Option Explicit

Sub Macro1()
'
' Macro1 Macro
'

'
Range("Table1[#All]").Select
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleMedium12"
End Sub

[p45cal]

First things first, I haven't got the solution!
In vba there are ways of bringing up some dialogue boxes, for example:
Application.Dialogs(xlDialogPageSetup).Show
will bring up the Page Setup dialogue box.
I don't think there is an enumeration for Table Design dialogue, nor for the New Table Style dialogue.

So I went looking for executing a control on the ribbon; generally you can do that as follows:

Dim myControl As CommandBarControl
Set myControl = CommandBars.FindControl(ID:=247)
myControl.Execute
which again brings up the Page Setup dialogue.
Now where I've come unstuck is that the Table Design tab which makes its appearance when you're on a Table on a sheet does NOT seem to be included in the CommandBars collection, so I haven't been able to find any of the controls within this tab.

I'll keep looking but I'm hoping someone else may be able to help…

Edit post posting:
I'm getting a bit warmer with:
Application.CommandBars.ExecuteMso ("TableStyleNew")

but I get an error with:

Application.CommandBars.ExecuteMso ("TableStylesGalleryNew")

which I don't understand because I get the following lines properly executed:
MsgBox Application.CommandBars.GetEnabledMso("TableStylesGalleryExcel")
MsgBox Application.CommandBars.GetLabelMso("TableStylesGalleryExcel")
MsgBox Application.CommandBars.GetScreentipMso("TableStylesGalleryExcel")
MsgBox Application.CommandBars.GetSupertipMso("TableStylesGalleryExcel")
(You may need the active cell to be in a table for these to work properly.)

[rorobear]

Yes, i spent several days working on it and looking for a solution and my search came up fruitless. the best i could do is to changes it one style at time. thank you for trying, your effort is appreciated. if i can't figure it out i may just have to omit this feature from my project.


Sub ChangeTableStyle()
Dim oStNormalNoNum As Style
On Error Resume Next
Set oStNormalNoNum = ActiveWorkbook.Styles("NormalNoNum")
On Error GoTo 0
If oStNormalNoNum Is Nothing Then
ActiveWorkbook.Styles.Add "NormalNoNum"
Set oStNormalNoNum = ActiveWorkbook.Styles("NormalNoNum")
oStNormalNoNum.IncludeNumber = False
End If
With ActiveSheet.ListObjects(1)
.Range.Style = "NormalNoNum"
'Now apply tablestyle:
.TableStyle = "TableStyleMedium7"
End With
ActiveWorkbook.Styles("NormalNoNum").Delete
End Sub

[Paul_Hossler]

You might do better by relocating the project controls, etc. to the Ribbon -- little tricky, but IMHO the efforts are worthwhile

As example for your Table Gallery, I have 2 'always there' groups and one that is visible only when the active cell is in a table

28922


There's a Forum here that specializes in the Ribbon

This is the Fluent XML I used. You need the Fluent editor to see / modify



<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad = "Ribbon_OnLoad">
<ribbon>
<tabs>


<tab id="tabProject" label="My Project" insertAfterMso="TabHome">
<group id="grpOne" label="First Group" >
<button id="a1" label="One" size="large" onAction="onActionSub" image="Alice" />
<button id="a2" label="Two" size="large" onAction="onActionSub" image="Asok1" />
<button id="a3" label="Three" size="large" onAction="onActionSub" image="Boss" />
</group>


<group id="grpTwo" label="Second Group" >
<button id="b1" label="One" size="large" onAction="onActionSub" image="Catbert" />
<button id="b2" label="Two" size="large" onAction="onActionSub" image="Dilbert1" />
<button id="b3" label="Three" size="large" onAction="onActionSub" image="Wally" />
</group>


<group id="grpTable" label="Table Functions" getVisible="InTable" >
<gallery idMso="TableStylesGalleryExcel"/>
</group>


</tab>
</tabs>
</ribbon>
</customUI>






This is the VBA macro code



Option Explicit


Public oRibbon As Object


'Callback for customUI.onLoad
Sub Ribbon_OnLoad(ribbon As IRibbonUI)
Set oRibbon = ribbon
End Sub


'Callback for a1 onAction
Sub onActionSub(control As IRibbonControl)
Select Case control.ID
Case "a1"
MsgBox "Group 1, Button 1"
Case "a2"
MsgBox "Group 1, Button 2"
Case "a3"
MsgBox "Group 1, Button 3"

Case "b1"
MsgBox "Group 2, Button 1"
Case "b2"
MsgBox "Group 2, Button 2"
Case "b3"
MsgBox "Group 2, Button 3"
End Select


End Sub


'Callback for TableQuickStyles getEnabled
Sub InTable(control As IRibbonControl, ByRef returnedVal)
returnedVal = IsActiveCellInTable
End Sub




'https://www.techrepublic.com/blog/microsoft-office/use-vba-to-determine-whether-an-active-cell-is-in-a-table/
'Function returns true if active cell is in a table and false if it isn't.


Function IsActiveCellInTable() As Boolean
Dim rngActiveCell As Range

Set rngActiveCell = ActiveCell
'Test for table.

'Statement produces error when active cell is not in a table.
On Error Resume Next
rngActiveCell = (rngActiveCell.ListObject.Name <> "")
On Error GoTo 0

'Set function's return value.
IsActiveCellInTable = rngActiveCell
End Function

[rorobear]

Paul,

this is great, thank you! But I’m not familiar with Ribbon UI and I’m not sure how I would incorporate it into my Userform. Trying to bring the table design dialogbox may not be the best approach as it doesn’t seem like an easy thing to accomplish. Instead, I was thinking I could use a button that every time it’s clicked it will cycle through the many different table styles. But right now the best I can come up with is an If Statement using a checkbox, but that only gives me two options. I’m starting to feel defeated, LOL.


Private Sub UserForm_Activate()
CheckBox1.Value = True '<---TableStyle
End Sub

Private Sub CheckBox1_Click()
Dim Table As ListObject
Set Table = ActiveSheet.ListObjects(1)
If CheckBox1.Value = True Then
ActiveSheet.ListObjects(1).TableStyle = "TableStyleMedium1"
Else
ActiveSheet.ListObjects(1).TableStyle = "TableStyleMedium2"
End If
End Sub