Need Macro Coding for monitoring Time Spend on tasks

[Aswinraj]

Hello Sir,

I have been asked to monitor the time spend for a particular task.

1. In the Excel Attached, Column E should contains the Drop Down Box with options Open and Completed.
2. When we choose Open - Current time should be displayed in alternative (Cell) Column G.
When we choose Closed - Current (End) Time should be displayed in alternative (Cell) Column H.
Total Time Should be displayed in alternative (Cell) Column I.
3. User should not be able to edit these columns - Start time (G Column), End Time (H Column) and Total Time Spend (I Column) - Should be visible but Disabled for editing/modification.

Kindly do need full please., its urgent.., Help me please

 

[vletm]

1) No Drop Boxes - just activate wanted Status cell
2) 1st activation => Open & Start Time
3) 2nd activation => Completed & End Time & Total Time
4) Tricky to edit those ... but possible
5) Possible to clear those ... of course
... for testing

 

[Aswinraj]

@vletm.., I just need a Macro for this.. I had attached the file kindly use the same.

1. When dropdown is selected as OPEN - Current Start Date/Time should be displayed in H, I Columns.
2. When dropdown is selected as Completed/Ouery - End Date/Time should be displayed in H, I Columns.
3. Total Time - End time Minus Start Time.
4. Columns should be editable - C,D,E, G, M
5. Columns should be disabled for editing/modification - A, B, F,H,I,J,K,L.
6 Data Validation - Customer name should not contains numbers, G Column should contain only numbers.
7. Columns A to L is Mandatory to proceed with next line. If any one column is missing then it should not proceed to next line.
8. Name, Request Category, Status should be fetched from Sheet 3.
9. All the formulas should be applicable for minimum 5000 rows.

 

[vletm]

@Aswinraj
1) DropDown don't work ... not yet, but clicking cell works without it.
2) values displays (double values, but there are date and time as You wished)
3) okay
4) all the time
5) after 'Completed'
6) okay, G max 10000
7) Needs more information - not solved
8) okay ( not Status ) and after 'Completed' cannot select 'Query'!
9) okay, there shouldn't be limits

 

[Aswinraj]

@veltm,

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sht As Worksheet
    Dim sPwd As String
   
    If Target.Column = 5 Then
        sPwd = "I will not tell u"
        Set sht = Target.Parent
        sht.Unprotect sPwd
        If Target.Value = "Open" Then
            Target.Offset(, 2) = Time
            Target.Offset(, 3) = ""
            Target.Offset(, 4) = ""
        ElseIf Target.Value = "Completed" Then
            Target.Offset(, 3) = Time
            Target.Offset(, 4) = Target.Offset(, 3) - Target.Offset(, 2)
        End If
        sht.Protect sPwd
    End If
End Sub

Sub unpro()
Unprotect "I will not tell u"
End Sub

Still need the changes please..,
1. this is the code i used for dropdown, since if they click my mistake then it will populate the time and date, so i need a dropdown.
2. Some task might be in pending so i need a pending option in dropdown.
this pending option which should display the current date and time.
3. Columns should be disabled for editing/modification - H,I,J,K,L (Since no one should modify the date time etc.)
4. All the columns should me mandatory field (A to L) - when any of the mandatory field is missed then it should not move to next line/activity.
5.Data Validation - Customer name should not contains numbers, G Column should contain only numbers.

 

[Aswinraj]

Hello Vletm,

I had managed to do some coding regarding the start date and time from selecting the dropdown box.

But i could not make the some changes as listed below,
1. Column A to F should be the Mandatory fields - If any data is missing from A to F column then the user should not allowed to choose the status from G column. If any data is missing in any particular row with Status column updated then Message box should be popup as "Enter Missing data"
2. Data Validation - Customer Name: should not contains any special characters other than "," (Example: Aswin,raj)
Req Rec Date/Time: Should not contains any letters.
Count/Line Items: Should contains only numbers.

I had attached the my sheet with the codings, kindly update the codings for the above needs, Please help me on this.
Password: "I will not tell u"

Please help me on this to complete it.

 

[vletm]

