How to make username and today static.

J_W

New Member
Joined
Sep 14, 2021
Messages
13
Office Version
  1. 365
Platform
  1. Windows
Good day,
I have created a task tracking sheet used by multiple users. I have two columns that are causing issues. The first one is a username column that when a user checks a checkbox to mark the task as complete, it automatically puts the username in using the following VB code in a module:
VBA Code:
Public Function UserName()
UserName = Environ$("UserName")
End Function
The problem is that the username is overwritten based on whoever opens it. I need the username to be recorded based on who checked the checkbox and then be static and not get overwritten.

My second issue is the column that records the current date using the today() formula. For each task, the date is recorded when the task was completed, but obviously this result is overwritten each time the sheet is opened on a new date. I also need this value to be recorded based on when the checkbox is checked but then be static.

Is this wishful thinking or actually possible to accomplish?

Thanks in advance!
 

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

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,854
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
The problem is you are using Formulas in the cells so it is normal behavior for formulas to be recalculated, then the value will change.
You need to use a procedure which is triggered when the checkbox is clicked and will assign static values to the cells.
For assistance however you will need to provide s9me more details about your setup.
 

J_W

New Member
Joined
Sep 14, 2021
Messages
13
Office Version
  1. 365
Platform
  1. Windows
The problem is you are using Formulas in the cells so it is normal behavior for formulas to be recalculated, then the value will change.
You need to use a procedure which is triggered when the checkbox is clicked and will assign static values to the cells.
For assistance however you will need to provide s9me more details about your setup.
Hi. Thanks for your response. What details do you need about my setup? Will I need to create procedures for each individual checkbox? I have hundreds.
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,854
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
Well, at least give us a clue. And elaborate a bit on the process and the data.
We'll see about the procedures. There are many ways to skin a cat (don't get me wrong, i like animals )
 

J_W

New Member
Joined
Sep 14, 2021
Messages
13
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Well, at least give us a clue. And elaborate a bit on the process and the data.
We'll see about the procedures. There are many ways to skin a cat (don't get me wrong, i like animals )
Ok. :) I have created this task list to track the completion of about 150 tasks that need to be completed each month. This workbook is shared as there are 2-3 people who complete these tasks and for some individual tasks one person does the task and another person verifies it was done correctly. Each task has up to 4 check boxes depending on if it requires one or two people to complete it. The first checkbox verifies that someone has claimed the task and when checked puts the username of the person who claimed it in the adjacent cell. The next checkbox is checked when the task is completed and puts today's date in the adjacent cell. The next set of checkboxes is the same for if a second person is required to complete the task. Here is a screenshot to help visualize. If required, I can remove some confidential data and try attaching the workbook itself.

task_list_screenshot.png


Please let me know if I have supplied enough information.
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,235
Office Version
  1. 2010
Platform
  1. Windows
Are the check boxes Form Controls or ActiveX Controls ?
 

J_W

New Member
Joined
Sep 14, 2021
Messages
13
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Are the check boxes Form Controls or ActiveX Controls ?
Good question. They are form controls and their cell link is the cell they are sitting in.
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,854
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
Nice design, IMHO.
How do these checkboxes work when you filter the data - do they hide or overlap with each other?
How does the usename and date get filled, meaning what happens when the box is clicked? Is it just linked to a cell, and when TRUE the formula evaluates to Username/Date?
Can cells on the sheet be selected?

BTW, what happens to the checkboxes once clicked? Don't the clicked ones become redundant?
 

J_W

New Member
Joined
Sep 14, 2021
Messages
13
Office Version
  1. 365
Platform
  1. Windows
Nice design, IMHO.
How do these checkboxes work when you filter the data - do they hide or overlap with each other?
How does the usename and date get filled, meaning what happens when the box is clicked? Is it just linked to a cell, and when TRUE the formula evaluates to Username/Date?
Can cells on the sheet be selected?

BTW, what happens to the checkboxes once clicked? Don't the clicked ones become redundant?
Thank you! IMHO?

The checkboxes work beautifully when the data is filtered. They are hidden and do not overlap (properties were set to move and size with cells).

When the 'V1 Own' checkbox is set to true, the username is automatically entered because the formula in the adjacent cell is: =IF(F18=TRUE,UserName(),""). The cell link for that checkbox is F18.

Yes, cells on the sheet can be selected.

When a set of checkboxes is clicked (i.e. V1 or V2) the V1 or V2 section of the sheet is greyed out. If only V1 is required, then once the task is complete, the entire row is greyed out. If both levels of verification are required, then each section is greyed out until both are complete and then the entire row is greyed out. In a way, once the task is complete it is redundant because it doesn't need to be looked at anymore, but its value is still useful because the user may want to filter by what is outstanding (column F = false).
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,854
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
One way to fix the values is to change the formulas to values before saving the workbook. However I would not use this, it may have negative performance impact at least.

I don't know if it is the best approach, but in such situation I would use only 4 comboboxes and VBA. 5 if you count the last Flag Column, but it is a bit different.
So 4 CBs (let's call them CB1, 2, 3 and 4), one for each of the cells that have to be filled. Normally the CBs will be hidden out of sight. Once you select a cell the CBs will be moved to the current row, position themselves over the relevant 4 cells. If the cell below a CB is empty - show the CB. Once You click the CB the cell below it will be filled with the necessary value, not a formula and the CB disappears.
You may even control which CBs to be shown depending on which cells are filled for example you don't show CBs 2, 3 and 4 if CB1 is not clicked.In this way you will also need 4 less columns.
 

Forum statistics

Threads
1,144,104
Messages
5,722,507
Members
422,440
Latest member
bhavsarsunil29

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