Excel 2013

Hello Experts

I have an Excel sheet created in MS Excel 2013. There are columns related to the days in a month. So if the sheet created is created for October, i have 31 columns in which daily data are entered datewise.

I want to put a restriction for users on the columns. The restriction will be once data for October 1st is entered then those data  will be locked and cannot be modified once the day is over. The same day user can modify the data but once day is over then it should not allow to modify in the columns whose dates are already passed.
 
Need urgent solution..
Hiren LapsiwalaManager - Information TechnologyAsked:
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.

[ fanpages ]IT Services ConsultantCommented:
Hello,

I noticed you have edited the last line of your question from "Need solution..." to "Need urgent solution...".

As a tip for the future, if you accurately summarise your requirement within the title of the question, rather than simply naming it "Excel 2013", then you may find more "Experts" contribute (quicker, if at all).

This aside,...

The following code is taken from the Worksheet code module, "shtQ_28259782", within the attached workbook:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

' --------------------------------------------------------------------------------------------------------------
' [ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Q_28259782.html ]
'
' Question Channel: Experts Exchange > Software > Office / Productivity > Office Suites > MS Office > MS Excel
'
' ID:               Q_28259782
' Question Title:   Excel 2013
' Question Asker:   hirennl                                   [ http://www.experts-exchange.com/M_4665972.html ]
' Question Dated:   2013-10-07 at 09:46:07
'
' Expert Comment:   fanpages                                   [ http://www.experts-exchange.com/M_258171.html ]
' Copyright:        (c) 2013 Clearlogic Concepts (UK) Limited    [ http://linkedin.com/in/ITServicesConsultant ]
' --------------------------------------------------------------------------------------------------------------

  On Error GoTo Err_Worksheet_Change
  
  If Not (Intersect(Target, [B2:AF2]) Is Nothing) Then
     If CDate(Cells(1&, Target.Column).Value) <> Int(Now()) Then
        Beep
        Target.Select
        MsgBox "Data cannot be changed in this cell.", _
               vbExclamation Or vbOKOnly, _
               ThisWorkbook.Name
        Application.EnableEvents = False
        Application.Undo
     End If ' If Cells(1&, Target.Column) <> Now() Then
  End If ' If Not (Intersect(Target, [B2:AF2]) Is Nothing) Then
  
Exit_Worksheet_Change:

  On Error Resume Next
  
  Application.EnableEvents = True
  
  Exit Sub
  
Err_Worksheet_Change:

  On Error Resume Next
  
  Resume Exit_Worksheet_Change
  
End Sub

Open in new window



Within the workbook you will see I have cells for each date for the current month on row 1, & corresponding data entry cells on row 2.

I have added Conditional Formatting to row 1 to "highlight" today's date (as an indication of which cell on row 2 is able to be changed today).

Presently, on 7 October 2013, cell [H1] is "highlighted", & cell [H2] is the only cell that may be changed in the range [B2:AF2].

BFN,

fp.
Q-28259782.xlsm
0
Hiren LapsiwalaManager - Information TechnologyAuthor Commented:
Thank you sir for the quick help.

In this code, only one row is considered. Can you help with the range of 10 rows in which this validation will be applicable by the code as we have 10 row headings which need to be entered daily. Rest of the criteria remains same for the datewise entry.
0
[ fanpages ]IT Services ConsultantCommented:
Sorry, do you mean you have a range of 10 columns (not rows)?

Perhaps a sample workbook uploaded/attached to this question thread would help me understand your requirements.

The workbook need not contain data, nor be a complete working model, just a "mock-up" of the layout you use & indicating what needs to be restricted within a worksheet.

Failing that, please can you confirm which columns/rows are affected?

Thank you.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Hiren LapsiwalaManager - Information TechnologyAuthor Commented:
Dear Sir,

Attached herewith the file. This file is monthly reporting file in which the users will enter the data everyday as per the headings in the row. Now the option as needed is this users will be allowed to modify data only for 2 days as discussed with them.

For example, In Sheet "DT Reason", if data is entered today 07-Oct-2013 then on the user end they will be allowed to modify or change the entered values upto 09-Oct-2013. On October 10th, they cannot change this data and will have to inform the superior if there is any changes to be done.

Thank you again for the help.
C---M.xlsx
0
[ fanpages ]IT Services ConsultantCommented:
Hi again,

