BeachDog_2600
New Member
- Joined
- Feb 4, 2025
- Messages
- 2
- Office Version
- 365
- Platform
- Windows
I am trying to add additional protections to a workbook that multiple users use and some of those users are not that great with technology. I'm running into an issue by using workbook/worksheet protections because I need everyone to be able to input data, but the inexperienced users are screwing up the worksheet by copying and pasting cells that have data validation drop downs and tooltips. I'm noticing that errors keep happening even after trying to help those users understand, but I'm thinking that improving the workbook with VBA code might be the best solution. I'm not very familiar with VBA code, but I'm taking some classes on LinkedIn learning to become more familiar (any suggestions on other resources for learning would be much appreciated.)
What I'm trying to do is create a VBA code that does the following:
Thank you so much and I really appreciate any insight you can give.
What I'm trying to do is create a VBA code that does the following:
- When the workbook is opened, I want the data to automatically sort the table (columns A-AL) on Sheet1 (LOA) ascending based on Employee Name.
- Is it possible to automatically add a row (which includes all of the data validation/dropdowns from the rest of the table) to Table1 if there is data in the current last row or would I just need to make sure the table is large enough to accommodate additional employee rows?
- I want columns A-B & H-AL to be locked unless a selection has been made in the dropdown for Column C (Employee Name).
- When opening the workbook, Columns A-B & H-AL should already be unlocked if column C (Employee Name) is not blank.
- Columns D-G should remain locked so users can’t mess up the formulas.
- I would like the following columns to be automatically pulled from Sheet2 (Reference) depending on the data selected in Employee Name (which will use the Employee Name list on Sheet2 (Reference) for data validation). I'm not sure if XLookUP or if Index would be best for this.
- Column D – Emp ID
- Column E – Employment Condition
- Column F – Reports To
- Column G – DID
- The data on Sheet2 (Reference) won’t always include the same number of employees so if the formulas for Emp ID, Employment Condition, Reports To, DID, and the data validation for Employee Name can automatically extend down to the last row of data that would be great.
- I will be updating the data on Sheet2 biweekly, but I don't want anyone else to change it so I'd like it to remain locked unless I unlock it.
- Would it be possible to restrict users from cut/copy/paste any of the cells in Columns A-C, P, U, and AB (those are the cells with data validation)? If not, could I set it up so users receiving an error message when they go to cut/copy/paste?
- Instead of using filters, I was hoping to insert slicers at the top so users can automatically filter based on 'Status', 'Trans Rep', and 'Employee Name'.
Thank you so much and I really appreciate any insight you can give.
LOA Spreadsheet.xlsx | ||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | AL | |||
1 | ||||||||||||||||||||||||||||||||||||||||
2 | ||||||||||||||||||||||||||||||||||||||||
3 | ||||||||||||||||||||||||||||||||||||||||
4 | ||||||||||||||||||||||||||||||||||||||||
5 | ||||||||||||||||||||||||||||||||||||||||
6 | ||||||||||||||||||||||||||||||||||||||||
7 | ||||||||||||||||||||||||||||||||||||||||
8 | Status | Trans Rep | Employee Name | Emp ID | Employment Condition | Reports To | DID | FMLA | PPL | MN P&P | Parenthood | Med. | Mil. (Paid) | Mil. | UNC Svs. | Leave Type | Anticipated Leave Start Date | Actual Leave Start Date | Anticipated Leave Return Date | Actual Leave Return Date | Short Term Disability | PPL Start Date | PPL End Date | PPL Exp. Date | Notes (No private medical information) | LOA Request Form Received | Date Leave Req. | RTW/Fitness for Duty Required | Date RTW/ Fitness for Duty Rec'd | Int. Coms for FY 2025 Sent on | Date Rights & Resp Sent | Prov. Leave Granted | Date Prov. Leave Granted | Cert Req. | Date Cert Rec'd | FMLA Approved | Designation Date | Date of Birth | ||
9 | Pending | Shaun | Thomas,Bill | 2 | Part-Time | Xavier,Charles | B04Z002 | X | X | Continuous | ||||||||||||||||||||||||||||||
10 | Inactive | Julie | Smith,Jack | 1 | Full-Time | Gray,Jean | B04Z001 | |||||||||||||||||||||||||||||||||
11 | Active | Sam | Paulson,John | 3 | Intermitt | Rosenberg,Willow | B04Z003 | |||||||||||||||||||||||||||||||||
12 | Active | Becky | Samuelson,Sammy | 7 | Intermitt | Meyers,Seth | B04Z007 | |||||||||||||||||||||||||||||||||
13 | Inactive | Julie | Jacobson,Zach | 4 | Full-Time | Pryce,Wesley | B04Z004 | |||||||||||||||||||||||||||||||||
14 | Pending | Becky | Giles,Rupert | 6 | Full-Time | Kimmel,Jimmy | B04Z006 | |||||||||||||||||||||||||||||||||
15 | Pending | Shaun | Summer,Buffy | 5 | Full-Time | Holmes,Pete | B04Z005 | |||||||||||||||||||||||||||||||||
16 | ||||||||||||||||||||||||||||||||||||||||
LOA |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
AE9:AL29 | Expression | =MOD(ROW(),2) | text | NO |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
F9:F16 | Any value | |
C8 | Any value | |
D8 | Any value | |
E8 | Any value | |
F8 | Any value | |
G8 | Any value | |
H8 | Any value | |
I8 | Any value | |
J8 | Any value | |
K8 | Any value | |
L8 | Any value | |
M8 | Any value | |
N8 | Any value | |
O8 | Any value | |
A9:A15 | List | Pending,Active,Inactive |
U9:U16 | List | N/A,Yes,No |
P8 | Any value | |
P9:P40 | List | Continuous,Intermittent,Cont. & Int.,Reduced Schedule |
AB9:AB16 | List | Yes,No |
B9:B40 | List | Sam,Julie,Shaun,Becky |
C9:C40 | List | =References!$A$2:$A$8 |
D9:D40 | Any value |