VBA Code for automated actionlog

[JAZ91]

Hi
I'm trying to create an Actionlog for my company that are a little more automated than our current one.

I have a string of headlines placed in each column as following : ID, Building, Unit, Room, Category, Author, Date Created, Description, Discipline, Supplier, Deadline date, Responsible, Date item complete, Remarks, Type, Status.
(In my sheet placed in row A3:P3, with and empty row below that so the codes start from A5:P5)


So far I have a macro called new_action that will assign new ID number, author, date created and a default deadline date 7 days from the creation date.
If possible i would like to remove the macro button and just have this done each time a new row is inserted or a new id is entered (secondary request)

I also have a macro for inserting comments, which will add date and user in before the comments. However I am attempting (without luck) to automate this a bit more so whenever i add text to a cell in the description column it automatically adds date and user before the comment. The cell should be able to hold multiple comments from different dates and users. (primary request)

Furthermore I would also like to color code a range in the rows of the actions so that with approaching deadline it goes from fx green-->yellow-->red, with the exception of that if the status of the action is closed it should be grayed out.
I am aware this can be done with conditional formatting but i might add more criteria at a later date and I also prefer it as code.

I dont know if im asking of to big of a task or if it can be done without overloading the document.
But i hope to get a little closer to what i imagine at least.

kind regards

Jakob


Below is this current macro code i have in the sheet which is activated with macro buttons when needed.
Ideally for me only the clear_actionlog has a button, but i might ask for to much



______________________________________________________________________________________________

Sub Insert_Comment()
    Application.ScreenUpdating = False

    Dim r As Range
    Dim col As Long
    Dim i As Variant

    'If ValidRow() Then
        col = 8 ' column for comments
        Set r = Range(Cells(ActiveCell.Row, col), Cells(ActiveCell.Row, col))
        r.Select
        r.Formula = DateValue(Now) & "  " & Application.UserName & ": " & s & Chr(10) & r.Value
        Application.SendKeys "{F2}"
       
    'Else
       ' InvalidRowMessage
    'End If
    For i = 1 To 25
    SendKeys "{Up}"
    Next i
    SendKeys "{End}"

Application.ScreenUpdating = True
End Sub

______________________________________________________________________________________________

Sub New_Action()
    Application.ScreenUpdating = False
  
  
    Range("A3:P3").AutoFilter
    Range("A3:P3").AutoFilter

    Rows("5:5").Insert Shift:=xlDown
   
' Format for new row
    Rows("6:6").Copy
    Rows("5:5").PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
 ' End format new row
 
    Cells(5, 1).Value = Cells(6, 1) + 1
    Cells(5, 5).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="A:Important, B:Necessary, C:Not in project"
    Cells(5, 6).Value = Application.UserName
    Cells(5, 7).Value = DateValue(Now)     ' Inserted
    Cells(5, 9).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="01 Process, 02 Mechanical, 03 Instrumentation, 04 Electrical, 05 Automation"
    Cells(5, 11).Value = DateValue(Now + 7) ' Due
    Cells(5, 15).Value = "Action"
    Cells(5, 15).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="Action, Decision, Information"
    Cells(5, 16).Value = "Open"
    Cells(5, 16).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="Open, Closed, On hold"

       
   
    'Range("P7").Value = "=IF(IFERROR(VLOOKUP(RC[-10],Dropdowns!R29C3:R148C7,2,FALSE),RC[-10])=0,RC[-10],IFERROR(VLOOKUP(RC[-10],Dropdowns!R29C3:R148C7,2,FALSE),RC[-10]))"
   
   ' For i = 0 To 4
    '    Cells(7, 11 + i).FormulaR1C1 = "=IF(R[0]C7=R2C,1,0)"
    'Next i
   
   
    Application.CutCopyMode = False
    Rows("7:7").EntireRow.AutoFit
    Cells(7, 2).Select
   
   
Application.ScreenUpdating = True

End Sub

Sub Actionlog()
Application.ScreenUpdating = False

    Sheets("Actionlog").Select
    Range("A1").Select
    Application.ScreenUpdating = True
End Sub

______________________________________________________________________________________________

Sub Clear_Actionlog()
  
   Worksheets("Actionlog").Columns.EntireColumn.Hidden = False
   Worksheets("Actionlog").Rows.EntireRow.Hidden = False
   Worksheets("Actionlog").Range("A5:P115").ClearContents
   Range("A5:P115").Interior.Color = xlNone

