List Object / Protecting Cells / Conditional Formatting

[rikkdh]

Help please, Jan Karel Pieterse suggested this was the place to be to get help... so here goes!

I have a spreadsheet where users input a number of data items. It then checks to see how many of these are duplicated across the last 10 days within the spreadsheet and then flags these on a scale and informs the user with flags if they are duplicated and need to be checked.
The table (List Object) has a date in the 1st Column (A) - TODAY() of a spreadsheet auto populates so the user doesn't need to input it.... which is set to auto filter on the last 10 days worth of data.

1) If the user deletes the date by mistake then the code bugs out as the calculations fail..

- Is there a way to protect column A as part of the list object, to stop the user deleting the date and making the code bug out!?
- Is there are way to set conditional formatting of Column A as the date that will allow the auto filtering to be maintained...? Seems to conflict between two date formats when i try this!

Also looking to change the range of Today Date from being one dimensional Today() to Today()+1, but can deal with one thing at a time if i get the 1st bit solved!! DOnt want walk before i can crawl!!

I have attached some of the code snippets, not all of the code as there's quite a lot, attaching the spreadsheet isn't an option i'm afraid , any help would be greatly appreciated, this is very frustrating!

Thanks

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

'   Switch Screen Off

With Application
  .DisplayAlerts = False
  .ScreenUpdating = False
  .Calculation = xlCalculationManual
End With

Dim EmptyCount As Variant
Dim EmptyCells As Range
Dim LastRow As Variant
Dim Calc_Status As String
Dim ActiveCellRow As Variant
Dim ActiveCellCol As Variant
Dim Amber_Cr As Variant
Dim Red_Cr As Variant

Dim tDate As String
Dim fDate As String


LastRow = ""
EmptyCount = ""
ActiveCellRow = ""
ActiveCellCol = ""
Calc_Status = ""

'   Stores last cell

  ActiveCellRow = ActiveCell.row
  ActiveCellCol = ActiveCell.Column

'    Calculate Last_Row Named Range on Control Sheet

  Worksheets("Controls").Range("Last_Row").Calculate

  LastRow = Sheets("Controls").Evaluate("Last_Row")
  Calc_Status = Sheets("Controls").Evaluate("Full_Calc")

  EmptyCount = Application.WorksheetFunction.CountBlank(Range("Change_Area"))

  If EmptyCount = 0 Then


      '   Make Sure Date is present First Column

          Dim DateCheck As Variant
          Dim DateValue As Date

          DateValue = Sheets("Controls").Evaluate("TodayDate")
          DateCheck = Sheets("book1").Evaluate("Date_Check")

          If DateCheck = "" Then

              Sheets("book1").Cells(LastRow, 1).Value = DateValue
              Cells(LastRow, 1).NumberFormat = "dd/mm/yyyy"

          End If

          Worksheets("Controls").Calculate

          With Application
              .DisplayAlerts = False
              .ScreenUpdating = False
              .Calculation = xlCalculationManual
          End With

      '   Delete any Conditional Formatting Applied to Sheet

          Cells.FormatConditions.Delete

      '   Apply  Conditional Formatting

Code: Select all

Worksheets("book1").Activate
              tDate = Sheets("Controls").Evaluate("First_Row_Date")
              fDate = Format(tDate, "yyyy/mm/dd")
              Worksheets("book1").ListObjects("book1_TABLE").Range.AutoFilter
Field:=1, Criteria1 :="=" , Operator:=xlor, Criteria2:=">=" & fdate

[HansV]

Welcome to Eileen's Lounge!

Would it be feasible to protect the worksheet? You could initially have most or all cells unlocked, then in the Worksheet_Change event lock cells in column A as they are being filled.

I don't understand your second question. What does have conditional formatting have to do with filtering?

[rikkdh]

Thanks for the quick response Hans,

Have tried protecting cells / ranges without success before... probably because the list object table adds a new row dynamically to the range being used for calculations only when the user starts entering data into the LastRow....

Happy to try code if you think it would work... i'm a complete VB novice so have cobbled stuff together and very much trying to make it work!

The comment about conditional formatting was a suggestions someone else had ... about making Column A a drop down from the Range Today defined in my Control Sheet as Today(), thus ensuring that a user could only select the date from that drop down. It didn't prevent the user from deleting the value in the cell, although would have helped with the Today +1 issue that I'm looking to solve whereby I can allow users to future date there entires!

Feel like i'm going round in circles and that there must be an easy way of doing this...

Thanks again

[Rudi]

