Question for Peter T - Copy Paste controls at runtime

[Geoff]

I would appreciate further comment re your solution to copy pasting controls
and your code:

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
End Sub

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

I have 2 questions
1. Using the above, I find each control is pasted to the left of the
original position. They are alll drawn relative to each other just shifted
to the left. How would you correct this?
2. How do you sub-class the event code for each control collection?

I would be very grateful if you could expand a little on your previous
comments

Geoff

Code as follows:

For a cmdbutton on a page I have in the form code:
Private Sub cmdDeleteChanges_Click()
'''clear controls
ClearControls (MultiSetasides.Value)
'''set focus on first empty control
FirstControl (MultiSetasides.Value)
End Sub
Then in a standard module I have:
Sub FirstControl(k As Byte)

Dim i As Byte

'''ignore main page
If Not frmMulti.MultiSetasides.Value = 0 Then
Set ctrl = frmMulti.MultiSetasides.Pages(k).Controls
For i = 0 To 9
If (TypeOf ctrl.Item(i) Is msForms.TextBox) Then
If Trim(ctrl.Item(i).Text) = "" Then Exit For
ElseIf (TypeOf ctrl.Item(i) Is msForms.OptionButton) Then
If ctrl.Item(i).Value = False And ctrl.Item(i + 1).Value =
False Then Exit For
End If
Next i
ctrl.Item(i).SetFocus
End If
End Sub

Sub ClearControls(k As Byte)
Dim i As Byte
'''ignore main page
If Not frmMulti.MultiSetasides.Value = 0 Then
Set ctrl = frmMulti.MultiSetasides.Pages(k).Controls
For i = 1 To 9
If (TypeOf ctrl.Item(i) Is msForms.TextBox) Then
ctrl.Item(i).Text = ""
ElseIf (TypeOf ctrl.Item(i) Is msForms.OptionButton) Then
ctrl.Item(i).Value = False
End If
Next i
End If
End Sub

 

[Peter T]

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

controls. ''

I first read that as you asking me but tracking down the thread I see I
posed it to the OP
http://tinyurl.com/ddy3e

Indeed you'd need to add and set withevents (w/e) classes for each control
type, with the class objects added to a module or global level collection
(could be an array). In the form's initialize event set for existing pages &
controls.

After pasting the page with the newly 'copied' controls, add w/e classes for
the new controls with whatever other properties (eg page name/no, control
id, etc) with similar code as used in the init event.

The event code might adopt a Select Case approach for both for the
control name & page index.

Bear in mind not all controls expose the full set of events in a w/e class
as
are available in a userform.

I recall the shifting controls problem and thought I found a way to prevent
that, not sure now. Otherwise store the left & top properties of controls on
the page to be copied in a 2D array and reapply same (in array order) to the
new controls. If controls are in a frame would only need to reset the
frame's position.

Revert back if you get stuck.

Regards,
Peter T

 

[Geoff]

Thanks for the reply. I have 1 succes and 2 failures
Firstly I have been able to reposition the pasted controls with:
For Each ctrl In newPage.Controls
ctrl.Move ctrl.Left + 48, ctrl.Top + 12
Next
Perhaps inelegant but effective.
Secondly a serious error - 'Automation Error. Object disconected from
clients'
I thought to tr y writng event code to the form module but though there in't
a crash at first, as soon as I try to navigate away from the new page I get
the error.
Thirdly whilst I remain on the new page the cmdbutton event code does not
fire. I think possibly because they will not have the same name. I have
tried to follow the advice on Chip Pearson's page and called the write
procedure using an Ontime event.

I would appreciate any advice you can give though I am afraid setting w/e
classes for each control is language I am not familiar with - sorry.
My code follows:

Private Sub cmdEdit_Click()

Dim i As Byte, j As Integer

