jolivanes
Well-known Member
- Joined
- Sep 5, 2004
- Messages
- 2,768
- Office Version
- 2013
- 2007
- Platform
- Windows
In the below part of the code, I have a UserForm ComboBox in which I highlight (select) a name.
Next I will click on one of 5 buttons that have colors as captions.
The relevant color shapes are very small, the height of a regular cell height and somewhere around one quarter of the width of a regular cell, and are in another sheet.
For now, the sheet name where these colored shapes are located is a sheet named "Pictures" (no double quotes)
The color shape needs to be copied from the sheet where they are and pasted into the cell with the name that I selected in the ComboBox.
It alls works good with the odd exception when it comes up with an error stating "Paste method of worksheet class failed."
For days on end, it works without problems and then out of the blue it will error.
I have tried with copy and paste, copy and pastespecial, with and without Do Events and whatever I found by googling.
The current way, "Duplicate.Cut", is the least problematic so far but still errors every so once in a while.
I have not been able to find a surefire way that will not error.
If I save the shapes in a folder and use "Pictures.Insert" I don't get the error message but for convenience sake I would prefer to copy/paste between sheets.
Is there a guaranteed way that will work?
Thanks in advance.
Next I will click on one of 5 buttons that have colors as captions.
The relevant color shapes are very small, the height of a regular cell height and somewhere around one quarter of the width of a regular cell, and are in another sheet.
For now, the sheet name where these colored shapes are located is a sheet named "Pictures" (no double quotes)
The color shape needs to be copied from the sheet where they are and pasted into the cell with the name that I selected in the ComboBox.
It alls works good with the odd exception when it comes up with an error stating "Paste method of worksheet class failed."
For days on end, it works without problems and then out of the blue it will error.
I have tried with copy and paste, copy and pastespecial, with and without Do Events and whatever I found by googling.
The current way, "Duplicate.Cut", is the least problematic so far but still errors every so once in a while.
I have not been able to find a surefire way that will not error.
If I save the shapes in a folder and use "Pictures.Insert" I don't get the error message but for convenience sake I would prefer to copy/paste between sheets.
Is there a guaranteed way that will work?
Thanks in advance.
Code:
With ThisWorkbook.Sheets("Pictures")
.Shapes(a).Duplicate.Cut '<---- a = UserForm1.ActiveControl.Caption
DoEvents
.Paste Destination:=ActiveSheet.Columns(3).Find(UserForm1.ComboBox1, , , 1)
End With
With Selection
.Name = a '<---- a = UserForm1.ActiveControl.Caption
.Top = Columns(3).Find(UserForm1.ComboBox1, , , 1).Top
.Left = Columns(3).Find(UserForm1.ComboBox1, , , 1).Left + Columns(3).Width - Selection.Width * j '<----- j is just a width multiplier
End With