If the date in column A is being generated automatically, you could try hiding the column, and if you need a date displayed, use column B and place a link to column A using "=A2". The formulas in the rest of the row can reference column A and will not be influenced if the link gets deleted accidentally.

I think you mean Data validation (which puts a drop down in a cell), not Conditional Formatting...

[HansV]

Hmmm, I see - a table doesn't work well in a protected sheet, even if all cells are unlocked. So protection is not a good idea.

You could use code in the Worksheet_Change event to prevent the user from clearing cells in column A:

Code: Select all

    If Target.Count = 1 And Not Intersect(Range("A:A"), Target) Is Nothing Then
        Application.EnableEvents = False
        If Target.Value = "" Then
            Application.Undo
        End If
        Application.EnableEvents = True
    End If

(You already have code to set Application.EnableEvents to False, and I presume also to set it to True, so you wouldn't have to do that in the above code).

Having in-cell dropdowns is data validation, not conditional formatting. You can use data validation to restrict what users can enter, but it won't prevent users clearing a cell.

[rikkdh]

Rudi,

Thanks, That solution would work, the dates are used and it would be a work around for stopping the code bugging out so I might explore it from a user friendliness point of view, its a nifty idea.. Would like to get it working more smartly without "working round" things tho!

And yes... i have spotted the error of my ways... data validation, not conditional formatting, its because i'm using conditional formatting elsewhere in the sheet, and din;t bother looking at the buttons! DOH :(

Might consider this as an alternative, thanks :)

[rikkdh]

Hi Hans,

Thanks for this... I think i explored this at some point in the past as well, but it didn't fare too well as a solution either. From memory it just didn't work, no errors or anything, just never did the undo part!!

I'm about to go and try the code you have given me, is there anywhere specific in the module i should be placing it (maybe thats where I had gone wrong before)..

And yup... Data Validation....although, it's not happy with the dates and formatting!... will maybe come back to that later :)

Thanks

[HansV]

You might place it near the beginning, it shouldn't bite other code.

[rikkdh]

Hans

So i have placed the code in the module after the auto filter code above and before all my conditional formatting code..

So if i delete one of the dates out of the worksheets I get runtime error 1004, Method of 'Undo' of object' _Application' failed

I placed the code just above the auto filter code...

also tried it just after (which is just before the formatting code) to see if that made a difference, it didn't.... this s what it was doing before when i tired, it so gave up on that!

Runs and cries.... :(

THanks

[HansV]

What if you use it like this?

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

    '  Switch Screen Off
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With

    If Target.Count = 1 And Not Intersect(Range("A:A"), Target) Is Nothing Then
        Application.EnableEvents = False
        If Target.Value = "" Then
            Application.Undo
        End If
        Application.EnableEvents = True
    End If

    Dim EmptyCount As Variant
    ...

[rikkdh]

Hans,

Genius, You have made me a very happy man, seems to work a treat....did a little happy dance :)

Now pushing the bounds a wee bit, If i wanted to do this for Columns A, B, C - would i repeat the code, or extend the range?

Coming back to the Data Validation too for the date if you don't mind:

Right now Ih ave a control sheet which has a names range on it called TodayDate populated with Today().... This populates the LastRow also a named range of the table with the date, to avoid the user having to do so... all good in the vast majority of cases!

On occasion, there is a need for users to change the date that has been pre-populated with Today Date / Today() , to Today+1, basically future dating entries.

I have created an additional Range TommorrowDate = Today+1 and then created the DataValidation from the list on the control sheet being TodayDate and TomorrowDate. This is giving the user the drop down of the two dates.... It is meaning though that any date in Column A that is prior to any of the 2 dates now has the wee green format error triangle in it...users might get confused and start looking at these and trying to correct them, which i don't want them to do as old entries should remain as were!

Hope i'm making sense It seems to be working in part, any thoughts? You have been a massive help so far.

Thanks again

[HansV]

If you want to prevent users from clearing cells in columns A, B and C, change the line

Code: Select all

    If Target.Count = 1 And Not Intersect(Range("A:A"), Target) Is Nothing Then

to

Code: Select all

    If Target.Count = 1 And Not Intersect(Range("A:C"), Target) Is Nothing Then

Error checking is a user-level setting, not a workbook- or worksheet-level setting. You'd have to instruct the users how to turn off error checking. In Excel 2010/2013:
- Select File | Options.
- Select Formulas.
- Clear the check box "Enable background error checking".
- Click OK.

S0452.png

[Jan Karel Pieterse]

Of course you could also turn off error checking using VBA:

Application.ErrorCheckingOptions.BackgroundChecking = False