1

i have two userforms - Userform1 has few textbox, combobox and check box and has a command submit button where it unloads userform1 and shows the userform2 Userform2 has other set of textbox and combobox and a submit button.

I want the userform2 submit button to update all the data from userform1 and userform2 to my work sheet at once (rather updating the userform1 data first to sheet and than moving to userform2 and updating its data)

Private Sub cmdsubmitdata_Click()
Application.ScreenUpdating = False
Windows("EMPDATA.xlsm").Activate
Sheets("EMP").Select

Range("B2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = NewJoinerEntry.Txtfirstname.Text

Range("B2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 4).Select
ActiveCell.FormulaR1C1 = ComboGender.Text

Range("B2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 14).Select
ActiveCell.FormulaR1C1 = ComboWageType.Text

On this button click it updates the values of ComboGender & ComboWageType on my sheet but doesnot update the Txtfirstname which is on my userform1.

Community
  • 1
  • 1
Jigar Rathod
  • 49
  • 1
  • 1
  • 9
  • 1
    From userform1, do you use Unload me when userform2 is loaded? You may need to define global variables to save the userform1 values, so they can be printed from userform2. – Cyril Aug 31 '17 at 18:14
  • Yes im using unload userform1 at the end of userform1 code and post that userform2.show. Additionally I have not defined any variables, I'm trying to call the textbox control value directly to the cell of sheet as required. – Jigar Rathod Aug 31 '17 at 19:16

2 Answers2

0

You will want to use global variables to save the values from Userform1. 

Public FirstName as String

Sub WhateverYouNamedUserForm1Sub()
     'Regular code you have in there
     FirstName = Txtfirstname.Value 'Check the name of the actual text box
End Sub

Private Sub cmdsubmitdata_Click()
     'Regular code you have in there
     ActiveCell.FormulaR1C1 = FirstName
     'Regular code you have in there
End Sub

See this for more info on global variables: How do I declare a global variable in VBA?

Cyril
  • 6,448
  • 1
  • 18
  • 31
0

Thanks Cyril. Defining a Global variable will surely help but i wanted to directly call the textbox value to the sheet. Its sorted in a very easy way. Well I can call myself dumb too. I had to use Userform1.hide instead of unload userform1. The unload userform1 was deleting the entries stored i guess. Got it sorted now Thanks :)

Jigar Rathod
  • 49
  • 1
  • 1
  • 9
  • You answered your question instead of commenting on an answer. – Moacir Aug 31 '17 at 20:58
  • Got'cha; that was part of the Unload me question I had in the first place. Make sure you Unload userform 1 in the end and you should be golden! – Cyril Sep 01 '17 at 13:45