Using both ColumnHidden and ColumnOrder on same VBA statement

[Lateral]

Hi guys,

I currently have a subform displaying upt o 40 columns of data and have a number of Button that the user can click to restrict the columns that are viewed.

I am using the following block of code to do this and it works fine.

I am trying to simplify it if possible.

Private Sub Command369_Click()
Forms![Workorders]![Workorder Parts].Form!DisplayOrder.ColumnHidden = True 'Displayorder
Forms![Workorders]![Workorder Parts].Form!Combo37.ColumnHidden = True 'Select Web Part
Forms![Workorders]![Workorder Parts].Form!PartID.ColumnHidden = True 'Part name
Forms![Workorders]![Workorder Parts].Form!WorkOrderNotes.ColumnHidden = True ' Workorder Notes
Forms![Workorders]![Workorder Parts].Form!Quantity.ColumnHidden = True ' Quantity
Forms![Workorders]![Workorder Parts].Form!UnitPrice.ColumnHidden = True 'Unit price
Forms![Workorders]![Workorder Parts].Form!ItemTotal.ColumnHidden = True ' Item Total
Forms![Workorders]![Workorder Parts].Form!Combo14.ColumnHidden = True 'Kitname
Forms![Workorders]![Workorder Parts].Form!Text29.ColumnHidden = True 'Web Category
Forms![Workorders]![Workorder Parts].Form!Text39.ColumnHidden = True 'Web Order reference
Forms![Workorders]![Workorder Parts].Form!Text41.ColumnHidden = True 'Web order Number
Forms![Workorders]![Workorder Parts].Form!Text43.ColumnHidden = True ' Web product ID
Forms![Workorders]![Workorder Parts].Form!Text47.ColumnHidden = True ' Part Number
Forms![Workorders]![Workorder Parts].Form!Text49.ColumnHidden = True ' URL
Forms![Workorders]![Workorder Parts].Form!Text56.ColumnHidden = True 'Parts Wholesale price(AUD)
Forms![Workorders]![Workorder Parts].Form!Text58.ColumnHidden = True 'SOH
Forms![Workorders]![Workorder Parts].Form!SOH_Updated_State.ColumnHidden = True 'Click to Update SOH
Forms![Workorders]![Workorder Parts].Form!Text67.ColumnHidden = True ' Date SOH Updated
Forms![Workorders]![Workorder Parts].Form!ReOrder_Level.ColumnHidden = True 'Reorder Level
Forms![Workorders]![Workorder Parts].Form!Order_Notes.ColumnHidden = True ' Pack/Each/Kit/Set
Forms![Workorders]![Workorder Parts].Form!SupplierID.ColumnHidden = True ' Supplier
Forms![Workorders]![Workorder Parts].Form!Combo76.ColumnHidden = True 'Location
Forms![Workorders]![Workorder Parts].Form!Text78.ColumnHidden = True 'Last Stocktake Date
Forms![Workorders]![Workorder Parts].Form!SOH_Last_Updated.ColumnHidden = True 'Part SOH Last Updated
Forms![Workorders]![Workorder Parts].Form!LastSoldDate.ColumnHidden = True 'Part Last Sold Updated
Forms![Workorders]![Workorder Parts].Form!Wholesale_Price.ColumnHidden = True 'Order WSP (AUD)
Forms![Workorders]![Workorder Parts].Form!ChangeWebsite.ColumnHidden = True 'Change Website
Forms![Workorders]![Workorder Parts].Form!SupplierWSP.ColumnHidden = True 'Supplier WSP (USD)
Forms![Workorders]![Workorder Parts].Form!ShippingCosts.ColumnHidden = True 'Shipping Etc (AUD)
Forms![Workorders]![Workorder Parts].Form!WOSupplierWSPDate.ColumnHidden = True 'WOSupplierWSPDate
Forms![Workorders]![Workorder Parts].Form!WOShippingCostsDate.ColumnHidden = True 'WOShippingCostsDate
Forms![Workorders]![Workorder Parts].Form![TotalOrderWSP(AUD)].ColumnHidden = True 'TotalOrderWSP(AUD)
Forms![Workorders]![Workorder Parts].Form!PartProfit.ColumnHidden = True 'PartProfit
Forms![Workorders]![Workorder Parts].Form!Text90.ColumnHidden = True 'Part Last Purchased Date
Forms![Workorders]![Workorder Parts].Form!ExchangeRate.ColumnHidden = True 'ExchangeRate
Forms![Workorders]![Workorder Parts].Form!CountParts.ColumnHidden = True '# of Items
Forms![Workorders]![Workorder Parts].Form![Single Part Profit].ColumnHidden = True 'Single Part Profit
Forms![Workorders]![Workorder Parts].Form![Qty Part Profit].ColumnHidden = True 'Qty Part Profit
Forms![Workorders]![Workorder Parts].Form!TotalSupplierWSP.ColumnHidden = True 'Total Supplier WSP (USD)
Forms![Workorders]![Workorder Parts].Form!TotalShipping.ColumnHidden = True 'Total Shipping (AUD)
Forms![Workorders]![Workorder Parts].Form!SupplierWSPDate.ColumnHidden = True 'SupplierWSPDate
Forms![Workorders]![Workorder Parts].Form!ShippingCostsDate.ColumnHidden = True 'ShippingCostsDate





' Set the default column order

