Multiple Auto-Sorting Worksheet Difficulties.

L

Legacy 394756

Guest
Firstly, I am not Excel proficient nor VBA literate. However, I have been slowly learning the language through sites such as HomeandLearn.org to overcome my difficulties but seemingly to no avail (Although making some progress). I have however, identified some of the problems with which I am faced. I believe in order to provide absolute context, I must explain my intentions of the worksheet with great detail. I must apologise in advance for any inappropriate use or misuse of terminology or any other uninformed misapprehension. Additionally, I have familiarised myself with the rules of this subreddit and believe (And hope) that this post is within the rules and guidelines that are in place.
The desired sheet should work as follows:

Sheet1: a blank sheet (Decorated with logo, colours) with a button that activates a userform.

Userform properties: The object contains 7 fields, one is a combo box, with a selection of 3 items, one is a date field, one currency, one numeric field and the rest are general/ text. All fields are required in order to submit the data. The object also contains 2 buttons, one that submits the data to another sheet, the other closes the userform. A message box prompts the user to fulfil this criteria with a [YES] or [NO] if not done so when they press the submit button. (For example: “Form is incomplete. Please complete the form before submitting” [YES][NO]) The submit button checks if the fields are satisfied, if yes; The combo box values are consulted and transmitted to the appropriate sheet (The combo box values dictate which sheet is selected), where the next empty row is filled with the newly input data and the userform fields are then reset in preparation for the next input. If no; A messagebox accompanied with text appears to prompt the user to input correctly.

Sheet2-4: Basically are the same design. Column A is a “priority” column, whereby data is sorted as ascending based on the date field (Input in the userform) subtracted from the current date, and then conditional formatted as: >7 = green, <=7 = Orange, <=0 = Red as a visually coloured indicator. A loop or clock-like macro runs to initiate the auto-sort to update the current date in the [=Now() or =Today()] cell, to constantly sort incoming data. A tickbox (Or Boolean input) is then required to be inputted to transmit the data to a final sheet. (Using the same range finder that the Submit button from the Userform does to find a new row for each new input.)

Sheet5: The simplest piece of the build. Data is received here and stored here.
In summary: User inputs data in userform that is transmitted to one of three sheets as per a drop-down selection, it is then autosorted based on the date difference into ascending order. Boolean input then decides whether the data is transmitted to the final sheet or not.
Noticeable problems/ theoretical difficulties:
- Auto-sorting would put massively negative integers at the very top of the column as it is the smallest number due to the [input date – current date] formula applying to the whole column.
- When submitting information in the userform whilst fields are not satisfied, [YES] on the messagebox to close the messagebox still transmits data to the other sheet despite fields being empty/ not satisfied.
- Userform Combobox directing the transmission of data to a specific sheet.
- [Input date – Current date] formula applying to the whole column.
- Sorting intact rows based on column value.
- Updating the sheet automatically with a macro to update the date/time for comparison.

Here’s what I have so far:

Sheet1:
Imgur: The most awesome images on the Internet
Userform:
Imgur: The most awesome images on the Internet
Imgur: The most awesome images on the Internet
Additional code:
Imgur: The most awesome images on the Internet


What I want seems possible; it just eludes me due to my insufficient Excel capabilities. I have attempted research on all of the above problems, but seemingly to no success. Any assistance with the above problems would be greatly appreciated - Even pointing me in a direction where I can find sufficient information will be favourable. I will be happy to engage anyone in discussion. Thank you for your time.
I am operating on a Microsoft Surfacebook with Windows 10 pro, 64bit.
Excel version 16.0
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
To anyone still interested and finding no assistance on here (Such as I experienced), the post on the Subreddit has been updated to reflect my current progress and is almost completely annotated to describe what does what.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top