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()
    UserForm1.PrintForm
    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
    Else
    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)
    FormName.Show
    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


    Graham

    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?


    Graham

    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.


    Graham

    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


    Graham

    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

    Code
    1. Private Sub LoadIt()
    2. I = 1
    3. For Each Control In Me.Controls
    4. With Me
    5. Control.Caption = Range("A" & I)
    6. I = I + 1
    7. End With
    8. Next Control
    9. End Sub


    Thanks

    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
    Else
    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


    Graham