Solved

Excel 2003 Multipage Copy

Posted on 2013-01-24
2
328 Views
Last Modified: 2013-02-03
Hello,

I have a multipage which has a list of transactions, what I want to do based on the types of transactions that are in the list box I want to create sub pages for each transaction type and copy the list box over to the name page and filter the list box data based on the transaction type.

I was trying to use something like this to copy everything over but it is not working.

    With CorpBndProfile.MultiPage1
        PagesCnt = .Count
        Set newPage = .Pages.Add("Page" & (PagesCnt + 1), _
        "Page " & (PagesCnt + 1), PagesCnt)
        .Pages(PagesCnt).Controls.Copy
    End With
    newPage.Paste
0
Comment
Question by:Jordan_WM
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 14

Accepted Solution

by:
Faustulus earned 300 total points
ID: 38822035
I found a site on the web where they claimed that your copy/paste idea would work if you place all controls within a frame. It didn't work for me. Therefore I created code that copies each control. I tested this code on a userform with a multipage textbox and a CommandButton1 on it.
Option Explicit
 
Private Sub UserForm_Initialize()
 CommandButton1.Caption = "Add page"
End Sub
 
Private Sub CommandButton1_Click()

    Dim PageCnt As Long
    Dim Ctl As Control
    Dim NewCtl As Control

    With MultiPage1.Pages
        PageCnt = .Count
        .Add "Page" & (PageCnt + 1), "Page " & (PageCnt + 1)
        For Each Ctl In .Item(PageCnt - 1).Controls
            Set NewCtl = .Item(PageCnt).Controls.Add( _
                         "Forms." & TypeName(Ctl) & ".1", Ctl.Name)
            SetControlProperties NewCtl, Ctl
        Next Ctl
    End With
End Sub

Private Sub SetControlProperties(NewCtl As Control, _
                                 Ctl As Control)
    
    With NewCtl
        .Top = Ctl.Top
        .Left = Ctl.Left
        .Height = Ctl.Height
        .Width = Ctl.Width
        .Visible = True
        Select Case TypeName(Ctl)
            Case "TextBox"
                .Text = Ctl.Text
            Case "CheckBox"
                .Value = Ctl.Value
        End Select
    End With
End Sub

Open in new window

Run the Initialize procedure to repeat the test.

Once the new control has been created the sub SetControlProperties is called. In it you can copy all relevant properties from the original control to its copy. I just added some to show the principles. Not all types of controls have all available properties. To avoid runtime errors caused by trying to set properties that don't exist use the Select statement to set control-specific properties only for those controls that have them.

Beware of the index numbers of the pages! Pages are counted 1-based but they are indexed 0-based. So, your initial 2 pages are indexed 0 and 1. I didn't program the index number because VB will add it automatically, but if you do it shouldn't be PageCnt -1.

Let me know if you need any more help with this.
0
 

Author Closing Comment

by:Jordan_WM
ID: 38849388
Excellent thank you very much thats what I was looking for :-)
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question