• 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.

Need Macro Coding for monitoring Time Spend on tasks

@vletm, In the Excel, the "Name" Dropdown list - It will provide the team names given in sheet 2.
The "Category" Dropdown list - It will provide the type of activity user is doing, those list is given in sheet 2.
"Status: Open" - When user started doing the request.
"Status:Query" - When user get back to the requester for more details.
"Status:Complete" - When user complete the request.
"Status: Inprogress" - When user stated the request and before completing the request he/she may go for break/meeting etc., On that time user should choose inprogress and while coming back again he/she should reopen the request.

I believe, now you got the answers which you need...,
 
@Aswinraj
Your answer was that everyone can pick any name from that list.
That means, everybody can use this, am I right?
So, no need to limit users!
Test,
Code: 
Sub UN()
   msgbox(Environ("Username"))
End sub
I asked something like that.
=> Status: Query
 
@vletm, in the "Name" Dropdown box - it contains the name of my team members, so they will choose their name only.

Status:Query - Example: if user provided less amount of information, then we need to get back to user in order to get more details. On that time we use to choose Status as "Query"
 
@Aswinraj
A: You can copy those three lines anywhere in module and run it.
Q1: ... waiting
Q2: Have You had any idea how to 'ReSet' all 'data'
(to empty table for new project)?
=> Status: Query
 
@Aswinraj
For Preview and prechecking ...
There is still something to change and also few parts are missing...
Notes, Ideas ... ?
=> Status: Query
 

Attachments

  • Copy of AMS Sheet 2015 .xlsb
    44.4 KB · Views: 3
I just need a help from you., The sheet has been finalized with the team but one concern was, In the Protected sheet - Column A to F and Column M should be Autofit when user enter the data and moves to another cell.

Can you please provide me the code and where the code should be placed?

I Tried with the code below in ThisWorkbook, but it autofits all the columns but i need only particular columns to be autofit when user enters the data.

Code: 
PrivateSub Workbook_SheetChange(ByVal Sh AsObject,ByVal Target As Range)
Application.ScreenUpdating =False
ActiveSheet.Columns.AutoFit
EndSub
Please help me...,

Mod Edit: Tag the code plz
 
Last edited by a moderator:
@Aswinraj
change 'ActiveSheet.Columns.AutoFit' line
to effect only those columns that You need,
not to all columns as You have done!
BTW - my previous version wasn't ready...
I hope that You found things to change.
 
@vletm.., May i know what line should replace "Activesheet.Columns.AutoFit" in order to Autofit the selected columns from A to F.

Please help..,
 
@vletm, i think you doesnt get my point.., I wrote the code 'ActiveSheet.Columns.AutoFit' now what is the code should replace 'ActiveSheet.Columns.AutoFit inorder to get Autofit the selected columns from A to F.
 
@Aswinraj
1) If You asked 'May i know what line should replace "Activesheet.Columns.AutoFit" in order to Autofit the selected columns from A to F.' (reply#35.) I have to answer to Your question, what line should You replace, as I did!
2) Reply #37, now You asked new code for that line.
3) I won't use "AutoFit" in that place!
Do You have any idea when and how often those columns will 'AutoFit'?
Have You?
If You really want to do so, that's okay, but not with me.
I can only try to give an idea that
You should try to record this part of Macro and copy&paste it somewhere.
I hope that You would see what will happen ...
 
Whenever the user enter the data, it should Autofit.., i tried recording the macro and placed it in the ThisWorkbook but it was not working.
 
@Aswinraj
You haven't think!
Have You got any idea, with case that someone write ~256 letters text to any of those columns? Have You?
Do You really want 'AutoFit' to act all the time?
Try to sit and think!
 
I was think about this whole day..,

User might enter 50 letters including numbers and special characters in any of the colums A To F.
Yes AutoFit should be enabled all the time.
 
Hello Vletm,
Anyupdate.., i answered to your question...

Also another issue i am facing..,

I had changed coding as below, but error occurs as - Application definition or object definition error in WorkBook_Deactive.



Code: 
Private Sub Workbook_Activate()
Sheets(1).Select
ActiveWindow.DisplayGridlines = True
ActiveWindow.WindowState = xlMaximized
Application.DisplayFormulaBar = False
Application.DisplayStatusBar = True
ActiveWindow.DisplayHeadings = True
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
ActiveWindow.DisplayVerticalScrollBar = True
ActiveWindow.DisplayHorizontalScrollBar = True
ActiveWindow.DisplayWorkbookTabs = False
End Sub

Code: 
Private Sub Workbook_Deactivate()
Sheets(1).Select
ActiveWindow.DisplayGridlines = True
ActiveWindow.WindowState = xlMaximized
Application.DisplayFormulaBar = True
Application.DisplayStatusBar = True
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
ActiveWindow.DisplayVerticalScrollBar = True
ActiveWindow.DisplayHorizontalScrollBar = True
ActiveWindow.DisplayHeadings = True
ActiveWindow.DisplayWorkbookTabs = True
End Sub

Mod edit: Tag code!
 
Last edited by a moderator:
Test to hide one by one those line to find out which line makes error.
You could start with 'Ribbon'-line.
 
You must log in or register to reply here.
Back
Top