pasting controls

[Gixxer_J_97]

hi all,

i have a multipage (MultiPage1) with controls on it (labels, textboxes and
combo boxes).

page1 (index 0) will always be there.

this function should add a new page, and copy all the controls from the
first page, and paste them on to the newly added page:

Private Sub AddAnother_Click()
MultiPage1.Pages.Add "Page" & (MultiPage1.Pages.Count + 1), "Address " &
(MultiPage1.Pages.Count + 1), 1
MultiPage1.Pages(1).Controls.Copy
Dim newPage As Object
Set newPage = MultiPage1.Pages.Item((MultiPage1.Pages.Count - 1))
newPage.Paste
End Sub

i am getting an error on newPage.Paste
Run-time error '-2147024809 (80070057)
Could not paste the control. Invalid argument.

what am i missing?

tia

J

 

[Vasant Nanavati]

I don't believe you can paste directly onto a MultiPage control at runtime.

 

[Gixxer_J_97]

according to the documentation you can -
unless i'm looking at something incorrectly

search for multipage in vba help
multipage control (forms) -> Example

Add, Cut, Paste methods....

 

[Vasant Nanavati]

I see only Move, SetFocus and ZOrder. Different Excel versions, perhaps? I'm
using 2002.

 

[Gixxer_J_97]

perhaps - im using 2003

here's the help listing:
Add, Cut, Paste Methods, Page Object, MultiPage Control Example

The following example uses the Add, Cut, and Paste methods to cut and paste
a control from a Page of a MultiPage. The control involved in the cut and
paste operations is dynamically added to the form.

This example assumes the user will add, then cut, then paste the new control.

To use this example, copy this sample code to the Declarations portion of a
form. Make sure that the form contains:

Three CommandButton controls named CommandButton1 through CommandButton3.


A MultiPage named MultiPage1.
Dim MyTextBox As Control

Private Sub CommandButton1_Click()
Set MyTextBox = MultiPage1.Pages(MultiPage1.Value).Controls_
.Add("MSForms.TextBox.1", "MyTextBox", Visible)
CommandButton2.Enabled = True
CommandButton1.Enabled = False
End Sub

Private Sub CommandButton2_Click()
MultiPage1.Pages(MultiPage1.Value).Controls.Cut
CommandButton3.Enabled = True
CommandButton2.Enabled = False
End Sub

Private Sub CommandButton3_Click()
Dim MyPage As Object
Set MyPage = _
MultiPage1.Pages.Item(MultiPage1.Value)

MyPage.Paste
CommandButton3.Enabled = False
End Sub

Private Sub UserForm_Initialize()
CommandButton1.Caption = "Add"
CommandButton2.Caption = "Cut"
CommandButton3.Caption = "Paste"

CommandButton1.Enabled = True
CommandButton2.Enabled = False
CommandButton3.Enabled = False
End Sub

 

[Vasant Nanavati]

You are correct. I was thinking of the MultiPage control itrself rather than
one of its pages. I'm getting the same error; I'll look at it again tomorrow
when I'm more awake .

 

[Peter T]

I know Vasent was going to look at this, but I was intrigued and hope he
won't mind if I butt in.

I'm pretty sure the reason your code fails is because when you add the new
page it is now Page(1), previous Page(1) becomes Page(2) ie the third page.
You then copy the new page(1) which of course has no controls.

Assuming you want to add the new page as the last page, try this:

Private Sub CommandButton1_Click()
Dim newPage As Page
Dim nPages As Long

With Me.MultiPage1
nPages = .Count
Set newPage = .Pages.Add("Page" & (nPages + 1), _
"Address " & (nPages + 1), nPages)
.Pages(1).Controls.Copy
End With

newPage.Paste

'ActiveCell.Copy
Application.CutCopyMode = False
End Sub

Tested in Xl97 & XL2K

Strangely, CutCopyMode = False does not appear to remove the copied controls
from the clipboard. So you may want to uncomment the ActiveCell.Copy line to
achieve this.

I assume you have addressed how to add new event code to the pasted
controls.

Regards,
Peter T

 

[Gixxer_J_97]

Hi Peter,

thanks for the input!

If my thinking is correct - and it's highly likely that it wasn't -
initially there is 1 page (page1) - when the next page is added it becomes
multipage1.pages.count+1 (page2)

and i *always* want to copy the controls from page1 (not the previous page)

heh - actually i just figured it out - i need to do:
(changed Pages(1) to Pages(0) - forgot about how it indexes)

MultiPage1.Pages.Add "Page" & (MultiPage1.Pages.Count + 1), "Address " &
(MultiPage1.Pages.Count + 1), 1
MultiPage1.Pages(0).Controls.Copy
Dim newPage As Object
Set newPage = MultiPage1.Pages((MultiPage1.Pages.Count - 1))
newPage.Paste


and the clearing controls - i thought it 'reset' them to empty. it removes
them all =)

thanks for the tipoff!

J

 

[Gixxer_J_97]

hmmm - this works for the first add.
after, it will add the new page to the right of the first (initial) page -
and not at the end where it should be.
it also copies/pastes the controls the first time - each succesive time it
pastes no controls

Initial (tabs of the multipage):
Address 1

after first add: (controls copied ok)
Address 1 Address 2

After the second add: (controls ok on 1 and 2, but 3 is 'empty')
Address 1 Address 3 Address 2

After the third add: (controls ok on 1 and 2 but 3 and 4 are 'empty')
Address 1 Address 4 Address 3 Address 2

etc etc

i'm going to look into your code a little more Peter and see if maybe
that'll fix it.

in the mean time - if there are any ideas.... greatly appreciated!

thanks!

J

 

[Gixxer_J_97]

Peter - you're a genius!

thanks - that worked perfectly (if only i'd tried your code first =) )

thx again

J