Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
MutiPage control question
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
MutiPage control question
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
MutiPage control question
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
MutiPage control question
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
MutiPage control question
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
MutiPage control question
With your project the active project in the VBE.
Insert|Module Then put that code there. 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
MutiPage control question
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
MutiPage control question
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stock control question | Excel Worksheet Functions | |||
Question on Control Toolbox | Excel Discussion (Misc queries) | |||
Extracting a Single Worksheet froma Mutipage Workbook | Excel Discussion (Misc queries) | |||
Updown control question | Excel Programming | |||
Control button question | Excel Programming |