End Sub

______________________________________________________________________________________________

Sub threecf()
Dim rg As Range
Dim cond1 As FormatCondition, cond2 As FormatCondition, cond3 As FormatCondition
Set rg = Range("A5:P115", Range("A5:P115").End(xlDown))

'clear any existing conditional formatting
rg.FormatConditions.Delete

'define the rule for each conditional format
Set cond1 = rg.FormatConditions.Add(xlCellValue, xlEqual, "Closed")
Set cond2 = rg.FormatConditions.Add(xlCellValue, xlEqual, "Open")
Set cond3 = rg.FormatConditions.Add(xlCellValue, xlEqual, "On hold")

'define the format applied for each conditional format
With cond1
.Interior.Color = vbGreen
.Font.Color = vbBlack
End With

With cond2
.Interior.Color = vbRed
.Font.Color = vbWhite
End With

With cond3
.Interior.Color = vbYellow
.Font.Color = vbRed
End With

End Sub

 

[OaklandJim]

This is a lot to ask but I'd like to try to assist. I do not quite understand the process though. To start, have a look at the worksheet event called



I'm trying to create an Actionlog for my company that are a little more automated than our current one.

I have a string of headlines placed in each column as following : ID, Building, Unit, Room, Category, Author, Date Created, Description, Discipline, Supplier, Deadline date, Responsible, Date item complete, Remarks, Type, Status.
(In my sheet placed in row A3:P3, with and empty row below that so the codes start from A5:P5)


So far I have a macro called new_action that will assign new ID number, author, date created and a default deadline date 7 days from the creation date.
If possible i would like to remove the macro button and just have this done each time a new row is inserted or a new id is entered (secondary request)

I also have a macro for inserting comments, which will add date and user in before the comments. However I am attempting (without luck) to automate this a bit more so whenever i add text to a cell in the description column it automatically adds date and user before the comment. The cell should be able to hold multiple comments from different dates and users. (primary request)

Furthermore I would also like to color code a range in the rows of the actions so that with approaching deadline it goes from fx green-->yellow-->red, with the exception of that if the status of the action is closed it should be grayed out.
I am aware this can be done with conditional formatting but i might add more criteria at a later date and I also prefer it as code.

I dont know if im asking of to big of a task or if it can be done without overloading the document.
But i hope to get a little closer to what i imagine at least.

kind regards

 

[OaklandJim]

Sorry for the previous post that should not have contained your post. This is a lot to ask but I'd like to try to assist. I do not quite understand the process though.

So far I have a macro called new_action that will assign new ID number, author, date created and a default deadline date 7 days from the creation date.
If possible i would like to remove the macro button and just have this done each time a new row is inserted or a new id is entered (secondary request)

How do you know who the author is? How do you know what ID to assign. This might be doable using the worksheet Change event. Here is a microsoft overview: Worksheet.Change event (Excel).

I also have a macro for inserting comments, which will add date and user in before the comments. However I am attempting (without luck) to automate this a bit more so whenever i add text to a cell in the description column it automatically adds date and user before the comment. The cell should be able to hold multiple comments from different dates and users. (primary request)

Do you mean adding remarks? If not I do not see a column for comments? Using the Change event you can test to determine if a change made is in a description cell. How do you know who (what user) added the "comment"?

Furthermore I would also like to color code a range in the rows of the actions so that with approaching deadline it goes from fx green-->yellow-->red, with the exception of that if the status of the action is closed it should be grayed out.

What does red mean, yellow, green? (I.e how many days out?) How do you know if it is closed? By Grayed out you mean FONT color?

Posting some data would help. Use the XL2BB addin. See here: XL2BB - Excel Range to BBCode

 

[JAZ91]

Sorry for the previous post that should not have contained your post. This is a lot to ask but I'd like to try to assist. I do not quite understand the process though.


How do you know who the author is? How do you know what ID to assign. This might be doable using the worksheet Change event. Here is a microsoft overview: Worksheet.Change event (Excel).



Do you mean adding remarks? If not I do not see a column for comments? Using the Change event you can test to determine if a change made is in a description cell. How do you know who (what user) added the "comment"?


What does red mean, yellow, green? (I.e how many days out?) How do you know if it is closed? By Grayed out you mean FONT color?

Posting some data would help. Use the XL2BB addin. See here: XL2BB - Excel Range to BBCode

Hi OaklandJim

