Multi paged vba form question

[skinfreak]

Hiya,

I am building a multi page/tabbed form in Excel for data entry and am having a few problems with it.

1) What is the code for flicking between the pages? I want a next button that will go to page 3, and a Previous button that will go to Page 1.

2) How do I set the default Page 1 on form load?

3) How do I minimise the main Excel window and only show the form? I have a macro that loads a splash screen with several options, one of which is loading the form. I want to minumise the main Excel windows with this option.

Thanks!

 

[MichaelRo]

Here we go:

1) What is the code for flicking between the pages? I want a next button that will go to page 3, and a Previous button that will go to Page 1.

Put this code in a button:

Dim i As Long

i = MultiPage1.Pages.Count - 1

'If you are on the last Page select the first one
If MultiPage1.Value = i Then
    MultiPage1.Value = 0
'If you are on the any other page - select the next one
Else
    MultiPage1.Value = MultiPage1.Value + 1
End If

2) How do I set the default Page 1 on form load?

On the Userform Initialize use the following:

'Select the first Page
MultiPage1.Value = 0

3) How do I minimise the main Excel window and only show the form? I have a macro that loads a splash screen with several options, one of which is loading the form. I want to minumise the main Excel windows with this option.

To minimize the excel window you can use the following code:

'Minimize Excel Window
ActiveWindow.WindowState = xlMinimized

 

[skinfreak]

Here we go:

1) What is the code for flicking between the pages? I want a next button that will go to page 3, and a Previous button that will go to Page 1.

Put this code in a button:

Dim i As Long

i = MultiPage1.Pages.Count - 1

'If you are on the last Page select the first one
If MultiPage1.Value = i Then
    MultiPage1.Value = 0
'If you are on the any other page - select the next one
Else
    MultiPage1.Value = MultiPage1.Value + 1
End If

It works fine for the next button, and if i substitute

MultiPage1.Value = MultiPage1.Value + 1

with

MultiPage1.Value = MultiPage1.Value - 1

then the previous button works, but I don't understand the code that returns the user to the first page if there is no next page.

Other than that - Thanks Very Much!

 

[MichaelRo]

The MultiPages are indexed 0 onwards - 0 being the first page of a multipage

My code says that if the index of the current page = the total count of pages (-1 to account for the first page being a zero) then select the first page.

I worked to your specifications but if you want a previous button use the following code as you will cause an error just by changing the +1 to a -1.

'Previous
Dim i As Long

i = MultiPage1.Pages.Count - 1

'If you are on the first Page select the last one
If MultiPage1.Value = 0 Then
    MultiPage1.Value = i
'If you are on the any other page - select the previous one
Else
    MultiPage1.Value = MultiPage1.Value - 1
End If

 

[skinfreak]

And how would I minimise ALL windows? (i.e. the other sheets that a user has open before running this program?)

 

[MichaelRo]

To minimize all windows you could minimize excel itself:

Application.WindowState = xlMinimized

 

[skinfreak]

The problem with that is the splash screen doesn't automatically come up because the program is minimised - it just blinks on the program bar in a minimised state.

Is there a way of maximising the splash screen - auto filling and centering the content with a plain white background? If the user couldn't see excel lying underneath, it wouldn't really make any difference (visually).