Re: Mandatory fields of a "Data Entry UserForm" should be filled serially/chronologically.

[Atiqul Haque]

Good Evening Everybody,

I'll be glad if any one help me to solve the following problems.
Thanks in advance.

=================================
Problem#1
Mandatory fields of a "Data Entry UserForm" should be filled serially/chronologically.

I've a data entry user form of the following:

TextBox1 = Mandatory field
TextBox2 = Optional
TextBox3 = Mandatory field
TextBox4 = Mandatory field
ComboBox1 = Mandatory field
TextBox5 = Mandatory field
ComboBox1 = Mandatory field

I want that the user must fill the mandatory fields serially/chronologically (i.e; firstly textbox1 then textbox3 then textbox4...something like that.), o/wise user can't add any data to the worksheet.

Problem#2

I've 8 (eight) worksheets under a workbook and when i add data in a worksheet by a "data entry form",
i need to copy/paste the same data to all other worksheets at the same time.

 

[6StringJazzer]

The best way to this is to disable all controls except the first one when the form is initialized/activated. Then create a _Change event for the first field so that when the field is properly filled out, it will enable the next field. Do this for all fields. Then when the last field is complete, enable the Add Data button

On problem #2. It will not literally be a copy/paste, but you will do an assignment for each value of the form into each corresponding sheet cell. You will have to have a loop to do this on multiple sheets. It will be easy if all the sheets have the same format. But I can't show you code without seeing your worksheets.

 

[Atiqul Haque]

The best way to this is to disable all controls except the first one when the form is initialized/activated. Then create a _Change event for the first field so that when the field is properly filled out, it will enable the next field. Do this for all fields. Then when the last field is complete, enable the Add Data button

On problem #2. It will not literally be a copy/paste, but you will do an assignment for each value of the form into each corresponding sheet cell. You will have to have a loop to do this on multiple sheets. It will be easy if all the sheets have the same format. But I can't show you code without seeing your worksheets.

Thank you sir,
Pls let me know how can i send you my workbook?
Thanks in advance.

 

[Atiqul Haque]

The best way to this is to disable all controls except the first one when the form is initialized/activated. Then create a _Change event for the first field so that when the field is properly filled out, it will enable the next field. Do this for all fields. Then when the last field is complete, enable the Add Data button

On problem #2. It will not literally be a copy/paste, but you will do an assignment for each value of the form into each corresponding sheet cell. You will have to have a loop to do this on multiple sheets. It will be easy if all the sheets have the same format. But I can't show you code without seeing your worksheets.

Dear Sir,
Attached herewith the pic of my workbook for your understanding.

 

[Atiqul Haque]

The best way to this is to disable all controls except the first one when the form is initialized/activated. Then create a _Change event for the first field so that when the field is properly filled out, it will enable the next field. Do this for all fields. Then when the last field is complete, enable the Add Data button

On problem #2. It will not literally be a copy/paste, but you will do an assignment for each value of the form into each corresponding sheet cell. You will have to have a loop to do this on multiple sheets. It will be easy if all the sheets have the same format. But I can't show you code without seeing your worksheets.

Dear Sir,
Attached herewith the pic of my workbook for your understanding.

 

[6StringJazzer]

That helps but it needs to show column and row numbers. Also, are all the sheets the same? Consider using the XL2BB tool (download button in edit panel) to show sheets/formulas.

The best way to share your file with a free file sharing service. Examples are Google Docs, Dropbox, OneDrive.

 

[Atiqul Haque]

That helps but it needs to show column and row numbers. Also, are all the sheets the same? Consider using the XL2BB tool (download button in edit panel) to show sheets/formulas.

The best way to share your file with a free file sharing service. Examples are Google Docs, Dropbox, OneDrive.

Sending herewith the workbook link in dropbox.

 

[6StringJazzer]

I described what to do for problem #1. Given the amount of code you already have I am not going to take the time to reverse engineer it all and implement that solution. Based on what I'm seeing here I'm sure you can do that.

Here is the code update to save the data into all sheets:

Private Sub CommandButton1_Click()

   Dim WS As Worksheet
   
   For Each WS In Worksheets
      With WS
         .Range("c9").Value = TextBox1.Text
         .Range("f9").Value = TextBox2.Text
         .Range("c10").Value = TextBox3.Text
         .Range("i10").Value = TextBox4.Text
         .Range("c11").Value = TextBox5.Text
         
         .Range("m10").Value = ComboBox1.Text
         .Range("f11").Value = ComboBox2.Text
      End With
   Next WS
   
   'clear the data
   Me.TextBox1.Value = ""
   Me.TextBox2.Value = ""
   Me.TextBox3.Value = ""
   Me.TextBox4.Value = ""
   Me.TextBox5.Value = ""
   
   Me.ComboBox1.Value = ""
   Me.ComboBox2.Value = ""
   
   Unload Me

End Sub

 

[Atiqul Haque]

That helps but it needs to show column and row numbers. Also, are all the sheets the same? Consider using the XL2BB tool (download button in edit panel) to show sheets/formulas.

The best way to share your file with a free file sharing service. Examples are Google Docs, Dropbox, OneDrive.

I described what to do for problem #1. Given the amount of code you already have I am not going to take the time to reverse engineer it all and implement that solution. Based on what I'm seeing here I'm sure you can do that.

Here is the code update to save the data into all sheets:

Private Sub CommandButton1_Click()

   Dim WS As Worksheet
  
   For Each WS In Worksheets
      With WS
         .Range("c9").Value = TextBox1.Text
         .Range("f9").Value = TextBox2.Text
         .Range("c10").Value = TextBox3.Text
         .Range("i10").Value = TextBox4.Text
         .Range("c11").Value = TextBox5.Text
        
         .Range("m10").Value = ComboBox1.Text
         .Range("f11").Value = ComboBox2.Text
      End With
   Next WS
  
   'clear the data
   Me.TextBox1.Value = ""
   Me.TextBox2.Value = ""
   Me.TextBox3.Value = ""
   Me.TextBox4.Value = ""
   Me.TextBox5.Value = ""
  
   Me.ComboBox1.Value = ""
   Me.ComboBox2.Value = ""
  
   Unload Me

End Sub

Good Evening Sir,

It works perfectly, thank you very much.
I also need that, a user can't put data in "textbox2" until he put data in "textbox1", similarly can't go for "textbox3" until he fills data in "textbox2"...... that means in all mandatory fields must filled before adding the data to the worksheets....how can i do this? Kindly advice.

 

[Atiqul Haque]

Good Evening Sir,

It works perfectly, thank you very much.
I also need that, a user can't put data in "textbox2" until he put data in "textbox1", similarly can't go for "textbox3" until he fills data in "textbox2"...... that means in all mandatory fields must filled before adding the data to the worksheets....how can i do this? Kindly advice.

Also need to know, if i want to copy data in some specific sheets then what will be the codes?
Thanks in advance.