Is THIS POSSIBLE - Dynamic Control Source On TextBox

[Sharid]

Is it possible to have a dynamic control source for a textbox?

In the example below, the userform has 7 pages. Each page has a command button to run the code and is linked to a separate sheet, however there is only 1 TEXTBOX to show results found. This will be triggered from each Sheets Change Events and record on that sheet.

I was thinking of doing it as such. However I am a bit stuck

1. Command button when clicked will place a number 1 to 7 in Sheet10 A1, depending on which multipage it is on.
2. Each Sheet 1 to 7 will record its own results on Sheet change event in a cell e.g. Sheet1 A1, Sheet2 A1, Sheet3 A1
3. The text box will have a if statement to state -

• If Sheet10 A1 = 1 then Control source is Sheet1 A1 End If
• If Sheet10 A1 = 2 then Control source is Sheet2 A1 End If
• If Sheet10 A1 = 3 then Control source is Sheet3 A1 End If

and so on,

is THIS POSSIBLE?

 

[Sharid]

It ok, I think I have done it, it all goes into the command button

Private Sub CommandButton2_Click()
Sheet1.Range("a1").Value = 2 ' Change The Number to set page
If Sheet1.Range("A1") = 2 Then
 TextBox1.ControlSource = "Sheet2!A1" 'new Control Source
End If
End Sub

 

[CephasOz]

You don't need a command button on every page. Use one button, up next to Results, with code like this to see which page is visible at the time, and act accordingly:

Private Sub CommandButton1_Click()
    Dim intPage As Integer
    Select Case Me.MultiPage1.Pages(Me.MultiPage1.Value).Name
        Case "Page1"
            ' Call code for page 1
            intPage = 1
        Case "Page2"
            ' Call code for page 2
            intPage = 2
        Case "Page3"
            ' Call code for page 3
            intPage = 3
        Case "Page4"
            ' Call code for page 4
            intPage = 4
        Case "Page5"
            ' Call code for page 5
            intPage = 5
        Case "Page6"
            ' Call code for page 6
            intPage = 6
        Case "Page7"
            ' Call code for page 7
            intPage = 7
    End Select
    MsgBox "On page " & Format(intPage), vbOKOnly + vbInformation, "Page Test"
End Sub

 

[Sharid]

Super,

just 1 thing, when you state "Call code for Page " is that SOME code i need to add or is that referring to intPage =

 

[CephasOz]

No. It's just whatever you need to do at that point. I used intPage just so you could test the code to show that it worked, but you don't need intPage at all. Delete the lines with intPage on them, and insert your own code at those points.