Posts by Wistow

I expect you have already considered it but why not put the names across the top

W

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

Thanks AJW

As i understand it the link relates to controls placed onto a sheet, I can't see if it also relates to userforms as well

Regards Graham

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

Here is a link to a site created by Rodney Powell Microsoft MVP which has formulas and information about YTD formulas

http://www.beyondtechnology.com/tips010.shtml

Hope it helps 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

Thanks Dennis
That was quick and gives me a starting point

Graham

How do I access the userforms collection?

I have searched everywhere with out any success

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

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

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