copying and pasting objects in VBA

Joker4321

Board Regular
Joined
Jan 5, 2007
Messages
60
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.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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

Board Regular
Joined
Jan 5, 2007
Messages
60
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

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
In my test page this code will copy a checkbox from the active row to the new destination.
Code:
ActiveCell.EntireRow.Copy Range("A10")
Could you post more of your code? Need to understand the variables you are using.
 

Joker4321

Board Regular
Joined
Jan 5, 2007
Messages
60

ADVERTISEMENT

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

Board Regular
Joined
Jan 5, 2007
Messages
60
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

Well-known Member
Joined
Jun 19, 2003
Messages
7,985

ADVERTISEMENT

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

Board Regular
Joined
Jan 5, 2007
Messages
60
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

Board Regular
Joined
Jan 5, 2007
Messages
60
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

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,160
Messages
5,768,527
Members
425,480
Latest member
br400821

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top