Thanks for the workbook.

Just so we are clear:

Your original question was limiting data entry to a single date (today), but now you are asking to limit for three consecutive days (today, tomorrow, & the next day)?

In your attachment, given today is 7 October 2013, I am presuming that you wish to restrict data entry within the [DT Reason] worksheet to the range [H6:J21].

Not the 10 rows you referred to above; this is sixteen rows (& three columns; 48 cells in total).

Also, no changes should be made to the [C & M Daily Report] worksheet.

Is that correct?


Thank you for your confirmation, &/or further explanation.
0
Hiren LapsiwalaManager - Information TechnologyAuthor Commented:
Dear Sir,

The main clause of restriction will work on date.

Suppose today is october 1 2013. THe user will enter data today and he is permitted. Now tomorrow if user wants to change the value in the rows below i.e. from B6:B21, he is permitted to do. Even he is allowed to change on October 3. But as soon as 2 days are over, he will be restricted to do any entry or modification.

This will be applicable on all date columns. the logic to enter data or modify data will be the date of column+2 applicable on all the data entry rows.
0
[ fanpages ]IT Services ConsultantCommented:
Thank you for confirming that your original question has changed.

I can update your previously provided attachment with the relevant Visual Basic for Application code to meet your (revised) requirements.

However, just to confirm, what will happen beyond 31 October 2013?

If, for example, data has been entered for this date, will the date range (on row 5 of the [DT Reason] worksheet) be extended into November 2013 (column [AG] onwards), or will you then be using a new workbook for the following month's data?
0
Hiren LapsiwalaManager - Information TechnologyAuthor Commented:
Dear Sir,

We will use new workbook in november 2013. But again the clause is same. For october 31, the data can be changed till November 2, 2013 and after that no changes possible in october worksheet as all the dates will be considered as 2 days over and the sheet is locked.
0
[ fanpages ]IT Services ConsultantCommented:
Yes, I understand the logic, but will you be replacing cell [B5] with 1 November 2013, or extending from cell [AG5] onwards?  Hence, cell [AF5] will be blank (as there are only 30 days in November) unless you will retain data in the last few days of October as the month of November progresses, & only clear column [AF] towards the end of November.

Just to confirm: the data restriction is limited to the range [B6:AF21].  I am, perhaps, presuming an issue will exist at the end of a month that will not be present due to your local business practices.

With that in mind, please find attached an updated workbook that addresses your (revised) requirements.

The code from the code module for the [DT Reason] worksheet is as follows:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

' --------------------------------------------------------------------------------------------------------------
' [ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Q_28259782.html ]
'
' Question Channel: Experts Exchange > Software > Office / Productivity > Office Suites > MS Office > MS Excel
'
' ID:               Q_28259782
' Question Title:   Excel 2013
' Question Asker:   hirennl                                   [ http://www.experts-exchange.com/M_4665972.html ]
' Question Dated:   2013-10-07 at 09:46:07
'
' Expert Comment:   fanpages                                   [ http://www.experts-exchange.com/M_258171.html ]
' Copyright:        (c) 2013 Clearlogic Concepts (UK) Limited    [ http://linkedin.com/in/ITServicesConsultant ]
' --------------------------------------------------------------------------------------------------------------

  On Error GoTo Err_Worksheet_Change
  
  If Not (Intersect(Target, [B6:AF21]) Is Nothing) Then
     If CDate(Cells(5&, Target.Column).Value) < Int(Now()) Or _
        CDate(Cells(5&, Target.Column).Value) > Int(Now()) + 2 Then
        Beep
        Target.Select
        MsgBox "Data cannot be changed in this cell.", _
               vbExclamation Or vbOKOnly, _
               ThisWorkbook.Name
        Application.EnableEvents = False
        Application.Undo
     End If ' If CDate(Cells(5&, Target.Column).Value) < Int(Now()) Or CDate(Cells(5&, Target.Column).Value) > Int(Now()) + 2 Then
  End If ' If Not (Intersect(Target, [B6:AF21]) Is Nothing) Then
  
Exit_Worksheet_Change:

  On Error Resume Next
  
  Application.EnableEvents = True
  
  Exit Sub
  
Err_Worksheet_Change:

  On Error Resume Next
  
  Resume Exit_Worksheet_Change
  
