MutiPage control question

[Patrick Simonds]

This code opens the UserForm Data_Input when I click on a cell within the
range E7:E11. This UserForm has a MultiPage control on it and I want the
code below to open the UserForm and set the Focus to the "Income" page
(page 3) of the MultiPage control.


If Not Application.Intersect(Target, Range("E7:E11")) Is Nothing Then
Data_Input.Show

 

[Dave Peterson]

This should get you close:

Option Explicit
Private Sub UserForm_Initialize()
Me.MultiPage1.Value = 3
End Sub

But the first page is 0, so you might want .value = 2.

(I wasn't sure how you were starting your count.)

You may want to change the (Name) property for that page to Income, then you
could use:

Option Explicit
Private Sub UserForm_Initialize()
With Me.MultiPage1
.Value = .Pages("Income").Index
End With
End Sub


And not worry about if that page ever changes position.

 

[Patrick Simonds]

Thanks for the reply.

I must admit that this was the second time I posted this, but did not get a
response, so I simplified it (maybe to much). Below I hope better describes
what it is I need to do.


I have 3 different worksheets and they each get their data from a different
Page on the MultiPage control. What I want to know is if the code below
could be altered in any way as to determine which page of the MultiPage
control has focus when the UserForm is opened? So lets say if line 1 of the
code below were to call the UserForm Page 1 of the MultiPage control would
have focus, but of line 2 were to all the UserForm then Page 3 of the
MultiPage control would have focus.


If Not Application.Intersect(Target, Range("E7:E11")) Is Nothing Then
Data_Input.Show
If Not Application.Intersect(Target, Range("E22")) Is Nothing Then
Data_Input.Show

 

[Dave Peterson]

One way is to use a public variable and pass information through that.

I put this in a General Module:

Option Explicit
Public myMPPageName As String

Then I had this behind the worksheet:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("E7:E11")) Is Nothing Then
myMPPageName = "Page1"
Data_Input.Show
ElseIf Not Application.Intersect(Target, Range("E22")) Is Nothing Then
myMPPageName = "Income"
Data_Input.Show
End If
End Sub

And finally, I had this behind the userform:
Option Explicit
Private Sub UserForm_Initialize()
With Me.MultiPage1
.Value = .Pages(myMPPageName).Index
End With
End Sub


It still relies you passing the (Name) property.

 

[Patrick Simonds]

Thanks

I put this code behind the worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

If Not Application.Intersect(Target, Range("E4")) Is Nothing Then
myMPPageName = "Income"
Data_Input.Show
ElseIf Not Application.Intersect(Target, Range("E7:E11")) Is Nothing
Then
myMPPageName = "Income"
Data_Input.Show
ElseIf Not Application.Intersect(Target, Range("E22")) Is Nothing Then
myMPPageName = "Income"
Data_Input.Show
End If

End Sub

And this in the UserForm Initialize:

With Me.MultiPage1
.Value = .Pages(myMPPageName).Index
End With

But not sure what is ment by a General Module. I tried placing "Option
Explicit Public myMPPageName As String" behind the worksheet and got no
errors, but it did not give the focus to the Income page.

 

[Dave Peterson]

With your project the active project in the VBE.
Insert|Module

Then put that code there.

Thanks

I put this code behind the worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

If Not Application.Intersect(Target, Range("E4")) Is Nothing Then
myMPPageName = "Income"
Data_Input.Show
ElseIf Not Application.Intersect(Target, Range("E7:E11")) Is Nothing
Then
myMPPageName = "Income"
Data_Input.Show
ElseIf Not Application.Intersect(Target, Range("E22")) Is Nothing Then
myMPPageName = "Income"
Data_Input.Show
End If

End Sub

And this in the UserForm Initialize:

With Me.MultiPage1
.Value = .Pages(myMPPageName).Index
End With

But not sure what is ment by a General Module. I tried placing "Option
Explicit Public myMPPageName As String" behind the worksheet and got no
errors, but it did not give the focus to the Income page.

 

[Dave Peterson]

And you want to show the same page for any change in any of those 3 ranges???

if yes:

If Not Application.Intersect _
(Target, Me.Range("E4,e7:e11,e22")) Is Nothing Then
myMPPageName = "Income"
Data_Input.Show
End If

(I didn't notice that before.)

Thanks

I put this code behind the worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

If Not Application.Intersect(Target, Range("E4")) Is Nothing Then
myMPPageName = "Income"
Data_Input.Show
ElseIf Not Application.Intersect(Target, Range("E7:E11")) Is Nothing
Then
myMPPageName = "Income"
Data_Input.Show
ElseIf Not Application.Intersect(Target, Range("E22")) Is Nothing Then
myMPPageName = "Income"
Data_Input.Show
End If

End Sub

And this in the UserForm Initialize:

With Me.MultiPage1
.Value = .Pages(myMPPageName).Index
End With

But not sure what is ment by a General Module. I tried placing "Option
Explicit Public myMPPageName As String" behind the worksheet and got no
errors, but it did not give the focus to the Income page.

 

[Patrick Simonds]

Thank you Sir

And you want to show the same page for any change in any of those 3
ranges???

if yes:

If Not Application.Intersect _
(Target, Me.Range("E4,e7:e11,e22")) Is Nothing Then
myMPPageName = "Income"
Data_Input.Show
End If

(I didn't notice that before.)