Using two userforms

[mnmhenry]

Good morning all,

I have two userforms - Userform1 & Userform2

I open Userform1 and enter data. I then unlaod Userform1 and open Userform2 to enter more data - then my command to send the data to the excel sheet but only the data from Userform2 carries across to the spreadsheet?

Why am I missing Userform1's data as I unload both sheets before transferring the data?

Unload UserForm1
'(text input)

Userform2.Show
'(text input)
Unload UserForm2

Range("K7").Select
Selection.FormulaR1C1 = TextBox16.Text (Userform1)

Range("P7").Select
Selection.FormulaR1C1 = TextBox5.Text (Userform2)

 

[AlphaFrog]

If you use the Unload statement, then the UserForm is removed from memory and all changes to the form are lost.

If you .Hide a UserForm, it will remain in memory. Any changes made to the form will be retained are accessible.

Try using UserForm1.Hide instead of Unload UserForm1

 

[mnmhenry]

Aahhh big help. Many thanks.

 

[SJG504]

Thank you UserForm1.Hide worked very well, however Userform1 updates the excel sheet before Userform2 is shown.

userform1.hide
userform2.show

I want the data from BOTH userforms to be updated on the excel sheet when I send the data command not before.

I can move from userform2 to userform1 without the excel sheet updating & the data remaing in both userforms however when I move from userform1 to userform2 the excel sheet updates each time

From 2 to 1 - no updating
userform2.hide
userform1.show

From 1 to 2 - updating excel sheet occurs
userform1.hide
userform2.show

 

[AlphaFrog]

Thank you UserForm1.Hide worked very well, however Userform1 updates the excel sheet before Userform2 is shown.

userform1.hide
userform2.show

I want the data from BOTH userforms to be updated on the excel sheet when I send the data command not before.

I can move from userform2 to userform1 without the excel sheet updating & the data remaing in both userforms however when I move from userform1 to userform2 the excel sheet updates each time

From 2 to 1 - no updating
userform2.hide
userform1.show

From 1 to 2 - updating excel sheet occurs
userform1.hide
userform2.show

Are SJG504 and mnmhenry the same person?

Can you post all you code for both userforms?

 

[SJG504]

My apologies, no we are not the same person just work in the same office & mnmhenry has been assisting me - when he showed me the forum I decided to join & learn myself without bugging him all the time, again I apologise. I should have (& did) post a new thread rather than just reply to him.

Ok ALL of the code is as follows:

SITE ADDRESS DETAILS - Userform1

Private Sub CB_Next_Click()

Sheets("Data Input").Select

Range("B4").Select ' Quote Number
Selection.FormulaR1C1 = TxtBox_Quote.Text

Range("D4").Select ' Housetype
Selection.FormulaR1C1 = ComboBox_Housetype.Text

Range("G4").Select ' Sales Consultants Initials
Selection.FormulaR1C1 = ComboBox_Consultant.Text

Range("B7").Select ' Lot No.
Selection.FormulaR1C1 = TxtBox_LotNo.Text

Range("C7").Select ' SP / RP / DP
Selection.FormulaR1C1 = TxtBox_SPNo.Text

Range("D7").Select ' Site Address
Selection.FormulaR1C1 = TxtBox_SiteStreet.Text

Range("F7").Select ' Site Suburb
Selection.FormulaR1C1 = TxtBox_SiteSuburb.Text

Range("H7").Select ' Site Estate Name
Selection.FormulaR1C1 = TxtBox_SiteEstate.Text

Range("J7").Select ' Site State
Selection.FormulaR1C1 = ComboBox_SiteState.Text

Range("K7").Select ' Site Postcode
Selection.FormulaR1C1 = TxtBox_SitePostcode.Text

Range("L7").Select ' Local Authority
Selection.FormulaR1C1 = ComboBox_SiteLocalAuthority.Text

SiteAddressDetails.Hide
ClientDetails.Show

End Sub

Back Button from Userform2 to Userform1

Private Sub CB_Back_Click()

ClientDetails.Hide

SiteAddressDetails.Show

End Sub


CLIENT DETAILS USERFORM - Userform2

Private Sub CB_Save_Click()

Unload ClientDetails

Sheets("Data Input").Select

Range("G10").Select ' Suffix 1
Selection.FormulaR1C1 = ComboBox_Suffix1.Text

Range("D10").Select ' First Name 1
Selection.FormulaR1C1 = TxtBox_Name1.Text

Range("F10").Select ' Initials 1
Selection.FormulaR1C1 = TxtBox_Initials1.Text

Range("B10").Select ' Surname 1
Selection.FormulaR1C1 = TxtBox_Surname1.Text

Range("M10").Select ' Suffix 2
Selection.FormulaR1C1 = ComboBox_Suffix2.Text

Range("J10").Select ' First Name 2
Selection.FormulaR1C1 = TxtBox_Name2.Text

Range("L10").Select ' Initials 2
Selection.FormulaR1C1 = TxtBox_Initials2.Text

Range("H10").Select ' Surname 2
Selection.FormulaR1C1 = TxtBox_Surname2.Text

