Add Data to Sheet on MultiPage Tab Active

[Sharid]

Hi

I am using the same code over and over again, each one has a few minor changes to it. Rather than do it like that I have worked out that if I place the changes onto a sheet and then reference to code to look there when it is run I can implement the changes. I have tested this and it work, however I have to implement the changes manually. Now I have done the following;

I have a userform on it I have a MultiPage (MultiPage1) and on each tab I have another MultiPage (MultiPage2). What I need help with is that when each page on MultiPage2 is ACTIVE it will automatically place data in column B on Sheet4.

The useform WILL NOT be opening on Sheet4

So when page 4 is Active data will go into Sheet4 Column B and the same for the others Page 5 and Page 6, i was testing this but could not get it to work

Private Sub MultiPage2_Change()
'Test Code
If Page4 = True Then
    Worksheets(Sheet4).Columns(2).ClearContents
        Sheet4.Range("B1").Value = "one"
        Sheet4.Range("B2").Value = "Two"
        Sheet4.Range("B3").Value = "Three"
        Sheet4.Range("B4").Value = "Four"
End If

If Page5 = True Then
    Worksheets(Sheet4).Columns(2).ClearContents
        Sheet4.Range("B1").Value = "Five"
        Sheet4.Range("B2").Value = "Six"
        Sheet4.Range("B3").Value = "Seven"
        Sheet4.Range("B4").Value = "Eight"
End If

If Page6 = True Then
    Worksheets(Sheet4).Columns(2).ClearContents
        Sheet4.Range("B1").Value = "Nine"
        Sheet4.Range("B2").Value = "Ten"
        Sheet4.Range("B3").Value = "Eleven"
        Sheet4.Range("B4").Value = "Twelve"
End If

End Sub

 

[dmt32]

Hi,
see if this idea will do what you want

Private Sub MultiPage2_Change()
    AddData Me.MultiPage2.Value
End Sub


Sub AddData(ByVal Index As Integer)
Dim arr As Variant
    arr = Array(Array("One", "Two", "Three", "Four"), _
                Array("Five", "Six", "Seven", "Eight"), _
                Array("Nine", "Ten", "Eleven", "Tweleve"))
                
     Sheet4.Range("B1:B4").Value = Application.Transpose(arr(Index))
End Sub

Dave

 

[Sharid]

dmt32

Not tried your code yet, but I will. I did get it working with this. Only problem is Page 4 which is alread active needs to be CLICKED AGAIN to input the data to Sheet4 all other worked fine

Private Sub MultiPage2_Change()
'Test Code

 If UserForm1.MultiPage2.Value = 0 Then
   
        Sheet4.Range("B1").Value = "one"
        Sheet4.Range("B2").Value = "Two"
        Sheet4.Range("B3").Value = "Three"
        Sheet4.Range("B4").Value = "Four"
End If
 If UserForm1.MultiPage2.Value = 1 Then
        Sheet4.Range("B1").Value = "Five"
        Sheet4.Range("B2").Value = "Six"
        Sheet4.Range("B3").Value = "Seven"
        Sheet4.Range("B4").Value = "Eight"
End If
 If UserForm1.MultiPage2.Value = 2 Then
        Sheet4.Range("B1").Value = "Nine"
        Sheet4.Range("B2").Value = "Ten"
        Sheet4.Range("B3").Value = "Eleven"
        Sheet4.Range("B4").Value = "Twelve"
End If

End Sub

 

[Sharid]

You code works, but has same issue as mine, Data for first tab has not gone in. I have to select page 5 or 6 then page 4 to to get page 4 data

After selecting another page and then coming back to page 4, now results have gone in. Both code have this issue

 

[dmt32]

Add this code to your UserForm code page

Private Sub UserForm_Initialize()
    AddData Me.MultiPage2.Value
End Sub

Do not rename the event to match your userforms name

Dave

 

[Sharid]

Cheers dmt32 I will

I fixed my code with this

Private Sub MultiPage2_Layout(ByVal Index As Long)
 If UserForm1.MultiPage2.Value = 0 Then

        Sheet4.Range("B1").Value = "one"
        Sheet4.Range("B2").Value = "Two"
        Sheet4.Range("B3").Value = "Three"
        Sheet4.Range("B4").Value = "Four"
End If
End Sub

but I will go with yours