'''ensure Setaside selection
If lboSetAsides.ListIndex <= 0 Then
MsgBox "Please select a Setaside "
lboSetAsides.SetFocus
Exit Sub
End If

'''set up pages and populate controls with data
If numoPages = 0 Then
With MultiSetasides.Pages(1)
.Visible = True
.Caption = "Ref " &
Trim(lboSetAsides.List(lboSetAsides.ListIndex, 0)) '''Rename page caption
.Controls(0).Text =
Trim(lboSetAsides.List(lboSetAsides.ListIndex, 1)) '''Setaside name
.Controls(6).Text =
Left(lboSetAsides.List(lboSetAsides.ListIndex, 2),
Len(lboSetAsides.List(lboSetAsides.ListIndex, 2)) - 3) '''Balance Pounds
.Controls(7).Text =
Right(lboSetAsides.List(lboSetAsides.ListIndex, 2), 2) '''Balance Pence
.Controls(8).Text = Format(Date, "dd mmm yyyy")
End With
numoPages = 1
ElseIf numoPages > 0 Then
'''determine if selected setaside has a page
For i = 1 To MultiSetasides.Pages.Count - 1
If Trim(lboSetAsides.List(lboSetAsides.ListIndex, 0)) =
Mid(MultiSetasides.Pages(i).Caption, 5) Then
j = j + 1
Else
j = j - 1
End If
Next
'''create page if not selected before
If j < 0 Then
With MultiSetasides
Set newPage = .Pages.Add(, "Ref " &
(lboSetAsides.ListIndex), .Count)
.Pages(1).Controls.Copy
newPage.Paste
'''adjust position of controls
For Each ctrl In newPage.Controls
ctrl.Move ctrl.Left + 48, ctrl.Top + 12
Next
newPage.Picture = MultiSetasides.Pages(1).Picture
'''clear controls
ClearControls (MultiSetasides.Pages.Count)
End With
'''clear clipboard
ActiveCell.Copy
Application.CutCopyMode = False
'''populate controls
lastPage = MultiSetasides.Pages.Count - 1
With MultiSetasides.Pages(lastPage)
.Caption = "Ref " &
Trim(lboSetAsides.List(lboSetAsides.ListIndex, 0)) '''Rename page caption
.Controls(0).Text =
Trim(lboSetAsides.List(lboSetAsides.ListIndex, 1)) '''Setaside name
.Controls(6).Text =
Left(lboSetAsides.List(lboSetAsides.ListIndex, 2),
Len(lboSetAsides.List(lboSetAsides.ListIndex, 2)) - 3) '''Balance Pounds
.Controls(7).Text =
Right(lboSetAsides.List(lboSetAsides.ListIndex, 2), 2) '''Balance Pence
.Controls(8).Text = Format(Date, "dd mmm yyyy")
End With
numoPages = numoPages + 1

Setup '''''''''''''proc to write module code

End If

End If

'''goto page selected
For i = 1 To MultiSetasides.Pages.Count - 1
If MultiSetasides.Pages(i).Caption = "Ref " &
Trim(lboSetAsides.List(lboSetAsides.ListIndex, 0)) Then
MultiSetasides.Value = i
FirstControl (i)
Exit For
End If
Next
End Sub


Sub Setup()
Application.OnTime Now, "AddProcedure"
End Sub


Sub AddProcedure()
Dim VBCodeMod As CodeModule
Dim LineNum As Long
Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("frmMulti").CodeModule
With VBCodeMod
LineNum = .CountOfLines + 1
.InsertLines LineNum, _
"Private Sub cmdDeleteChanges" & MultiSetasides.Pages.Count & "_Click()" &
Chr(13) & _
" ClearControls (MultiSetasides.Value)" & Chr(13) & _
" FirstControl (MultiSetasides.Value)" & Chr(13) & _
"End Sub"
End With

End Sub

 

[Peter T]

Difficult to follow your code without creating a similar form + same name
controls.

When you get that automation error are you still using the same newPage
object. If so work with a new ref to the page, if the page is the last page
it would be pages.count - 1

Thirdly whilst I remain on the new page the cmdbutton event code does not
fire. I think possibly because they will not have the same name.

It won't be the same name but if you've correctly added it to the a new w/e
class it event code should fire. Store an additional property in the class,
eg

Public sCtlName

and at the same time as setting the control object ref in the class assign
sCtlName with its name. Set an appropriate break and step through.

Regards,
Peter T

 

[Geoff]

Peter
I am strruggling to understand and thanl you for staying with this. But to
answer the first point - if I click on another tab after creating the new
page I get the automation error. If however I put a break on the
MultiSetasides_Change() event and step through the code then it remains ok.
The cmdbutton still does not work because the event code has not been
written because when stepping through I get a msgbox to say 'Can't execute in
break mode'. This becomes a circular argument from which I do not have
enough knowledge yet to break out of.

I hesitate to ask but wondered if I sent my wbook whether you would be kind
enough to look through? I would not expect you to do anything other than
indicate what I should do. I do appreciate the concerns about viruses etc or
you simply do not have the time but the solution I am trying to provide for
my son is a worthwhile goal. And I cannot be the first to have had this sort
of issue with multipages but there i sprecious little on the i'net to learn
from.

Regards

Geoff

 

[Peter T]

OK send your workbook. Can't guarantee I can fix it but I'll have a look.

Regards,
Peter T
pmbthornton gmail com

 

[Geoff]

Peter
That's kind of you. I will 'tidy' it up a bit first as you will apppreciate
this is still in an experimental state. It is already reasonably well
commented. I will put together an email laying out the aims.

Geoff