Question about MSCommonDialog object

[Scott Hartig]

I'm something of a moron when it comes to these objects in Excel. So
this may be a stupid question....

I have a worksheet that I want to clone (I need two of them in the
same workbook which perform the same functions for different sets of
data). The worksheet I need to clone was produced by a vendor of
ours.

The worksheet has a command button object which produces an XML file
from the data on the sheet. It requires an MSCommonDialog object to
function properly.

Well, I can't copy/paste this CommonDialog object to my cloned sheet.
And I can't figure out how to embed a new one. The Command Button
object copied and pasted just fine. But Excel returns an error to me
when I try to do the same with the CommonDialog object.

Any suggestions?

 

[Tom Ogilvy]

Try using "more controls" from the control toolbox toolbar or if in the vbe,
additional controls under the tools menu or maybe you only need to create a
reference to it.

 

[Scott Hartig]

Can somebody help me create this reference? I tend to think that's my
answer. In the second sheet, where the identical command button
exists, I think I ought to be able to adjust the VB code so that it
points to the CommonDialog object which exists in the first sheet.

But I'm no VB programmer and I have no idea how to do this.

Here's the reference in the Command Button that is working correctly
(in Sheet1):

CommonDialog1.Filter = "XML Files (*.xml)|*.xml"
CommonDialog1.ShowSave
sName = CommonDialog1.Filename

The identical code currently exists in the second sheet (Sheet2).

Any thoughts?