I'm using VBA to copy a range from a worksheet in one workbook to a
worksheet in another workbook. The range I'm copying from has buttons
that I don't want to copy. Can I copy the range without copying the
buttons? Alternately, can I delete only the buttons in the copied
range when finished copying (to prevent deleting buttons already on the
destination sheet)? The buttons on both sheets were created with the
Forms menu. ExcelXP (2002). I cannot control the properties of the
buttons in the source file. TIA, James

someworksheetvariable.buttons.delete

where someworksheetvariable represents your new worksheet.

Zone wrote:

I'm using VBA to copy a range from a worksheet in one workbook to a
worksheet in another workbook. The range I'm copying from has buttons
that I don't want to copy. Can I copy the range without copying the
buttons? Alternately, can I delete only the buttons in the copied
range when finished copying (to prevent deleting buttons already on the
destination sheet)? The buttons on both sheets were created with the
Forms menu. ExcelXP (2002). I cannot control the properties of the
buttons in the source file. TIA, James

--

Dave Peterson

Thanks, Dave. I guess the best strategy is to delete the buttons on
the source sheet before copying, as you suggest, and then close the
source sheet's workbook without saving. James

Dave Peterson wrote:
someworksheetvariable.buttons.delete

where someworksheetvariable represents your new worksheet.

Zone wrote:

I'm using VBA to copy a range from a worksheet in one workbook to a
worksheet in another workbook. The range I'm copying from has buttons
that I don't want to copy. Can I copy the range without copying the
buttons? Alternately, can I delete only the buttons in the copied
range when finished copying (to prevent deleting buttons already on the
destination sheet)? The buttons on both sheets were created with the
Forms menu. ExcelXP (2002). I cannot control the properties of the
buttons in the source file. TIA, James

--

Dave Peterson

I think what Dave meant was that if you have a variable that points to the
worksheet to which you have copied, then on that variable you can call
buttons.delete method.

You can also do what you are suggesting - that is delete from the source
worksheet and then not save the workbook before closing.

"Zone" wrote:

Thanks, Dave. I guess the best strategy is to delete the buttons on
the source sheet before copying, as you suggest, and then close the
source sheet's workbook without saving. James

Dave Peterson wrote:
someworksheetvariable.buttons.delete

where someworksheetvariable represents your new worksheet.

Zone wrote:

I'm using VBA to copy a range from a worksheet in one workbook to a
worksheet in another workbook. The range I'm copying from has buttons
that I don't want to copy. Can I copy the range without copying the
buttons? Alternately, can I delete only the buttons in the copied
range when finished copying (to prevent deleting buttons already on the
destination sheet)? The buttons on both sheets were created with the
Forms menu. ExcelXP (2002). I cannot control the properties of the
buttons in the source file. TIA, James

--

Dave Peterson

Thanks, Alok.

I did mean your first interpretation.

Alok wrote:

I think what Dave meant was that if you have a variable that points to the
worksheet to which you have copied, then on that variable you can call
buttons.delete method.

You can also do what you are suggesting - that is delete from the source
worksheet and then not save the workbook before closing.

"Zone" wrote:

Thanks, Dave. I guess the best strategy is to delete the buttons on
the source sheet before copying, as you suggest, and then close the
source sheet's workbook without saving. James

Dave Peterson wrote:
someworksheetvariable.buttons.delete

where someworksheetvariable represents your new worksheet.

Zone wrote:

I'm using VBA to copy a range from a worksheet in one workbook to a
worksheet in another workbook. The range I'm copying from has buttons
that I don't want to copy. Can I copy the range without copying the
buttons? Alternately, can I delete only the buttons in the copied
range when finished copying (to prevent deleting buttons already on the
destination sheet)? The buttons on both sheets were created with the
Forms menu. ExcelXP (2002). I cannot control the properties of the
buttons in the source file. TIA, James

--

Dave Peterson



--

Dave Peterson

Sometimes I am asked to have a feature by which an end user can select a set
of worksheets and export them to another workbook -- without having to deal
with controls placed on the workbook. I normally tend to write code that will
do that and that would also delete the code modules associated with the
worksheets. Your replay suggests that it is much simpler to delete all the
controls and that will automatically disable the control from being called.
This can only be done, I guess, if the worksheet events are not present. Any
way something for me to keep in mind.
Thanks.

"Dave Peterson" wrote:

Thanks, Alok.

I did mean your first interpretation.

Alok wrote:

I think what Dave meant was that if you have a variable that points to the
worksheet to which you have copied, then on that variable you can call
buttons.delete method.

You can also do what you are suggesting - that is delete from the source
worksheet and then not save the workbook before closing.

"Zone" wrote:

Thanks, Dave. I guess the best strategy is to delete the buttons on
the source sheet before copying, as you suggest, and then close the
source sheet's workbook without saving. James

Dave Peterson wrote:
someworksheetvariable.buttons.delete

where someworksheetvariable represents your new worksheet.

Zone wrote:

I'm using VBA to copy a range from a worksheet in one workbook to a
worksheet in another workbook. The range I'm copying from has buttons
that I don't want to copy. Can I copy the range without copying the
buttons? Alternately, can I delete only the buttons in the copied
range when finished copying (to prevent deleting buttons already on the
destination sheet)? The buttons on both sheets were created with the
Forms menu. ExcelXP (2002). I cannot control the properties of the
buttons in the source file. TIA, James

--

Dave Peterson



--

Dave Peterson