• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA UserForm - Skip Form Fields, Apply Formulas to New Rows on Data Table

Y

Yodelayheewho

Member
Hello,
I have a UserForm with 100+ textboxes referencing that many columns on the data table
I have a two-prong issue that I want to resolve:
1. For those columns, on the data table that have formulas, when filling out the UserForm, when the user tabs through, I want to skip these fields so they can't be edited.
2. When the user completes the form and submits it (Add New), I need the formulas in the data table to automatically copy down to include the new row. Currently, I have to manually go into the data table and 'drag down' the formulas.
Please see the sample attached.
Thank you in advance!
 

Attachments

  • Sample_Systems Order Entry Master_VBA.xlsm
    227.4 KB · Views: 7
Problem 1:
In the properties set tabstop on false and locked on true where applicable.
Problem 2:
Use a real table instead of Defined Name Range for your mastersheet then the Formulas will be added automaticly.
 
Problem 1: Solved! Thank you!
Problem 2: My data is within a table. I've read other articles and they all say your data must be in a table and mine is.
 
When I click anywhere inside A2:DN7, the Table Design menu appears. My table is named: Master. What am I missing?
 

Attachments

  • 2022-06-01_8-47-20.jpg
    2022-06-01_8-47-20.jpg
    197.4 KB · Views: 6
Good Morning,
Any chance anyone can assist with my second issue: When the user completes the form and submits it (Add New), I need the formulas in the data table to automatically copy down to include the new row. Currently, I have to manually go into the data table and 'drag down' the formulas.
My data is in a table, yet the formulas are not automatically copied to the newly created row.
 
I been looking in your code,the formulas are added but deleted again, I can't find why.
There is such a bunch of crap in your codes,sorry.
A starter up to column 65.
 

Attachments

  • Systems Order Entry Master (reworked).xlsm
    148.4 KB · Views: 12
WOW! You must love doing this because I can tell you spent a ton of time on this. Thank you so very very much!
So, is the crap in my codes gone...LOL. Hey, I'm a beginner:)
 
You are clearly an expert at this and I am slowly learning coding and love Excel. Unfortunately, this project I am working on is for work and I have to build and learn at the same time. Plus, I have to create a form that people will use once I leave the company when I retire. So, I have to build it with future users in mind and make it as intuitive as possible.
If you can recommend any training resources, I would appreciate it. It would be nice to eventually 'pay it forward' and help others as well as myself even during my retirement:).

I really love the Search and Display this is so cool. You can see the history of searches, double-click a record and you're good to go. Genius!

The buttons positioned at the bottom of the form look and behave much better too. Thank you!
  • Can the Update and Save button remain active, so you can save the same record more than once, as you go along?
  • If you're on the same record and accidentally click the Add New Button more than once, it duplicates the record. Our Shop Order Numbers are unique. Is there a way to prevent this? Perhaps block the action of a duplicate number is entered and have a popup message that says, "The Shop Order Number Already Exists. Please Enter a Unique Shop Order Number."
  • Is there a way to bring back the Delete Button, but with a shorter/better code than what I had? :)
It is so nice to have a Calendar feature on the form and have it visible all the time. LOVE IT!
  • Can a clickable TODAY be added to the calendar margin to more easily get today's date?
  • Is there a way for the calendar to pop up when you click on the field (see attached image)? As I'm testing the form, I get tripped up when I'm tabbing through the items and land on the date fields. I instinctively start trying to type the date.
Code
  • I noticed you have very short names for the textboxes, which is a space-saver. I'm impressed that you know what is what. It seems like they 'almost' coincide with the column numbers. For example, Column 23 is PO Received Date, the textbox name is T_22. Just trying to see if you have a pattern.
The TOC is super cool! I didn't expect any improvements, I just left it on the sample. That was a very nice surprise when you improved the heck out of that!
Thank you so very much!
 
I was working on your file file, but my wife says I have to do the dishes now and then;), to finish it it takes a bit of time.
I use short names T_00,T_01 etc.. so it is easy to use a loop (For 0 to 10 for example)
You are right, the short names are almost the same as the columns, but I start with T_00 because a listbox or combobox starts with column 0, to be clear, if you want the value of the first column in your list-or Combobox it is column(0)
The Update and Save button can remain active, but I do that for a reason, the user is obliged to select the right record in the listbox first to make sure you have the right Record, you can use a messagebox instead, but I realy hate messageboxes, (unnecessary clicks)
 
You must log in or register to reply here.
Back
Top