ExcelBanter - View Single Post

Patrick Simonds

Thank you Sir

"Dave Peterson" wrote in message
...
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.)

Patrick Simonds wrote:

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" wrote in message
...
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 wrote:

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" wrote in message
...
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 wrote:

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

--

Dave Peterson

--

Dave Peterson