Multipage Function

    I have a MultiPage item on my UserForm. It has several pages, each page requires information from and submits information to different worksheets in my workbook. Additionally there are checkboxes on each page that I want to write to cells in each worksheet.


    E.G. CheckBox1.Value = True Then
    Sheet?.Cells("1", "A") = "Y"


    CheckBox2.Value = True Then
    Sheet?.Cells("1", "B") = "Y"


    These Y's add figures to a total amount, and where the ? I want it to be the relevant worksheet number for the page selected.


    How can I make it that when I select a page, the focus is changed to the specific worksheet.

    Each page on the multipage control has a Tag property.


    Set the tag property for each sheet to be the corresponding sheet name you'd like to activate when the page is selected.


    Then just add this code to your userform:


    Private Sub MultiPage1_Change()
    Sheets(MultiPage1.SelectedItem.Tag).Activate
    End Sub

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

    Gollum & Aaron,


    Both good but the sheets referred to are VeryHidden and need to stay hidden as the user will not see all the data on the worksheets. I am using the MultiPage so I can let them have certain data. Your solutions only seem to work on active sheets

    Ok,


    try this


    ActiveWorkbook.Sheets(UserForm1.MultiPage1.Value + 1).cells(1,1).value = "test"


    So you can define the sheet and cell even if you don't select it. So you can fill your cells without activating the sheets. (or you can get the data you want)


    Gollem

    Example attached


    OK Gents,


    Attached is an example.


    Firstly look at 'Summary 2004' worksheet (In my workbook this is sheet5). the Yellow area will have figures from other sheets (All hidden). Columns B, C, D and E when a 'Y' is entered the value in column G is added to. Column O is the total figure. You will also notice there are 4 shift patterns (50 hour week, 60 hour week etc)


    Now select 'Colin's Button' to view the MultiPage. Look at Page labelled 2004.
    I have set this up so that each CheckBox a script e.g. for the first CheckBox (Technician, London)


    If CheckBox1.Value = True Then
    Sheet5.Cells("5", "B") = "Y"
    If CheckBox1.Value = False Then
    Sheet5.Cells("5", "B") = ""


    This works OK, but as the row value is set at 5 I can only get it to work for the first shift.


    The MultiPage is all the user will see, there first think is to select the shift. I want this to change the row source (Either "5", "15", "25" or "35" dependant on which shift is selected). The second to select any additional features (London, Lodge etc). At the end of each row is the total figure.


    What should happen then is when they select 'Update', all the operatives that have been selected should have the title (Technician, Labourer etc) for that operative pasted into Column A (First available cell) and the total into Column B of sheet4.


    Obviously when they select 2005 page, the data is from Sheet6, therefore I need to change the sheet reference. But the submit will still be to sheet4.



    OK, so you don't activate the sheets... as mentioned above. You can modify my example in a similar fashion.


    I gotta warn you though, the Gollem method is making an assumption that the sheets in the workbook are a 1:1 relationship with the sheets in the multipage control and it assumes the sheets are in the same order.


    That may be OK. But if you move the sheets in the workbook around, the multipage won't always refer to the same sheets. Just something to be aware of.

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

    Sorry Gents still not getting this:


    I want to change the sheet source and row source (Either "5", "15", "25" or "35" dependant on which shift is selected). Where would I put this in the routine you gave me before???



    ActiveWorkbook.Sheets(UserForm1.MultiPage1.Value + 1).cells(1,1).value = "test"

    Aaron or Gollum,


    I still need some help with this.


    Can you look at the spreadsheet I submitted earlier and let me know where I would insert your formula to get the specified results.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login