@Aswinraj - Did You even check my file?
Notes for Reply#5 - code - something that could work only and only if task starts and ends in same day and why password, if always unprotects?
That password didn't work! 'My version' don't need password nor protect sheet.
You can go one step backward by clicking wanted row's cell 'O'...
Notes for Reply#6 -
1. Mandatory Fields, check sample.
2. Prev version - Customer Names can select with DropDown from Sheet3
3. Req Rec Date/Time - There isn't such data validation (= both), gotta check later maybe..
'My version', Every cell activation clears all data validations and after that 'the code' makes cells data validations.
I tried to make quick modifications, because some columns had swapped.
Try to check code.
Ideas?

 

[Aswinraj]

Hello sir, The changes which you had send was ok for me but my team is not ready to accept it. They just need as below, so that i am requesting you to help me on those changes below.
1. Column A to F should be the Mandatory fields - If any data is missing from A to F column then the user should not allowed to choose the status from G column. If any data is missing in any particular row with Status column updated then Message box should be popup as "Enter Missing data"
2. Data Validation - Customer Name: should not contains any special characters other than "," (Example: Aswin,raj)
Req Rec Date/Time: Should contains only number, special characters
Count/Line Items: Should contains only numbers.

I had attached the my sheet with the codings, kindly update the codings for the above needs, Please help me on this.
Password: aswin

please please do need full on this..,

 

[vletm]

@Aswinraj
You can copy and paste those data validations from my code.
But: 'Customer Name' comes from list and 'Req Rec Date/Time' don't have neither that kind of data validation.
In the same time, You could see, one possible, how to make those data validations then those will need.
If Your team wants to have data validations ready for 25000 cells, it's okay too, but what would happen if someone wants to make a minor change...
Password works with sheet but not with project!
Sheet has still those 'mistakes' as I have tried to tell.
Should You ask one more time from Your team?

 

[Aswinraj]

Hello Vletm,

I had removed the password for the code, now you can check. Alt +F11

I tried to add the validation code to my codes but i could not make it, so please help me on this too..,
Data Validation:
1. Customer Name: Customer name is an user entry field, customer name will not comes from the list. (Kindly see the attachment with the code and fields so that you can understand)
Customer Name: should not contains any special characters other than "," (Example: Aswin,raj)Count/Line Items: Should contains only numbers.

2. Column A to F should be the Mandatory fields - If any data is missing from A to F column then the user should not allowed to choose the status from G column. If any data is missing in any particular row with Status column updated then Message box should be popup as "Enter Missing data"
Spoken to my supervisor but they approved my sheet which i had attached and also they need these changes.

Please please kindly do need full sir..,
1. Column A to F should be the Mandatory fields - If any data is missing from A to F column then the user should not allowed to choose the status from G column. If any data is missing in any particular row with Status column updated then Message box should be popup as "Enter Missing data"
1. Column A to F should be the Mandatory fields - If any data is missing from A to F column then the user should not allowed to choose the status from G column. If any data is missing in any particular row with Status column updated then Message box should be popup as "Enter Missing data"

 

[vletm]

