How to validate 'time' value from Excel spreadsheet

[essaytee]

Obviously, I do have control in creating the spreadsheets for users, but the spreadsheets themselves will not be directly data entered, one row by one row. The idea is that the spreadsheet is provided as a template for the required data. Users will be using other spreadsheets received from external agencies and all will be in various formats. The information provided by external agencies, usually incorporates the information that is required, and our users will manipulate that data creating additional columns in the process. Some columns require narratives to be written, incorporating the data, usually including dates, times, amounts, locations etc. (think mail merge). Not all users will have the skills to achieve this, but some are capable and would rather spend a day or two with Excel as opposed to weeks of data entry.

The general advice is that once they have compiled or constructed all the columns, line them up in the same order as the template, then copy and paste as values to the template spreadsheet.

For the import routine Access will scan the spreadsheet, if there are any problems with the data, that problem is written to a text file so at least afterwards the user can check the error text file and directly check the entries of concern. This will be very useful if there are 1000's of records.

Initially, the import is to a temporary table, then some further error checking, and eventually appended to the live table and the temporary table records are deleted.

So, while the users are butchering manipulating other spreadsheets, they do what they have to do. Come the final 'paste as values' to the templated spreadsheet, it's quite possible that column formats are also copied across and not quite as I had planned.

I am not an expert in all things Excel so if there are easy ways to prevent users from modifying column formats, without side effects, I'm all ears. In a previous comment above, I did mention that I trialed locking the row headings, and protecting with a password, but the side effect was that the users could not resize the rows or columns, and was a PIA.

 

[MarkK]

You can force data validation in cells. Check out ribbon path: Data->Data Tools->Data Validation. This opens a dialog that lets you pick various formats, including Time. A time range is required, so you can do Start=0, and End=23:59:59.

 

[essaytee]

I've now added data validation and below is a sample snippet in relation to dates, just to see if it worked, which it does. The data validation is applied to the entire column, which includes the first row, which is the header row. Is there a way to apply the validation but to ignore the first row? My initial thought is after applying the global validation to then go back and purposely delete the validation from the first row cells.

    ' Create Row headings in spreadsheet from field names of temp charges table
    For c = 0 To rst.Fields.Count - 1
        Sheet1.Cells(1, c + 1) = rst(c).Name
        Select Case rst(c).Type
        
            Case dbDate
                Select Case rst(c).Name
                    Case "Event_Date1", "Event_Date2", "Event_Report_Date"
                        Sheet1.Columns(c + 1).NumberFormat = "dd/mm/yyyy"
                        
                        With Sheet1.Columns(c + 1).Validation
                            .Add Type:=xlValidateDate, AlertStyle:=xlValidAlertStop, Operator:=xlGreater, Formula1:="01/01/2000"
                            .InputTitle = "Date required"
                            .ErrorTitle = "Date is required"
                            .InputMessage = "A date greater than 1/1/2000 is required"
                            .ErrorMessage = "Fix IT"
                        End With

                    Case Else       ' must be time
                        Sheet1.Columns(c + 1).NumberFormat = "hh:mm"
                
                End Select
                
            Case dbInteger, dbLong
                Sheet1.Columns(c + 1).NumberFormat = "0"
                
            Case dbCurrency
                Sheet1.Columns(c + 1).NumberFormat = "0.00"
        End Select
    Next

 

[Gasman]

Start the row at 2 and use a range? How many entries are likely to be made?
I do not think it matters much for the header rows.
I often format a column and it never affects the headers.

 

[MarkK]

Here's one way you can get your sub column...

Function GetSubColumn(iCol As Integer, Optional iRow As Integer = 2) As Excel.Range
    With Sheet1.Columns(iCol)
        Set GetSubColumn = Sheet1.Range(.Cells(iRow), .Cells(.Cells.Count - iRow))
    End With
End Function

In the With block we identify the column, and we use its Cells--starting at iRow--to return a range from the worksheet.

 

[essaytee]

Ok, I've coded in the formatting and validation components of the spreadsheet, but I fear that that was all in vain as regards my project, not so from a learning perspective.

The spreadsheet that Access provides is a template, as mentioned previously, users will butcher other spreadsheets to prepare the rows/columns of data, get them in the right order, then copy to the template spreadsheet. It is the copy/paste process that can bring things undone. The advice is that the copied data is to be 'pasted as values' and that is the second option in the popup, not the first. If 'paste by values' the validation remains, but if not pasted by values then validation is removed. If validation remains, then at least users could select the Excel option to circle non-validated data.

At the end of the day, the final error checking and data validation is done when the data has been imported into a temporary table.

I'm now moving on to other parts of the project, fine-tuning the actual append queries (data from spreadsheet to temp tables) to live tables on the network. I can always re-visit the spreadsheet routine later to enhance it.

EDIT: Couldn't help myself, did a last check of pasting via values and as formatted and this time around data validation remained on the spreadsheet. I am sure this was not the case yesterday when I was checking, but anyway, moving on.

EDIT 2: After posting the above, I then thought, why not re-apply the validation prior to import - that's something I'll look at later.

 

[MarkK]

ChatGPT says you can disable Excel paste options as follows...

Disable Paste Options Button:

• Go to File > Options > Advanced.
• Under "Cut, Copy, and Paste," uncheck "Show Paste Options button when content is pasted."
  

I have not tried it, but I definitely ask ChatGPT a lot of questions.