Range("B13").Select ' Postal Address
Selection.FormulaR1C1 = TxtBox_PostalAddress.Text

Range("D13").Select ' Postal Suburb
Selection.FormulaR1C1 = TxtBox_PostalSuburb.Text

Range("F13").Select ' Postal State
Selection.FormulaR1C1 = ComboBox_PostalState.Text

Range("G13").Select ' Postal Postcode
Selection.FormulaR1C1 = TxtBox_PostalPostcode.Text

Range("D16").Select ' Work Number 1
Selection.FormulaR1C1 = TxtBox_WorkPhone1.Text

Range("F16").Select ' Mobile Number 1
Selection.FormulaR1C1 = TxtBox_MobilePhone1.Text

Range("L16").Select ' Email Address 1
Selection.FormulaR1C1 = TxtBox_Email1.Text

Range("H16").Select ' Work Number 2
Selection.FormulaR1C1 = TxtBox_WorkPhone2.Text

Range("J16").Select ' Mobile Number 2
Selection.FormulaR1C1 = TxtBox_MobilePhone2.Text

Range("N16").Select ' Email Address 2
Selection.FormulaR1C1 = TxtBox_Email2.Text

Range("B16").Select ' Home Phone Number
Selection.FormulaR1C1 = TxtBox_HomePhone.Text

Range("A1").Select

End Sub

Your help is greatly appreciated

 

[AlphaFrog]

Try something like this. (not tested)

SITE ADDRESS DETAILS - Userform1

Private Sub CB_Next_Click()
    SiteAddressDetails.Hide
    ClientDetails.Show
End Sub

CLIENT DETAILS USERFORM - Userform2

Private Sub CB_Back_Click()

    ClientDetails.Hide
    SiteAddressDetails.Show

End Sub


Private Sub CB_Save_Click()

    Sheets("Data Input").Select
    
    With SiteAddressDetails
        Range("B4").Value = .TxtBox_Quote.Text                  ' Quote Number
        Range("D4").Value = .ComboBox_Housetype.Text            ' Housetype
        Range("G4").Value = .ComboBox_Consultant.Text           ' Sales Consultants Initials
        Range("B7").Value = .TxtBox_LotNo.Text                  ' Lot No.
        Range("C7").Value = .TxtBox_SPNo.Text                   ' SP / RP / DP
        Range("D7").Value = .TxtBox_SiteStreet.Text             ' Site Address
        Range("F7").Value = .TxtBox_SiteSuburb.Text             ' Site Suburb
        Range("H7").Value = .TxtBox_SiteEstate.Text             ' Site Estate Name
        Range("J7").Value = .ComboBox_SiteState.Text            ' Site State
        Range("K7").Value = .TxtBox_SitePostcode.Text           ' Site Postcode
        Range("L7").Value = .ComboBox_SiteLocalAuthority.Text   ' Local Authority
    End With

    With ClientDetails
        Range("G10").Value = .ComboBox_Suffix1.Text             ' Suffix 1
        Range("D10").Value = .TxtBox_Name1.Text                 ' First Name 1
        Range("F10").Value = .TxtBox_Initials1.Text             ' Initials 1
        Range("B10").Value = .TxtBox_Surname1.Text              ' Surname 1
        Range("M10").Value = .ComboBox_Suffix2.Text             ' Suffix 2
        Range("J10").Value = .TxtBox_Name2.Text                 ' First Name 2
        Range("L10").Value = .TxtBox_Initials2.Text             ' Initials 2
        Range("H10").Value = .TxtBox_Surname2.Text              ' Surname 2
        Range("B13").Value = .TxtBox_PostalAddress.Text         ' Postal Address
        Range("D13").Value = .TxtBox_PostalSuburb.Text          ' Postal Suburb
        Range("F13").Value = .ComboBox_PostalState.Text         ' Postal State
        Range("G13").Value = .TxtBox_PostalPostcode.Text        ' Postal Postcode
        Range("D16").Value = .TxtBox_WorkPhone1.Text            ' Work Number 1
        Range("F16").Value = .TxtBox_MobilePhone1.Text          ' Mobile Number 1
        Range("L16").Value = .TxtBox_Email1.Text                ' Email Address 1
        Range("H16").Value = .TxtBox_WorkPhone2.Text            ' Work Number 2
        Range("J16").Value = .TxtBox_MobilePhone2.Text          ' Mobile Number 2
        Range("N16").Value = .TxtBox_Email2.Text                ' Email Address 2
        Range("B16").Value = .TxtBox_HomePhone.Text             ' Home Phone Number
    End With

    Unload SiteAddressDetails
    Unload ClientDetails

End Sub

 

[SJG504]

Thank you very much for your feedback, it worked a real treat (tested) it made a lot of sense when we saw your approach to the issue & we will use it for quite a few other applications in the future. You assistance was greatly appreciated.

 

[mikerickson]

Let me throw in this suggestion, rather than two userforms, it might be easier to have one userform with a MultiPage control.

 

[SJG504]

Thank you for your suggestion, the userform looks even better this way, question is though in using a "next" & "back" command button to move from page to page I can't find a code that works as .Hide & .Show doesn't work. What am I missing??