multipage forms

I am trying to develop a multipage form. I need the names of 10 pages to come from sheets(“WORK”).Range(“A3:A12”)(therefore the pages should be named when the form is initialized). Sometimes some of these cells are empty. In these cases I want the corresponding multipages to be invisible.

using excel 2007 vista

Is this at all possible?

Thanks

Max

I don't see any difficulty with that. :)

You can set the pages .Caption and .Visible properties via the MultiPage's .Pages collection depending on the values in a range. If the range is always the same size, as you say it is, that's all you need to do after setting up the pages at design-time.

Does that help, or were you having a problem with any particular step?

I'd also like to ask what you're planning to put on each of these 10 pages? Sounds like your form is going to get complicated very quickly?

Thanks for the replies. I can take catre of the visibility part, but I can't come up with the code to name the page when the form is initialized.

There will only be three text boxes on each page. The user will enter info in these boxes.

Thanks

Max

Thanks for the replies. I can take catre of the visibility part, but I can't come up with the code to name the page when the form is initialized.

There will only be three text boxes on each page. The user will enter info in these boxes.

Thanks

Max

Hi Max,

Just like Cas said - assign the value of the relevant cell to the caption of the page, eg.


Private Sub UserForm_Initialize()
Me.MultiPage1.Pages(0).Caption = Worksheets("Source").Range("A1").Value
End Sub


Just adapt for a loop and away you go!!

Colin

Thanks, that worked great. Now I can't remember how i got the individual pages to be visible or invisible. Any help would be appreciated.

Max

Hi Max,

Determine if the relevant range is empty or not, and then toggle the object's visible property as necessary.

eg.

MultiPage1.Pages(0).Visible = False

Wheteher it is visible depends upon what is in the range that named the pages. If the range for that particular page is blank then the page need to not be visible. This must be coded to be determined at run time.

Thanks

Max

Are you familiar with the If-statement (http://msdn.microsoft.com/en-us/library/aa716199(VS.60).aspx)?

If you are, I think we're missing something here, because this really seems quite straightforward... :confused:

This is what I am trying to do. But it doesn't work.

Private Sub UserForm_Initialize()
''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''
'''''NAME FIRST PAGE
'''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''
If Sheets("WORK").Range("A3") = "" Then
MultiPage1.pages0.Visible = False
Else
Me.MultiPage1.Pages(0).Caption = Worksheets("WORK").Range("A3").Value
End If
''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''
'''''NAME SECOND PAGE
'''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''

Private Sub UserForm_Initialize()
''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''
'''''NAME FIRST PAGE
'''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''
If Sheets("WORK").Range("A3") = "" Then
MultiPage1.pages0.Visible = False
Else
Me.MultiPage1.Pages(0).Caption = Worksheets("WORK").Range("A3").Value
End If
''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''
'''''NAME SECOND PAGE
'''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''

(emphasis mine)

You can set the pages .Caption and .Visible properties via the MultiPage's .Pages collection
Just a tip Max, when you see people refer to properties and methods in explanations, it's much quicker to go to the VB editor, type in the word, and hit F1 than it is to post back asking for a definition. That's what MS made the help files for after all, plus you might spot something else which will come in handy ;)

Thanks for the replies. I have used the help files but it does not explain what I am trying to do, or I can't grasp it.

The above code will name the pages, but it want make the unnamed pages not visible.

Thanks

Max,

Pay some careful attention to the example I posted in #7 and line 2 of the code you posted in #10.

MultiPage1.Pages(0).Visible = False
MultiPage1.pages0.Visible = False
Can you see any differences?

Colin

I can see that you have an () around your 0 and I don't. But I have tried it with the () and it doesn't make the page invisible. I am sorry to be such a pain, and I appreciate your patience.

Max

I can see that you have an () around your 0 and I don't. But I have tried it with the () and it doesn't make the page invisible. I am sorry to be such a pain, and I appreciate your patience.

Max

What does it do then? Nothing? Are you getting an error? What is the error? What else have you tried?

Step through your code using F8 to see how your IF statement condition is being evaluated when the form is initialised. I wouldn't be surprised if the logic is faulty due to some formulas in your cells returning "".

That being the case then try something like:

If IsEmpty(Sheets("WORK").Range("A3").Value) Then
Me.MultiPage1.pages(0).Visible = False
Else
Me.MultiPage1.Pages(0).Caption = Worksheets("WORK").Range("A3").Value
End If


Colin

thanks for all the help. It turned out that there was a formular in the cell.

Thanks

max

How to Debug (http://www.xtremevbtalk.com/showthread.php?t=9415)