Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Don't copy buttons
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Don't copy buttons
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Don't copy buttons
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Don't copy buttons
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Don't copy buttons
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Don't copy buttons
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Don't copy buttons
That's one of the very nice things about using the controls from the Forms
toolbar. The macros associated with those controls would be in a general module. So you don't have to worry about those _click events that come with the controls from the Control toolbox toolbar. Chip Pearson does share code showing how to write code that cleans up code: http://cpearson.com/excel/vbe.htm But sometimes, it's just easier copy|pasting the cells (formulas, values, formatting...). Or even create a template and just paste the values/formulas into a new workbook based on that template. Alok wrote: 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 -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Don't copy buttons
Thanks again, Dave and Alok. Maybe I didn't state this point very
well. If my destination sheet already has buttons on it and I copy more from the source sheet, I only want to delete the copied ones, leaving the buttons that were originally on the destination sheet. That's why I was wondering if it would be possible to delete only the buttons within the copied range after it was pasted. Deleting the buttons from the source sheet before copying, then pasting and closing the source workbook without saving it working fine for me, though. James Dave Peterson wrote: That's one of the very nice things about using the controls from the Forms toolbar. The macros associated with those controls would be in a general module. So you don't have to worry about those _click events that come with the controls from the Control toolbox toolbar. Chip Pearson does share code showing how to write code that cleans up code: http://cpearson.com/excel/vbe.htm But sometimes, it's just easier copy|pasting the cells (formulas, values, formatting...). Or even create a template and just paste the values/formulas into a new workbook based on that template. Alok wrote: 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 -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Don't copy buttons
I think I'd copy the cells and paste special--formulas, values or formats (or a
combination of them). Then the buttons wouldn't travel with the copy|pasting. Zone wrote: Thanks again, Dave and Alok. Maybe I didn't state this point very well. If my destination sheet already has buttons on it and I copy more from the source sheet, I only want to delete the copied ones, leaving the buttons that were originally on the destination sheet. That's why I was wondering if it would be possible to delete only the buttons within the copied range after it was pasted. Deleting the buttons from the source sheet before copying, then pasting and closing the source workbook without saving it working fine for me, though. James Dave Peterson wrote: That's one of the very nice things about using the controls from the Forms toolbar. The macros associated with those controls would be in a general module. So you don't have to worry about those _click events that come with the controls from the Control toolbox toolbar. Chip Pearson does share code showing how to write code that cleans up code: http://cpearson.com/excel/vbe.htm But sometimes, it's just easier copy|pasting the cells (formulas, values, formatting...). Or even create a template and just paste the values/formulas into a new workbook based on that template. Alok wrote: 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 -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Don't copy buttons
Yes, that would be the best way to do it. Thanks! In a moment of
laziness, I will just clear the buttons, copy and not save before closing. Doesn't look that risky....runs fast. Regards, James Dave Peterson wrote: I think I'd copy the cells and paste special--formulas, values or formats (or a combination of them). Then the buttons wouldn't travel with the copy|pasting. Zone wrote: Thanks again, Dave and Alok. Maybe I didn't state this point very well. If my destination sheet already has buttons on it and I copy more from the source sheet, I only want to delete the copied ones, leaving the buttons that were originally on the destination sheet. That's why I was wondering if it would be possible to delete only the buttons within the copied range after it was pasted. Deleting the buttons from the source sheet before copying, then pasting and closing the source workbook without saving it working fine for me, though. James Dave Peterson wrote: That's one of the very nice things about using the controls from the Forms toolbar. The macros associated with those controls would be in a general module. So you don't have to worry about those _click events that come with the controls from the Control toolbox toolbar. Chip Pearson does share code showing how to write code that cleans up code: http://cpearson.com/excel/vbe.htm But sometimes, it's just easier copy|pasting the cells (formulas, values, formatting...). Or even create a template and just paste the values/formulas into a new workbook based on that template. Alok wrote: 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 -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What keyboard has buttons of cut, copy and paste on it? | Excel Discussion (Misc queries) | |||
Copy buttons by rows | New Users to Excel | |||
How to copy a workbook so the buttons run the macros? | Excel Discussion (Misc queries) | |||
copy sheet with buttons | Excel Programming | |||
Do not copy buttons or macros | Excel Programming |