@Aswinraj
Okay, Your Supervisor has to be right.
I tried to make Your Supervisor satisfied until not me ...
my opinion is that there are many things to change.
I have tried to give ideas, but no way, Your Supervisor accepts those too!
Good Luck to both!
Modifies:
[Customer Name] accepts ONLY letters A..Z, a..z and ','. (*
[Count/Line Items] accepts ONLY numbers 0..9 (*
---> (* all other characters will hide with message!
'Mandatory Fields' gives "Enter Missing data", if some or all field are missing.
Also it would be a little tricky to choose status from column G.

 

[Aswinraj]

Hello Vletm,

Thanks for your help., I had checked the sheet and it looks convenience.., before showing it to my supervisor and team., i would like to have one minor change
in Customer Name:
We use to copy paste the customer name from Outlook to excel, while doing it
the name will be reflecting in excel as K, Srepathi <srepathi.k@he.com>

In your code- if it deletes the Non Valid characters then while we copy the name from outlook it looks like K,Srepathisrepathikhecom - which should not be.

I think it would be better to provide a message as "Enter valid customer name" (Example: K, Srepathi - User should able to enter characters, "," )

Also in Count/Line items - It is not accepting "0" value.., So kindly help on this as Count/Line items should accept from 0 to infinity..,

Kindly do needful...,

 

[vletm]

@Aswinraj .. do modifications:
1)

If (ax = 3 And (xx = 44 Or (xx >= 65 And xx <= 90) Or (xx >= 97 And xx <= 122))) Or _

to

If (ax = 3 And (xx = 32 Or xx = 44 Or (xx >= 65 And xx <= 90) Or (xx >= 97 And xx <= 122))) Or _

2)

MsgBox ("NON valid character[s] has deleted!")

to

MsgBox ("Enter valid " & .Cells(1, ax) & " !")

'Count/Line items' ... here, it accepts 'zero' and there is no limits of value!
I couldn't use 'normal data validations'.

 

[Aswinraj]

Hello vletm,

I done those modifications.,
Count/Line items - When i enter the "0" and click status column it shows the message"Enter Missing data". but when i change the "0" to "1" there is no error message.
So that i requested you to modify the code as it accepting "0".,
Which means when i enter "0" and click status column it should allow me to change the status.

 

[vletm]

@Aswinraj - That is a different case!
The zero has accepted to 'Count/Line items'!
For the new case, change this ...
[case]
If .Cells(ay, x) = Empty Then
[/case]
to this ...
[case]
If .Cells(ay, x) = Empty And .Cells(ay, x) <> 0 Then
[/case]

 

[Aswinraj]

@vletm, I had made the changes but found some issues in Mandatory field as well as validation.

1. In row 8 - Count/Line Items - i have entered Letters and given next arrow and made the changes in Status (Where it need to should show error since i entered Characters but it doesnt).
2. In row 9 - Count/Line Items - I had not entered any thing in column F but it is allowing to choose the Status from Status column.

I have highlighted those lines in the attachment.

Can you help me on this..., please..,

 

[vletm]

1) select [F8] => press twice F => press <ENTER> => continue
Checking occurs only after <ENTER>
2) do modification

    For x = 1 To 6
'        If .Cells(ay, x) = Empty Or .Cells(ay, x) <> 0 Then
        If .Cells(ay, x) = Empty Then
            If x = 6 And Len(.Cells(ay, x)) = 1 And .Cells(ay, x) = 0 Then
            Else
                If CAN_DO Then .Cells(ay, x).Select
                CAN_DO = False
            End If
        End If
    Next x

 

[Aswinraj]

@vletm, Instead of doing the Validation only by pressing Enter, Is there any other way to do the validation like - when user entered invalid data and move on to next cell using tab or right arrow it should throw the error message??

If so can you help me on this.., This is the only concern left for me before taking this macro to my team please..,

 

[vletm]

@Aswinraj - Did You read Reply #13's the last row?
Because You have protected the sheet,
it is not possible to use 'Excel's data validation'.
If You wanted to use 'Excel's data validation', so please use.
Same time, You should delete that columns 'my made like data validation code'.
With Your version of this, You have to do all 'Excel's data validations' manually Yourself, as You wanted with Your Supervisor. With 'my version', the code do it!

 

[Aswinraj]

@vletm, then can you protect the sheet as user should not able to edit Start Date/Time, End Date/time, Total Time, Status, Name and Category?
and then the validation as requested in Reply 18#.

Please do needful on this by adding those changes to the attachment.

 

[vletm]

@Aswinraj - think!
Where is a list of users or is there only one user?
If I'll make those, without information of user, only I can edit that sheet!
I should use 'my version'. I should made some of previous modifications and find out 'Status'-column's needed function; there are new unknown functions.
The biggest challenge is that 'my version' wasn't accepted by Your Supervisor!
I gotta think how to do.

 

[Marc L]

Hi vletm !

It's the reason why I try to manage to not write a single codeline
until all the need seems crystal clear …

Congrats for your patience !
​

 

[Aswinraj]

@vletm, I had given the user names in sheet 2, which has been hided by coding. Ok finally if possible use your version and do the changes as i had asked, but status column should comes only with dropdown. I will try my supervisor to convenience.

 

[Aswinraj]

Hello Marc, Since i was not an expert, but started with Crystal Clear points on what i need, and then to achieve that i need an expert helping mind person (like Vletm) to discuss and get some ideas in and out to finalize the coding and excel macro. Which i think Vletm is great unless not like others.

 

[vletm]

@Aswinraj
Do that UserList work with OperationSystem's UserName
like user = Environ("UserName")? or something else?
It cannot be only list of someones names!
Everybody else cannot use at all this or for what purpose is that list?
Status: What purpose is the newest two Statues?
Or is it like: 'N/A' -> Open (s) -> InProgress -> Query -> Complete (e) ?
I can try to help, only if I can get answers.