Autopopulating cells not working in different situations

Hi gurus

With the help from the gurus in this site,(The details of my request are in https://www.experts-exchange.com/questions/29093251/Sheet-falls-in-to-calculation-mode-when-a-row-is-selected.html), I successfully completed my 3 week trail with my project. Thanks to all contributors especially to Neeraj.

There are very few suggestions, from the users to improve.

The data in columns C to K  in the attached spreadsheet is mainly transferred from another workbook, using a macro in that workbook. There are times we need to manually type data in the same columns. Issue is, when data is transferred from another workbook, Column L is not populating the date. However, if we type in the data in the columns, then the date will auto populate.

Also if I protect the sheet, the date will not auto populate.

Any suggestions to overcome these two things.

Regards
Plan-30-v2.xlsm
Matt MaliAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Hi Matt,

Replace the existing code on WiP Sheet Module with the following one.
Don't forget to change the Password for Sheet in the first line of code. Right now the password is set to "Matt".

Const PW As String = "Matt"

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long
Application.EnableEvents = False
On Error GoTo Skip
ActiveSheet.Unprotect PW
If Not Intersect(Target, Range("C6:C3000")) Is Nothing Then
    r = Target.Row
    If Target.Cells(1) <> "" Then
        If Cells(r, "L") = "" Then
            Cells(r, "L").NumberFormat = "dd/mm/yyyy"
            Cells(r, "L") = Now
        End If
    End If
ElseIf Not Intersect(Target, Range("AA6:AA3000")) Is Nothing Then
    If Target <> "" Then
        If LCase(Target.Value) = "completed" Then
            Target.EntireRow.Hidden = True
        End If
    End If
End If
ActiveSheet.Protect PW
Skip:
Application.EnableEvents = True
End Sub

Open in new window

0
Matt MaliAuthor Commented:
Thanks mate

Date is populating only when data entered manually in column C to k, not happening when data is allocated remotely.

Acknowledge you do not have the option to auto populate the data, any suggestions so that I can try here

Regards
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
If data in column C:K is being populated by another macro considering the column C is not blank, it will also populate the column L with Date.
Where does another macro paste the data on the sheet i.e. in which range?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Also, make sure that your another macro doesn't contain a line like this...
Application.EnableEvents = False

Open in new window

0
Matt MaliAuthor Commented:
Thanks Neeraj

The data is pasted in to column C to K using macro. Column C is not blank

Just noticed if we click the cells in column C , the date is populating in that row. But if we have 20 to 30 tasks allocated , then we need to click in each cell with the new data.

Regards
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Do you mean data is pasted in more than one rows for columns C:K?

I am logging off due to some personal work. I will look into it in a while.
0
Matt MaliAuthor Commented:
Thanks again Neeraj

I noticed your second reply after posting my reply to the earlier suggestion.

The macro contains
1. Skip:Application.EnableEvents = True

Open in new window

0
Matt MaliAuthor Commented:
No worries Neeraj

When ever you can

The tasks are allocated in bulk. For each task, columns C to K will be populated. I tried sending data one at a time, but the date is not populating.

Regards
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Okay, give this a try and see if this works for you.

Const PW As String = "Matt"

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long
Dim cell As Range

Application.EnableEvents = False
On Error GoTo Skip
ActiveSheet.Unprotect PW
If Not Intersect(Target, Range("C6:C3000")) Is Nothing Then
    For Each cell In Target.Columns(1).Cells
        r = cell.Row
        If Cells(r, "C") <> "" Then
            If Cells(r, "L") = "" Then
                Cells(r, "L").NumberFormat = "dd/mm/yyyy"
                Cells(r, "L") = Now
            End If
        End If
    Next cell
ElseIf Not Intersect(Target, Range("AA6:AA3000")) Is Nothing Then
    If Target <> "" Then
        If LCase(Target.Value) = "completed" Then
            Target.EntireRow.Hidden = True
        End If
    End If
End If
ActiveSheet.Protect PW
Skip:
Application.EnableEvents = True
End Sub

Open in new window


I noticed your second reply after posting my reply to the earlier suggestion.

The macro contains

1. Skip:Application.EnableEvents = True
I meant, the macro which paste data on WiP sheet should not contain any line like the below line in the beginning of the code...
Application.EnableEvents = False

Open in new window

Because if that's the case, the Sheet Change Event will not get triggered when another macro paste data onto the WiP Sheet.
0
Matt MaliAuthor Commented:
Thanks Neeraj

After a closer look, I see the Application.EnableEvents=False line is there in the macro that paste data in the WiP. I am pasting the macro that paste data on WiP sheet below. The code below is the one you developed for my other request to move data across different workbooks on a file pathway.

 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    Dim dws2 As Worksheet
    Dim dwb2 As Workbook
    Dim dwbPath As String, Team As String, TeamNo As String
    
    If Intersect(Target, Range("N6:N" & Range("C" & Rows.Count).End(xlUp).Row)) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    On Error GoTo Skip
    Application.EnableEvents = False
    If Target <> "" Then
        TeamNo = ExtractNumber(Target.Value)
        dwbPath = "C:\Secured\Planner Level\Planner " & TeamNo & "\Planner " & TeamNo & ".xlsm"
    On Error Resume Next
    Set dwb2 = Workbooks("Planner " & TeamNo & ".xlsm")
    On Error GoTo 0
    
        If dwb2 Is Nothing And Dir(dwbPath) <> "" Then
            Set dwb2 = Workbooks.Open(dwbPath, False)
        End If
        If Not dwb2 Is Nothing Then
            Set dws2 = dwb2.Sheets(1)
            Range("C" & Target.Row & ":K" & Target.Row).Copy
             dws2.Range("C" & Rows.Count).End(3)(2).PasteSpecial xlPasteAll
            End If
        Range("C" & Target.Row & ":O" & Target.Row).Delete shift:=xlUp
    End If
    Range("N6").Select
Application.ScreenUpdating = True
Skip:
Application.EnableEvents = True
End Sub

Open in new window


Hope this will help you understand the issue a better way.

Regards
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
That's the reason, the Sheet Change Event on WiP sheet was not triggering automatically.
Please replace the code you posted in your last post with this and it will fix the issue.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    Dim dws2 As Worksheet
    Dim dwb2 As Workbook
    Dim dwbPath As String, Team As String, TeamNo As String
    
    If Intersect(Target, Range("N6:N" & Range("C" & Rows.Count).End(xlUp).Row)) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    If Target <> "" Then
        TeamNo = ExtractNumber(Target.Value)
        dwbPath = "C:\Secured\Planner Level\Planner " & TeamNo & "\Planner " & TeamNo & ".xlsm"
    On Error Resume Next
    Set dwb2 = Workbooks("Planner " & TeamNo & ".xlsm")
    On Error GoTo 0
    
        If dwb2 Is Nothing And Dir(dwbPath) <> "" Then
            Set dwb2 = Workbooks.Open(dwbPath, False)
        End If
        If Not dwb2 Is Nothing Then
            Set dws2 = dwb2.Sheets(1)
            Range("C" & Target.Row & ":K" & Target.Row).Copy
             dws2.Range("C" & Rows.Count).End(3)(2).PasteSpecial xlPasteAll
            End If
        Range("C" & Target.Row & ":O" & Target.Row).Delete 'shift:=xlUp
    End If
    Range("N6").Select
Application.ScreenUpdating = True
End Sub

Open in new window

0
Matt MaliAuthor Commented:
Thanks Neeraj

The problem still exists. Is there anything else I need to look for?
Regards
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
What problem?
0
Matt MaliAuthor Commented:
Thanks Neeraj

Date in Column L is populating only when data entered manually in column C, not happening when data is allocated remotely.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
No. That's not the case. I tested both the codes and it's working fine.

All you need is, replace the Sheet Change Event Code on WiP Sheet Module with the code I posted in the following post.
https://www.experts-exchange.com/questions/29098093/Autopopulating-cells-not-working-in-different-situations.html?anchor=a42555280¬ificationFollowed=207211036#a42554912

And replace the code which copies the data onto the WiP Sheet with the one I posted in the following post.
https://www.experts-exchange.com/questions/29098093/Autopopulating-cells-not-working-in-different-situations.html?anchor=a42555280¬ificationFollowed=207211036#a42555245
0
Matt MaliAuthor Commented:
Thanks Neeraj

I think I am taking too much of your time. I blame it on my  knowledge in vba.

Last resort, I have attached both spreadsheets for you to have a look. If time permits, please check whether I have done the right thing or not.

I had two more improvement suggestions on the other vba you gave me, but I will request help another day.

Appreciating your patience.
Team-2.xlsm
Planner-2.xlsm
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
There was issue with the Planner file name. Now it contains a hypen in it's name.

Here are the final codes.

Task Allocation Sheet Module:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    Dim dws2 As Worksheet
    Dim dwb2 As Workbook
    Dim dwbPath As String, Team As String, TeamNo As String
    
    Application.ScreenUpdating = False
    If Intersect(Target, Range("N6:N" & Range("C" & Rows.Count).End(xlUp).Row)) Is Nothing Then Exit Sub
    If Target <> "" Then
        TeamNo = ExtractNumber(Target.Value)
        dwbPath = "C:\Secured\Planner Level\Planner " & TeamNo & "\Planner-" & TeamNo & ".xlsm"
    On Error Resume Next
    Set dwb2 = Workbooks("Planner-" & TeamNo & ".xlsm")
    On Error GoTo 0
    
        If dwb2 Is Nothing And Dir(dwbPath) <> "" Then
            Set dwb2 = Workbooks.Open(dwbPath, False)
        End If
        If Not dwb2 Is Nothing Then
            Set dws2 = dwb2.Sheets(1)
            Range("C" & Target.Row & ":K" & Target.Row).Copy
             dws2.Range("C" & Rows.Count).End(3)(2).PasteSpecial xlPasteAll
            End If
        Range("C" & Target.Row & ":O" & Target.Row).Delete shift:=xlUp
    End If
    Range("N6").Select
Application.ScreenUpdating = True
End Sub

Open in new window


WiP Sheet Module:

Const PW As String = "Matt"

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long
Dim cell As Range
Application.ScreenUpdating = False
Application.EnableEvents = False
On Error GoTo Skip
ThisWorkbook.ActiveSheet.Unprotect PW
If Not Intersect(Target, Range("C6:C3000")) Is Nothing Then
    For Each cell In Target.Columns(1).Cells
        r = cell.Row
        If Cells(r, "C") <> "" Then
            If Cells(r, "L") = "" Then
                Cells(r, "L").NumberFormat = "dd/mm/yyyy"
                Cells(r, "L") = Now
            End If
        End If
    Next cell
ElseIf Not Intersect(Target, Range("AA6:AA3000")) Is Nothing Then
    If Target <> "" Then
        If LCase(Target.Value) = "completed" Then
            Target.EntireRow.Hidden = True
        End If
    End If
End If
ThisWorkbook.ActiveSheet.Protect PW
Skip:
Application.EnableEvents = True
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Matt MaliAuthor Commented:
There you go mate. Excellent. You are a definite life saver.
0
Matt MaliAuthor Commented:
Neeraj is an excellent contributor for this site. He has the knowledge and patience to come up with solutions.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Glad it worked as desired in the end.
Thanks for the feedback Matt! I really appreciate it. :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.