copying and pasting objects in VBA

[Joker4321]

Hi, I have a range of cells I want to copy which has some textboxes and check boxes in it. When I do a copy/insert cells in vba it only copies the cells and their values but not any of the boxes. Any Ideas how I can copy everything?

Here is my code:
.Range(.Cells(Origin.Row + 1, Origin.Column), .Cells(Origin.Row + 12, Origin.Column + 10)).EntireRow.Copy

.Range(.Cells(Ind.Row, 3), .Cells(Ind.Row, 3)).EntireRow.Insert Shift:=xlDown

Thanks.

 

[Datsmart]

Set the object properties to either "Move and size with cells" or "Move but don't size with cells". If you are set to "Don't move or size with cells it will not copy.

 

[Joker4321]

That option is enabled and if I copy and paste manually it works fine but if try it with VBA or manually while I am recording a macro it only copies the cells and not the objects.

 

[Datsmart]

In my test page this code will copy a checkbox from the active row to the new destination.

ActiveCell.EntireRow.Copy Range("A10")

Could you post more of your code? Need to understand the variables you are using.

 

[Joker4321]

Here you go:

Dim TblOrigin As Range
Dim InsTableInd As Range
DimNewTblOrigin As Range





Set TblOrigin = Sheets("Financial").Cells.Find(What:="AddExpStart", LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
True)

Set InsTableInd = Sheets("Financial").Cells.Find(What:="AddExpEnd", LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
True)

With Sheets("Financial")
'Copy first table
.Range(.Cells(TblOrigin.Row + 1, TblOrigin.Column), .Cells(TblOrigin.Row + 13, TblOrigin.Column + 10)).Copy

'Insert the copied table at the InsTableInd row and shift cells down
.Range(.Cells(InsTableInd.Row, 3), .Cells(InsTableInd.Row, 3)).Insert Shift:=xlDown

End With

 

[Joker4321]

It seems like I can only copy paste objects manually when design mode is enabled. If it's disabled only the cells get copied and it is always disabled when a macro runs.

 

[Datsmart]

Since you mention "Design Mode", you are using Active X objects from the Control Toolbox.
I am using a checkbox from the Forms Menu.
I setup a page with your code and some data with Forms checkboxes. My checkboxes copied fine with your code.
I like to use the Textbox from the Drawing Toolbar. Easy to setup colors and other properties. Neither the Forms or the Drawing toolbar need to use design mode, so you avoid those issues that Control objects have.

If you must use Control objects, you will have to setup code to select and copy your objects separate from your cell range copy and paste code.

 

[Joker4321]

It seems to work if I group all of the objects together but then as soon as I paste I get an error "Can't enter break mode at this time". Hope this helps

 

[Joker4321]

that did the trick. I never knew there were different types of objects. Is there a simple way to clear the contents of the textboxes that are copied?

Thanks for all your help Datsmart!

 

[Datsmart]

Are you wanting to remove the Textboxes or just their Text?
Manual steps or code?
To change their text you will have to work with each by name, group name or a particular range. Please explain exactly what situation you have and what you want done.