Using two userforms

[SJG504]

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 remaining in both userforms however when I move from userform1 to userform2 the excel sheet updates each time

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

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

 

[Sal Paradise]

Check to see if you have a command that runs on load/on hide with userform2, and not on userform1.

 

[SJG504]

Thank you for your reply, an example of the code for both userforms:


USERFORM 1

Private Sub CB_Next_Click()

Sheets("Data Input").Select

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

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

SiteAddressDetails.Hide
ClientDetails.Show

End Sub

USERFORM 2

Private Sub CB_Save_Click()

Unload ClientDetails

Sheets("Data Input").Select

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

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

Range("A1").Select

End Sub

 

[Sal Paradise]

Here's your problem:

Unload ClientDetails

When you unload the form, the changes get saved I'd wager. Try removing that line.

 

[SJG504]

Sorry Sal you lost your wager, Userform2 remains on screen after data has been entered in excel sheet from userform2 with no change from data being entered from userform1.

 

[Sal Paradise]

Okay, you've got me.

Let's try this in a more philosophical sense.

How are people entering their data?
- It looks like you have a text box and a combobox, both linked to a cell in Userform1.
- It looks like you have a text box and a combobox, both linked to a cell in Userform2.

The difference between the two is that when you click the "Next" button on Userform1, the data is already being entered into the sheet (because you are saving the values of your cb/textbox to the sheet when you click the button), but that isn't happening with form2 despite the same code?

That doesn't make much sense, unless you mean that going from form2 to form1 by clicking the "x" button in the top right doesn't update data (that it wouldn't).

You could, instead, set a list of data entries to update using global variables, and then have a function to update the sheet when you push the button in the second form only.

For instance:

Dim strQuoteNumber, strLocalAuthority, strSuffix1, strHomePhoneNumber As String

Private Sub CB_Next_Click()
    strQuoteNumber = TxtBox_Quote.Text
    strLocalAuthority = ComboBox_SiteLocalAuthority.Text

    SiteAddressDetails.Hide
    ClientDetails.Show

End Sub

Private Sub CB_Save_Click()
    strSuffix1 = ComboBox_Suffix1.Text
    strHomePhoneNumber = TxtBox_HomePhone.Text

    With Sheets("Data Input")
        .Range("B4").Value = strQuoteNumber
        .Range("L7").Value = strLocalAuthority
        .Range("G10").Value = strSuffix1
        .Range("B16").Value = strHomePhoneNumber
    End With

        strQuoteNumber = ""
        strLocalAuthority = ""
        strSuffix1 = ""
        strHomePhoneNumber = ""

End Sub

Diddle as needed.

 

[SJG504]

Hi Sal,

Sorry for no quick response, I have been tied up. We have changed the method & have gone with one userform using multipage instead, works even better. Thanks for you help anyway.