Add Data to Sheet on MultiPage Tab Active

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
967
Office Version
  1. 2016
Platform
  1. Windows
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

1587989487480.png


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

VBA Code:
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
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,280
Office Version
  1. 2019
Platform
  1. Windows
Hi,
see if this idea will do what you want

VBA Code:
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

Well-known Member
Joined
Apr 22, 2007
Messages
967
Office Version
  1. 2016
Platform
  1. Windows
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

VBA Code:
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

Well-known Member
Joined
Apr 22, 2007
Messages
967
Office Version
  1. 2016
Platform
  1. Windows
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

1587993808722.png


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

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,280
Office Version
  1. 2019
Platform
  1. Windows
Add this code to your UserForm code page

VBA Code:
Private Sub UserForm_Initialize()
    AddData Me.MultiPage2.Value
End Sub

Do not rename the event to match your userforms name

Dave
 

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
967
Office Version
  1. 2016
Platform
  1. Windows
Cheers dmt32 I will

I fixed my code with this
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,308
Messages
5,571,463
Members
412,394
Latest member
divster27
Top