Hello all,
I am trying to finally get my first user form working and hoping its quite basic for asking for help
I would appreciate any help trying to glue the final pieces together and helping with a couple of issues.
I have the form made and its working so far but need help code to get it fully functional and the code below is what I have already on the command button 1
I have a sheet called ebrexit columns A to N and down to row 13660.
I do not need any records added and I just need to update the sheet with details displayed once combobox 2 option chosen which I have the row source working but need it to lookup the paynumber in Column A and then update Column J with the country chosen
I then choose from ComboBox2 and update column K with Contact chosen.
I finally need to get the user to manually input an email address in Text Box10 and update the row in column L based on the paynumber from Textbox1
The above is all based on the pay number chosen in Textbox1 and im trying to lookup the paynumber then update the appropriate column/row but unsure on how to make happen from the form and put into VBA
In Textbox 1, I have an employee paynumber that when input Text Boxes 1 to 9 autocomplete.
The other issue I have is the format in the text box is alpha numeric 8 digits long and although I have formatted the sheet column to text to it only populates the form if I take the First letter off and input numbers only.
Private Sub commandbutton1_click()
Row_Number = 0
Do
DoEvents
Row_Number = Row_Number + 1
review = Sheets("eBrexit").Range("A" & Row_Number)
If review = TextBox1.Text Then
TextBox2.Text = Sheets("eBrexit").Range("B" & Row_Number)
TextBox3.Text = Sheets("eBrexit").Range("C" & Row_Number)
TextBox4.Text = Sheets("eBrexit").Range("D" & Row_Number)
TextBox5.Text = Sheets("eBrexit").Range("E" & Row_Number)
TextBox6.Text = Sheets("eBrexit").Range("F" & Row_Number)
TextBox7.Text = Sheets("eBrexit").Range("G" & Row_Number)
TextBox8.Text = Sheets("eBrexit").Range("H" & Row_Number)
TextBox9.Text = Sheets("eBrexit").Range("I" & Row_Number)
End If
Loop Until review = ""
End Sub
Once all the additional fields are complete from the combox boxes and TextBox 10, I would then like to Click the "Submit Changes" button and have the sheet updated then cleared automatically and then default to Text Box 1 for next record.
It sounds long winded but I am hoping to try and get my first form working and hopefully learn further from this.
I basically have a form that is looking up the paynumber in column A and displaying the appropriate details in text box 2 to 9.
Im then trying to update the sheet columns J,K and L and in the appropriate row based on employee number.
I do hope this makes some sort of sense and someone can help glue the final pieces together to get me up and running
Any help to try and help me glue this together would be greatly appreciated
Kind Regards
Ian
I would like the form to be constant open in a sheet called BrexForm
I am trying to finally get my first user form working and hoping its quite basic for asking for help
I would appreciate any help trying to glue the final pieces together and helping with a couple of issues.
I have the form made and its working so far but need help code to get it fully functional and the code below is what I have already on the command button 1
I have a sheet called ebrexit columns A to N and down to row 13660.
I do not need any records added and I just need to update the sheet with details displayed once combobox 2 option chosen which I have the row source working but need it to lookup the paynumber in Column A and then update Column J with the country chosen
I then choose from ComboBox2 and update column K with Contact chosen.
I finally need to get the user to manually input an email address in Text Box10 and update the row in column L based on the paynumber from Textbox1
The above is all based on the pay number chosen in Textbox1 and im trying to lookup the paynumber then update the appropriate column/row but unsure on how to make happen from the form and put into VBA
In Textbox 1, I have an employee paynumber that when input Text Boxes 1 to 9 autocomplete.
The other issue I have is the format in the text box is alpha numeric 8 digits long and although I have formatted the sheet column to text to it only populates the form if I take the First letter off and input numbers only.
Private Sub commandbutton1_click()
Row_Number = 0
Do
DoEvents
Row_Number = Row_Number + 1
review = Sheets("eBrexit").Range("A" & Row_Number)
If review = TextBox1.Text Then
TextBox2.Text = Sheets("eBrexit").Range("B" & Row_Number)
TextBox3.Text = Sheets("eBrexit").Range("C" & Row_Number)
TextBox4.Text = Sheets("eBrexit").Range("D" & Row_Number)
TextBox5.Text = Sheets("eBrexit").Range("E" & Row_Number)
TextBox6.Text = Sheets("eBrexit").Range("F" & Row_Number)
TextBox7.Text = Sheets("eBrexit").Range("G" & Row_Number)
TextBox8.Text = Sheets("eBrexit").Range("H" & Row_Number)
TextBox9.Text = Sheets("eBrexit").Range("I" & Row_Number)
End If
Loop Until review = ""
End Sub
Once all the additional fields are complete from the combox boxes and TextBox 10, I would then like to Click the "Submit Changes" button and have the sheet updated then cleared automatically and then default to Text Box 1 for next record.
It sounds long winded but I am hoping to try and get my first form working and hopefully learn further from this.
I basically have a form that is looking up the paynumber in column A and displaying the appropriate details in text box 2 to 9.
Im then trying to update the sheet columns J,K and L and in the appropriate row based on employee number.
I do hope this makes some sort of sense and someone can help glue the final pieces together to get me up and running
Any help to try and help me glue this together would be greatly appreciated
Kind Regards
Ian
I would like the form to be constant open in a sheet called BrexForm
