Solved: Programmatically add controls 2

[Djblois]

If you read my first post I got the form added with this code:

LC = "ItemCode"
No = "NoItemNumber"
SBI = "SortbyItem"
SBP = "SortbyProduct"
PivotTableOptions.Controls.Add bstrProgId:="Forms.Frame.1", Name:=LC, Visible:=True
PivotTableOptions.Controls(LC).Top = 157
PivotTableOptions.Controls(LC).Left = 6
PivotTableOptions.Controls(LC).Height = 70
PivotTableOptions.Controls(LC).Width = 210
PivotTableOptions.Controls(LC).Caption = "Do you want Item# in the Report?"
PivotTableOptions.Controls(LC).Font.Bold = True
PivotTableOptions.Controls(LC).Add bstrProgId:="Forms.optionbutton.1", Name:=SBP, Visible:=True
PivotTableOptions.Controls(SBP).Top = 14
PivotTableOptions.Controls(SBP).Left = 12
PivotTableOptions.Controls(SBP).Height = 14
PivotTableOptions.Controls(SBP).Width = 120
PivotTableOptions.Controls(SBP).Caption = "Yes, Sort by Product"
PivotTableOptions.Controls(SBP).Font.Bold = True
PivotTableOptions.Controls(SBP).GroupName = Itemcode


PivotTableOptions.Controls(LC).Add bstrProgId:="Forms.optionbutton.1", Name:=SBI, Visible:=True
PivotTableOptions.Controls(SBI).Top = 38
PivotTableOptions.Controls(SBI).Left = 12
PivotTableOptions.Controls(SBI).Height = 14
PivotTableOptions.Controls(SBI).Width = 120
PivotTableOptions.Controls(SBI).Caption = "Yes, Sort by Item#"
PivotTableOptions.Controls(SBI).Font.Bold = True
PivotTableOptions.Controls(SBI).GroupName = Itemcode

PivotTableOptions.Controls(LC).Add bstrProgId:="Forms.optionbutton.1", Name:=No, Visible:=True
PivotTableOptions.Controls(No).Top = 27
PivotTableOptions.Controls(No).Left = 140
PivotTableOptions.Controls(No).Height = 14
PivotTableOptions.Controls(No).Width = 30
PivotTableOptions.Controls(No).Caption = "No"
PivotTableOptions.Controls(No).Font.Bold = True
PivotTableOptions.Controls(No).Value = True
PivotTableOptions.Controls(No).GroupName = Itemcode

Unfortunately my checkboxes do not recognize the controls I added. here is the code that I use:

'Add Fields to Pivot Table
On Error Resume Next
If PivotTableOptions.NoItemNumber.Value = True Then
pt.AddFields RowFields:=Array("Customer", "Cust#", "Product"), ColumnFields:="Date"
pt.PivotFields("Cust#").Subtotals = Array(False, False, False, False, False, False, _
False, False, False, False, False, False)
pt.PivotFields("Product").Subtotals = Array(False, False, False, False, False, False, _
False, False, False, False, False, False)
End If
If PivotTableOptions.SortbyProduct.Value = True Then
pt.AddFields RowFields:=Array("Customer", "Cust#", "Product", "Item#"), ColumnFields:="Date"
pt.PivotFields("Cust#").Subtotals = Array(False, False, False, False, False, False, _
False, False, False, False, False, False)
pt.PivotFields("Product").Subtotals = Array(False, False, False, False, False, False, _
False, False, False, False, False, False)
pt.PivotFields("Item#").Subtotals = Array(False, False, False, False, False, False, _
False, False, False, False, False, False)
End If
If PivotTableOptions.SortbyItem.Value = True Then
pt.AddFields RowFields:=Array("Customer", "Cust#", "Item#", "Product"), ColumnFields:="Date"
pt.PivotFields("Cust#").Subtotals = Array(False, False, False, False, False, False, _
False, False, False, False, False, False)
pt.PivotFields("Product").Subtotals = Array(False, False, False, False, False, False, _
False, False, False, False, False, False)
pt.PivotFields("Item#").Subtotals = Array(False, False, False, False, False, False, _
False, False, False, False, False, False)
End If

[XLGibbs]

Before I get into solving this, we know you have a big project going..may be useful to keep related questions in the same thread so that people trying to help can see the "big picture" of what is going on as it relates to other aspects of the project...

THis post requires that we read the other one and so forth..

After the code adds the objects, did you double check the properties of those objects to make sure the names are attached properly?


OptionButtons are different from Checkboxes as well...

[Djblois]

I actually figured this out also.

[Djblois]

Thank you XLGibbs. If I start one post for it it will be on the page for months because I keep adding to it. I am creating a add-in for my company to run reports on (right now any reports either look like crap or take over an hour to modify.) Whenever, I come up with a useful idea or get a feature request or find a bug I might be adding to it if I get stumped.

[XLGibbs]

The code didn't seem right to me:

Dim PTO As UserForm

Set PTO = PivotTableOptions

LC = "ItemCode": No = "NoItemNumber"
SBI = "SortbyItem": SBP = "SortbyProduct"

With PTO
.Controls.Add bstrProgId:="Forms.Frame.1", Name:=LC, Visible:=True
With .Controls(LC)
.Top = 157
.Left = 6
.Height = 70
.Width = 210
.Caption = "Do you want Item# in the Report?"
.Font.Bold = True
End With

.Controls.Add bstrProgId:="Forms.optionbutton.1", Name:=SBP, Visible:=True
With .Controls(SBP)
.Top = 14
.Left = 12
.Height = 14
.Width = 120
.Caption = "Yes, Sort by Product"
.Font.Bold = True
.GroupName = Itemcode
End With

.Controls.Add bstrProgId:="Forms.optionbutton.1", Name:=SBI, Visible:=True
With .Controls(SBI)
.Top = 38
.Left = 12
.Height = 14
.Width = 120
.Caption = "Yes, Sort by Item#"
.Font.Bold = True
.GroupName = Itemcode
End With

.Controls.Add bstrProgId:="Forms.optionbutton.1", Name:=No, Visible:=True
With .Controls(No)
.Top = 27
.Left = 140
.Height = 14
.Width = 30
.Caption = "No"
.Font.Bold = True
.Value = True
.GroupName = Itemcode
End With

End With

Am not sure why the objects would not be recognized by the other code if they were named properly. I would still double check that aspect (make sure the objects have the right names...)