userform update to sheet

[pwwato]

Hi all thanks for looking at this and any help given much appreciated.

I have a multipage for (sites) on a customer userform the multipage has only page1 at design, on running depending on criteria (how many sites a customer has it adds further pages dynamically), I have done this by looping thru the sites and getting the detail on to original page1, then copying that page and pasting into a newly added page with a new reference, so if a customer has three sites it will find the first instance in my sites worksheet, add detail from that row to the page1 controls ( textboxes), then copy and paste controls to a new page and rename that page with site ref and also log the row value, then repeat procedure for next site.

This all works fine, but the problem I am having is if I change the info for a site I need it to update back to sheet, I have used this code to do that but although it runs without error and selects the right sheet it doesn't update the data in the sheet.
Basically this code copies the data from the active sheet back to page1 and then updates the info from there to the site worksheet but it doesn't?

Can someone help please here is my code for the updating part.


Private Sub SUPDATE_Click()
Dim SP As Double
Dim SROW As Long
Dim SPNAME As Variant
Dim SITE As Excel.Worksheet

Application.ScreenUpdating = False
Application.Workbooks("yesdatav2.xlsm").Sheets(4).Activate
Application.Workbooks("yesdatav2.xlsm").Sheets("sites").Select



Set SITE = Sheets(4)



SP = MultiPage2.Value getting index number for active page
SPNAME = MultiPage2.Pages(SP).Name getting ref (name) of active page


MultiPage2.Pages(0).Visible = True making page1 visible this is hidden normally
MultiPage2.Pages(SP).Controls.Copy
MultiPage2.Pages(0).Paste


SROW = MultiPage2.Pages(0).SROW.Text getting row value of site from previously stored row value in textbox when first copied


SITE.Cells(SROW, 3).Value = MultiPage2.Pages(0).SNAME.Value
SITE.Cells(SROW, 8).Value = MultiPage2.Pages(0).SADD1.Value
SITE.Cells(SROW, 9).Value = MultiPage2.Pages(0).SADD2.Value
SITE.Cells(SROW, 10).Value = MultiPage2.Pages(0).SCITY.Value
SITE.Cells(SROW, 11).Value = MultiPage2.Pages(0).SPCODE.Value
SITE.Cells(SROW, 5).Value = MultiPage2.Pages(0).SCONTACT.Value
SITE.Cells(SROW, 6).Value = MultiPage2.Pages(0).SPHONE.Value
SITE.Cells(SROW, 7).Value = MultiPage2.Pages(0).SEMAIL.Value
SITE.Cells(SROW, 13).Value = MultiPage2.Pages(0).SINFOS.Value

MultiPage2.Pages(0).Visible = false
End Sub

like iv said I get no errors it just doesn't change the data on the sites sheet.
Hope someone can help

 

[dmt32]

Hi,
not tested but see if this update to your code helps

Private Sub SUPDATE_Click()
    Dim SROW As Long
    Dim i As Integer, c As Integer
    Dim ControlsArr As Variant
    Dim SITE As Excel.Worksheet
    
    
    Set SITE = ThisWorkbook.Worksheets("Site")
    
    ControlsArr = Array(Me.SNAME.Value, Me.SCONTACT.Value, Me.SPHONE.Value, Me.SEMAIL.Value, Me.SADD1.Value, _
                        Me.SADD2.Value, Me.SCITY.Value, Me.SPCODE.Value, Me.SINFOS.Value)
    
'getting row value of site from previously stored
'row value in textbox when first copied
    SROW = Val(Me.SROW.Text)
    If SROW = 0 Then Exit Sub

    i = LBound(ControlsArr)
    For c = 3 To 13
        c = IIf(c = 4, 5, IIf(c = 12, 13, c))
        SITE.Cells(SROW, c).Value = ControlsArr(i)
        i = i + 1
    Next c
End Sub

Dave

 

[pwwato]

Thanks for that dave but didnt seem to work, it copied only one of the textbox values scontact but into the wrong column, and I'm not sure how to adjust this code as I have never really dealt with arrays bit of a newbie.

 

[pwwato]

Really need some help on getting this working if any one can it would be much appreciated.

 

[dmt32]

Thanks for that dave but didnt seem to work, it copied only one of the textbox values scontact but into the wrong column, and I'm not sure how to adjust this code as I have never really dealt with arrays bit of a newbie.

Like all these things, without seeing workbook a bit of a guess.

If you could Place copy of your workbook with sample data in dropbox with link to it here would be a help.

Dave