Forms![Workorders]![Workorder Parts].Form!DisplayOrder.ColumnOrder = 1 ColumnHidden = False 'Displayorder
'Forms![Workorders]![Workorder Parts].Form!Combo37.ColumnOrder = 2 'Select Web Part
'Forms![Workorders]![Workorder Parts].Form!PartID.ColumnOrder = 3 'Part name
'Forms![Workorders]![Workorder Parts].Form!WorkOrderNotes.ColumnOrder = 4 ' Workorder Notes
'Forms![Workorders]![Workorder Parts].Form!Quantity.ColumnOrder = 5 ' Quantity
'Forms![Workorders]![Workorder Parts].Form!UnitPrice.ColumnOrder = 6 'Unit price
'Forms![Workorders]![Workorder Parts].Form!ItemTotal.ColumnOrder = 7 ' Item Total
'Forms![Workorders]![Workorder Parts].Form!Combo14.ColumnOrder = 8 'Kitname
Forms![Workorders]![Workorder Parts].Form!Text29.ColumnOrder = 9 'Web Category
'Forms![Workorders]![Workorder Parts].Form!Text39.ColumnOrder = 10 'Web Order reference
Forms![Workorders]![Workorder Parts].Form!Text41.ColumnOrder = 11 'Web order Number
Forms![Workorders]![Workorder Parts].Form!Text43.ColumnOrder = 12 ' Web product ID
Forms![Workorders]![Workorder Parts].Form!Text47.ColumnOrder = 13 ' Part Number
Forms![Workorders]![Workorder Parts].Form!Text49.ColumnOrder = 14 ' URL
Forms![Workorders]![Workorder Parts].Form!Text56.ColumnOrder = 15 'Parts Wholesale price(AUD)
Forms![Workorders]![Workorder Parts].Form!Text58.ColumnOrder = 16 'SOH
Forms![Workorders]![Workorder Parts].Form!SOH_Updated_State.ColumnOrder = 17 'Click to Update SOH
Forms![Workorders]![Workorder Parts].Form!Text67.ColumnOrder = 18 ' Date SOH Updated
Forms![Workorders]![Workorder Parts].Form!ReOrder_Level.ColumnOrder = 19 'Reorder Level
Forms![Workorders]![Workorder Parts].Form!Order_Notes.ColumnOrder = 20 ' Pack/Each/Kit/Set
Forms![Workorders]![Workorder Parts].Form!SupplierID.ColumnOrder = 21 ' Supplier
Forms![Workorders]![Workorder Parts].Form!Combo76.ColumnOrder = 22 'Location
Forms![Workorders]![Workorder Parts].Form!Text78.ColumnOrder = 23 'Last Stocktake Date
Forms![Workorders]![Workorder Parts].Form!SOH_Last_Updated.ColumnOrder = 24 'Part SOH Last Updated
Forms![Workorders]![Workorder Parts].Form!LastSoldDate.ColumnOrder = 25 'Part Last Sold Updated
Forms![Workorders]![Workorder Parts].Form!Wholesale_Price.ColumnOrder = 26 'Order WSP (AUD)
Forms![Workorders]![Workorder Parts].Form!ChangeWebsite.ColumnOrder = 27 'Change Website
Forms![Workorders]![Workorder Parts].Form!SupplierWSP.ColumnOrder = 28 'Supplier WSP (USD)
Forms![Workorders]![Workorder Parts].Form!ShippingCosts.ColumnOrder = 29 'Shipping Etc (AUD)
Forms![Workorders]![Workorder Parts].Form!WOSupplierWSPDate.ColumnOrder = 30 'WOSupplierWSPDate
Forms![Workorders]![Workorder Parts].Form!WOShippingCostsDate.ColumnOrder = 31 'WOShippingCostsDate
Forms![Workorders]![Workorder Parts].Form![TotalOrderWSP(AUD)].ColumnOrder = 32 'TotalOrderWSP(AUD)
Forms![Workorders]![Workorder Parts].Form!PartProfit.ColumnOrder = 33 'PartProfit
Forms![Workorders]![Workorder Parts].Form!Text90.ColumnOrder = 34 'Part Last Purchased Date
Forms![Workorders]![Workorder Parts].Form!ExchangeRate.ColumnOrder = 35 'ExchangeRate
Forms![Workorders]![Workorder Parts].Form!CountParts.ColumnOrder = 36 '# of Items
Forms![Workorders]![Workorder Parts].Form![Single Part Profit].ColumnOrder = 37 'Single Part Profit
Forms![Workorders]![Workorder Parts].Form![Qty Part Profit].ColumnOrder = 38 'Qty Part Profit
Forms![Workorders]![Workorder Parts].Form!TotalSupplierWSP.ColumnOrder = 39 'Total Supplier WSP (USD)
Forms![Workorders]![Workorder Parts].Form!TotalShipping.ColumnOrder = 40 'Total Shipping (AUD)
Forms![Workorders]![Workorder Parts].Form!SupplierWSPDate.ColumnOrder = 41 'SupplierWSPDate
Forms![Workorders]![Workorder Parts].Form!ShippingCostsDate.ColumnOrder = 42 'ShippingCostsDate
End Sub

Is there a way to use the ColumnOrder and ColumnHidden options on the same "line"?

I've tried various ways but can't get it to work.

Thanks for any help.

Cheers
Greg

 

[Ranman256]

No. That's 2 different commands.