End Sub

Open in new window


BFN,

fp.
Q-28259782b.xlsm
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
Hiren LapsiwalaManager - Information TechnologyAuthor Commented:
Dear Sir,

Working fine but only one change. As i mentioned before, if i am doing entry on todays date, then it should allow me to modify back dates 2. i.e. i can modify today for 5th and 6th october including todays date also.

Here it is allowing me to modify 8th and 9th of October which is not required.
0
Hiren LapsiwalaManager - Information TechnologyAuthor Commented:
THANK YOU SO MUCH. WORKING PERFECT.
0
[ fanpages ]IT Services ConsultantCommented:
That seems contrary to what you said here, but maybe I misunderstood what you meant:

This will be applicable on all date columns. the logic to enter data or modify data will be the date of column+2 applicable on all the data entry rows.

OK, here is (I hope) the final revision:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

' --------------------------------------------------------------------------------------------------------------
' [ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Q_28259782.html ]
'
' Question Channel: Experts Exchange > Software > Office / Productivity > Office Suites > MS Office > MS Excel
'
' ID:               Q_28259782
' Question Title:   Excel 2013
' Question Asker:   hirennl                                   [ http://www.experts-exchange.com/M_4665972.html ]
' Question Dated:   2013-10-07 at 09:46:07
'
' Expert Comment:   fanpages                                   [ http://www.experts-exchange.com/M_258171.html ]
' Copyright:        (c) 2013 Clearlogic Concepts (UK) Limited    [ http://linkedin.com/in/ITServicesConsultant ]
' --------------------------------------------------------------------------------------------------------------

  On Error GoTo Err_Worksheet_Change
  
  If Not (Intersect(Target, [B6:AF21]) Is Nothing) Then
     If CDate(Cells(5&, Target.Column).Value) < Int(Now() - 2) Or _
        CDate(Cells(5&, Target.Column).Value) > Int(Now()) Then
        Beep
        Target.Select
        MsgBox "Data cannot be changed in this cell.", _
               vbExclamation Or vbOKOnly, _
               ThisWorkbook.Name
        Application.EnableEvents = False
        Application.Undo
     End If ' If CDate(Cells(5&, Target.Column).Value) < Int(Now()-2) Or CDate(Cells(5&, Target.Column).Value) > Int(Now()) Then
  End If ' If Not (Intersect(Target, [B6:AF21]) Is Nothing) Then
  
Exit_Worksheet_Change:

  On Error Resume Next
  
  Application.EnableEvents = True
  
  Exit Sub
  
Err_Worksheet_Change:

  On Error Resume Next
  
  Resume Exit_Worksheet_Change
  
End Sub

Open in new window



An updated/revision workbook is attached.
Q-28259782c.xlsm
0
Hiren LapsiwalaManager - Information TechnologyAuthor Commented:
Yes Sir, Thanks and working perfect.
0
[ fanpages ]IT Services ConsultantCommented:
You're very welcome.

Good luck with the rest of your project.
0
Hiren LapsiwalaManager - Information TechnologyAuthor Commented:
Hello Sir,

can you help me to restrict the view code option with password in excel so that no user can view and change the code
0
[ fanpages ]IT Services ConsultantCommented:
Hi again,

Open the "Q-28259782c.xlsm" workbook, & when you see the "worksheet" view, use the [ALT]+[F11] key combination to display the "Microsoft Visual Basic for Applications - Q-28259782c.xlsm" window.

Click the "Tools" menu, then the "prjQ_28259782c Properties..." menu item.

You will see the "prjQ_28259782c - Project Properties" dialog box that has two tabs; [General] (selected) & [Protection].

Select the [Protection] tab, & then check (select) the "Lock project for viewing" check-box.

Enter a "Password" & enter it again to "Confirm password" inside the "Password to view project properties" frame (box).

Click [OK] to confirm.

Close the "Microsoft Visual Basic for Applications - Q-28259782c.xlsm" window, use the [ALT]+[F11] key combination again, or simply click on the other open window relating to the same Microsoft Excel "session" application running to display the "worksheet" view again.

Save the workbook.  Close the workbook.  Re-open it & try to view the VB(A)Project.

BFN,

fp.
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
Microsoft Office

From novice to tech pro — start learning today.