How to copy an Excel Worksheet with Forms to another workbook without external references

Avoid external references with Active X form controls in Excel
The trick is to replace all of your "Form Controls" with "ActiveX Controls". 
In the Developer toolbar in Excel they appear below the Form Controls.
The differences I have found:
  • When copying a sheet with the buttons or list boxes to another workbook, there are no longer external references to the original workbook if you use ActiveX controls.
  • ActiveX control graphical items can be resized and positioned using the "Design Mode" button in the Developer tab in Excel.
  • You rename or change properties of buttons and other items in Design Mode by right-click "Properties" and change the appropriate property.
  • VBA code is no longer stored by default in a Module, instead it is stored in the sheet specific code. 
  • This sheet specific code will not allow you to directly modify values of cell ranges that are in another sheet.  To do this you would call an external sub you write in a Module.

Leave a Reply

Your email address will not be published. Required fields are marked *