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

Atiqul Haque

New Member
Joined
Dec 13, 2020
Messages
44
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Attachments

  • Pic of UserForm.JPG
    Pic of UserForm.JPG
    29.9 KB · Views: 4

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
714
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Dec 13, 2020
Messages
44
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Dec 13, 2020
Messages
44
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Attachments

  • MyWorkbook.JPG
    MyWorkbook.JPG
    56.6 KB · Views: 4

Atiqul Haque

New Member
Joined
Dec 13, 2020
Messages
44
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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.
 

Attachments

  • MyWorkbook.JPG
    MyWorkbook.JPG
    56.6 KB · Views: 1

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
714
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Dec 13, 2020
Messages
44
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Jan 27, 2010
Messages
714
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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

New Member
Joined
Dec 13, 2020
Messages
44
Office Version
  1. 2016
Platform
  1. Windows
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:
VBA Code:
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

New Member
Joined
Dec 13, 2020
Messages
44
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,456
Messages
5,596,234
Members
414,048
Latest member
wnied1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top