Posts by Wistow

    Hi Putman Thanks for the reply

    Yes I have looked at breaking it up and XLDennis has been helping me with code for loading forms using a variable name

    Yes 10 of the pages are identical except for the questions(lables) against the option buttons the responses from the user and the title.
    I have not looked at a tabcontrol, I will look in the help file and I had not even thought about just reloading the controls on the same page, the questions are currently on a sheet in the workbook and loaded when the form is initalized

    Back to the drawing board

    thanks Graham

    There is a print form option could you redirect this

    PrintForm prints all visible objects and bitmaps of the UserForm object. PrintForm also prints graphics added to a UserForm object.

    The printer used by PrintForm is determined by the operating system's Control Panel settings.

    Private Sub Print_This_Form()
    End Sub

    Hi Dennis

    Thanks for you reply I have been searching everywhere for this answer

    However I never think about searching the MS knowledge base

    That link and the posted code have help me a lot.

    What is the benefit of loading the forms into memory, this project will be opened only once by each of 5000+ people who will work through it 1 form at a time and probably not needing to go back. I was intending to show, allow completion and then unload each form, reloading if needed

    Thanks Graham

    Hi Dennis

    The code is very "learner" there are bound to be ways to tidy it up but it works up to the point of loading the form, as can be seen from the code I have tried different ways to load the form

    Option Base 1
    Public Form_To_Show(11)

    Sub Form_To_Show_Array()
    i = 4
    j = 10
    Form_To_Show(1) = "About"
    Form_To_Show(2) = "Windows"
    Form_To_Show(3) = "Remote"
    For j = 10 To 17
    If Worksheets("Summary").Cells(2, j).Value = "" Then
    Form_To_Show(i) = Worksheets("Summary").Cells(2, j).Value
    i = i + 1
    End If
    Next j
    Form_To_Show(i) = "Finish"
    End Sub

    Function NextPage(Page_Number)
    FormName = Form_To_Show(Page_Number + 1)
    End Function

    Function PreviousPage(Page_Number)
    Load (Form_To_Show(Page_Number - 1))
    End Function

    Sub test()
    NextPage 3
    End Sub

    Sub test2()
    PreviousPage 5
    End Sub

    There is:

    1 Userform having:-
    1 multipage control with 11 pages
    page 1 has:-
    10 labels, 10 textboxes, 3 frames 4option buttons, 9 checkboxes and 1 command button
    pages 2-10 have on each page:-
    20 frames, 20 labels, 80 option buttons and 1 command button

    The spread sheet has 3 sheets with no more than 300 cells containing data no formulas or formating

    In all a lot of objects


    This is the situation

    a number(num) is passed to a function

    in the function
    an array with a list of userform names
    the number(num) is used to get the name of the userform at that position in the array
    the name is used to load/show the userform

    I can do everything up to the point of loading the userform, I get object required

    Any Ideas

    Thanks Graham

    I can add as many pages as I want up to and including page 11.

    If I add option buttons to any pages 8 to 11 I get the error

    Is there a maximun number of objects/items or maximun number of 1 type of object that VBA or excel can handle?


    Hi Jack

    Thanks for the response

    Followed your suggestion, and I can add page 8 and more without any problem, however the moment I add the group of option buttons I get the error again.


    Thats what I needed !

    I have just searched the help file again and TypeName is only shown as "to return information about a variable" what else can you use TypeName for?

    Thanks Dave

    PC = AMD 1700 with 512mb memory, 40gb Disk with 20gb free

    Windows XP pro
    TSR apps pop-up-ad filter and SpIDer Guard running

    Only Excel and VBA interface

    The problem ocours in the VBA interface in design mode when swapping between code and drawing of userform.

    I copied the file and then started taking pages off the multipage control and when I got to 7 of the 11 the fault stopped

    I don't want to go to separate userforms as selections made on the first page selects which pages show and takes the user through them which is easy on a multipage

    Thanks Graham

    Thnks for the reply Dave

    If I understand correctly I have to put somthing into the tag property when I put each label onto the page.

    If this is so, I have a problem that all of my 200+ labels are already in place.

    Is there a labels collection that I can access

    Otherwise I will have to use an If & Left statement with the current names which are all "LabelXX" where XX is a number

    Thanks Graham

    I am trying to create an employee survey which I intend to email to 5000 employees for completion and then get it emailed back to me for analysis

    I have a workbook with very little content( it just stores the responsesfrom the userform) and 1 userform

    The userfom has a multipage control, there are 11 pages, each page having :-

    25 frames, with 1 label and 4 option buttons in each frame
    and 1 command button per page.

    and now surprise surprise I am getting an out of memory error

    What can I do to get past this?

    Thanks Graham

    Try this

    Sub MakeProper()
    Dim MyRange
    Dim LastCell, MyCounter
    Set MyRange = ActiveSheet.Range(ActiveWindow.Selection.Address)
    LastCell = MyRange.Cells.Count
    For MyCounter = 1 To LastCell
    If Not MyRange.Cells(MyCounter).HasFormula Then
    MyRange.Cells(MyCounter) = Application.Proper(MyRange.Cells(MyCounter))
    End If
    Next MyCounter
    End Sub


    I have created a multipage userform and on each page there are going to be 20 sets of four option buttons, each set has a label. There are also other controls on each page

    Currently the labels are all stored on a sheet ie multipage.page1 labels are on sheet1 "A1:A20"

    My question is how do I rename the labels with the contents of the sheet, I have tried the following but other controls get renamed as well

    Private Sub LoadIt()
          I = 1
          For Each Control In Me.Controls
                With Me
                Control.Caption = Range("A" & I)
                I = I + 1
                End With
        Next Control
    End Sub


    I have found some answers and also another question

    multipage1.value = 1 'to move to the 2nd page linked to a command button
    Check boxes
    Private Sub CheckBox1_Click()
    If CheckBox1 Then
    MultiPage1.Pages(3).Visible = True
    MultiPage1.Pages(3).Visible = False
    End If
    End Sub

    New Questions
    Can I hide the tabs at the top?
    how do i make the userform fill the screen no matter what the resolution?
    my thoughts are to maximinise excel (fullscreen) and then make the userform the same size as the excel window, I can make excel full screen but I have no idea on making the userform the same size