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]

I'm working on a new version for you. Maybe today. No buttons. Merely add new row at bottom. Will keep you informed.

 

[OaklandJim]

AutoUpdateTable_ver5.xlsm

This workbook is supposed to do what you wanted. It took a while so I did not test it enough, but hey, the price is right. So expect issues.

I tried giving a more detailed post but got a security issue!?

 

[OaklandJim]

Here is the longer message...

You'll have to give the workbook a whirl with real data for good shake-out. Almost certainly you'll find issues. I'll try to help you out.

Consider the following topics.

If you paste a bunch of existing data at once to populate the data table it may take a few tries to get everything synced up. Maybe try working with it a bit first before putting your data in.

I use range names a lot. For example I added names for various header cells in the data. Those names are good because code does not need to refer to a cell by its address. I use those names when doing processing of 1. data row was added or removed, 2. coloring status cells and 3. add author and date to comments. Maybe cycle through the headers manually and you'll see the name show up in the upperleft data field that normally shows the cell address.

I also make "advanced" use of names -- DataRowsPrevious and NextID -- to keep track of 1. how many data rows there are and 2. the next ID to use. DataRowsPrevious is needed because code needs to know how many data rows existed before any data rows are added or removed.

I gave a "code name" to the "Master" worksheet. You can see that in the project explorer window in the VBA code editor. As the name implies a code name is used in code to refer to the respective worksheet rather than the worksheet name shown on the worksheet tab. One nice result of using code names for worksheets is that if the name on the tab changes the code name does not so code can still refer to the worksheet using the code name.

When changing the content of the worksheet that I named Master the Change for event (sub) for that worksheet fires. That Change event sub calls 1. the sub that handles a data row was added or removed, 2. the sub that colorizes the status column and 3. the one that adds date and author to comments. Those three subs are 1. RowAddOrDelete 2. StatusCellsColor and 3. AddDateAndNameToDescription.

Another topic is that I added a sheet called Control that has a few settings and buttons. I'll leave it to you to explore that.

I set it up so that when rows are added the formatting in the top row is used to format the other rows.

I tried to cover most realistic use scenarios but I have to imagine that I did not get them all so we have to expect issues.

 

[JAZ91]

Here is the longer message...

You'll have to give the workbook a whirl with real data for good shake-out. Almost certainly you'll find issues. I'll try to help you out.

Consider the following topics.

If you paste a bunch of existing data at once to populate the data table it may take a few tries to get everything synced up. Maybe try working with it a bit first before putting your data in.

I use range names a lot. For example I added names for various header cells in the data. Those names are good because code does not need to refer to a cell by its address. I use those names when doing processing of 1. data row was added or removed, 2. coloring status cells and 3. add author and date to comments. Maybe cycle through the headers manually and you'll see the name show up in the upperleft data field that normally shows the cell address.

I also make "advanced" use of names -- DataRowsPrevious and NextID -- to keep track of 1. how many data rows there are and 2. the next ID to use. DataRowsPrevious is needed because code needs to know how many data rows existed before any data rows are added or removed.

I gave a "code name" to the "Master" worksheet. You can see that in the project explorer window in the VBA code editor. As the name implies a code name is used in code to refer to the respective worksheet rather than the worksheet name shown on the worksheet tab. One nice result of using code names for worksheets is that if the name on the tab changes the code name does not so code can still refer to the worksheet using the code name.

When changing the content of the worksheet that I named Master the Change for event (sub) for that worksheet fires. That Change event sub calls 1. the sub that handles a data row was added or removed, 2. the sub that colorizes the status column and 3. the one that adds date and author to comments. Those three subs are 1. RowAddOrDelete 2. StatusCellsColor and 3. AddDateAndNameToDescription.

Another topic is that I added a sheet called Control that has a few settings and buttons. I'll leave it to you to explore that.

I set it up so that when rows are added the formatting in the top row is used to format the other rows.

I tried to cover most realistic use scenarios but I have to imagine that I did not get them all so we have to expect issues.

Thank you very much Jim

I will look at it and play around a bit and get back to you when I figured out how it works.
Thank you for the great effort

Best regards
Jakob