Yes you are right it is a lot I know and I knew it was a gamble to ask that much in one go but I thought I might as well give it a shoot.
To make it less I tried to prioritize my question so people could choose to answer just one

To answer your questions.

• The author/creator of the new action is know by the user logged into MS Office excel. And it is my understanding that the code for that is "Cells(5, 6).Value = Application.UserName" in my case.
  
  
• The new ID assigned is just the next in the numerical order so i.e. 1,2,3,4 then if a new action is put in is assign it as 5. I figured by trial and error that this is shown in my current macro with "Cells(5, 1).Value = Cells(6, 1) + 1"
  
  
• For the comment question, then yes im sorry I should have said that this should apply to remarks and perhaps also the description column. knowing the user is the same as above.
  
  
• regarding the coloring, what I meant was that is the "Status" column is stating the action/task is closed then the background color of the row or part of the row would be grayed out,
  The red, yellow and green color should on the other hand reflect how close the current date is to the deadline date. So let in example say that 3 days are left before the deadline, then the background color of row or some of the cell in that row should be red.
  Likewise if lets say 7 days to deadline then yellow and everything above 7 days could have a green background color.
  (I am saying that it could be either the entire row or part of it should have background color due to request from my manager. I know it is probably easier if it was the whole row but I can imagine a range also could be specified in this case).

I hope this answered the questions to my question.

And thank you I'm really glad and appreciative about the quick reaction and the time you have taken to read my issue.

best regards

Jakob

 

[JAZ91]

Sorry for the previous post that should not have contained your post. This is a lot to ask but I'd like to try to assist. I do not quite understand the process though.


How do you know who the author is? How do you know what ID to assign. This might be doable using the worksheet Change event. Here is a microsoft overview: Worksheet.Change event (Excel).



Do you mean adding remarks? If not I do not see a column for comments? Using the Change event you can test to determine if a change made is in a description cell. How do you know who (what user) added the "comment"?


What does red mean, yellow, green? (I.e how many days out?) How do you know if it is closed? By Grayed out you mean FONT color?

Posting some data would help. Use the XL2BB addin. See here: XL2BB - Excel Range to BBCode

Here is the file I currently have
I tried to use google drive but it don't allow me to transfer the macro's I currently have in the document.

But basically it is just the headlines I already described, and the code I also pasted in my original post which is put into macro buttons.

https://file.io/0amV25OQcb9j

Hope this helps

best regards

Jakob

 

[JAZ91]

Here is the file I currently have
I tried to use google drive but it don't allow me to transfer the macro's I currently have in the document.

But basically it is just the headlines I already described, and the code I also pasted in my original post which is put into macro buttons.

https://file.io/0amV25OQcb9j

Hope this helps

best regards

Jakob

I just realized the link doesnt work. Plus i also read that i am not supose to post links here so please disregard the link.
I dont have the addon to upload material since it a company computer and security basically doesnt allow me to install anything without approval it might not be possible.
I will however try to see if I can do it from my private pc later.

 

[Fluff]

We have no problem with people posting links to files when looking for help.
However I would recommend using sites such as OneDrive, Dropbox, Google Drive as the site you used is blocked by my security as it's been reported for phishing

 

[JAZ91]

Posting some data would help. Use the XL2BB addin. See here: XL2BB - Excel Range to BBCode

I have taken a screenshot, where you can see the macro buttons and headlines.
I have added come text to fill it out so you get some context. hope this helps a bit. the code for the macro can be seen in the original post

 

[OaklandJim]

There has to be a good way to tell if a new row is added at the bottom of the data. Detecting an INSERTED row is easy but adding that row at the bottom less so. Which two columns are the most likely to be filled in first for a new row?

 

[JAZ91]

There has to be a good way to tell if a new row is added at the bottom of the data. Detecting an INSERTED row is easy but adding that row at the bottom less so. Which two columns are the most likely to be filled in first for a new row?

Well the way it is done in my sheet is that the new row is inserted right under the headers. I have made a dropbox file and will attempt to share the file again, since I think it is easier to understand and see (code) what is happening. But in my file the ID number could be said to be the first input along with some other default information such as originator, date created and a default 7 days deadline.

If I had to simplify my long original request it would be to get rid of the macro buttons but keep the functions. And then some changes regarding the coloring thing I mentioned lastly(in original request).

Actionlog_20220928.xlsm

Shared with Dropbox

www.dropbox.com