Getting date picker to run on a protected sheet

[dpaton05]

I have this date picker from:

Home | Ron de Bruin Excel Automation

Welcome to https://www.rondebruin.nl

www.rondebruin.nl

It appears to work well, but the only problem is that you need to allow format cells and format columns if the sheet is protected but I do not want to allow users to format cells and columns. How can I get around this?

 

[dpaton05]

I have some further ideas. I could protect the sheet and do not allow formatting cells or columns. The date is stored in column A and you need to right click on the cell to access the date picker, but the only problem is that when you select it, the small calendar appears but it won't let you select a date due to the permissions to format cells or columns.

My solution is that I could add code that when you right click on a cell in column A, code is run that will allow formatting cells and columns so the date picker will work, but after the date has been entered, more code will run that removes that permission to format cells and columns.

Can anyone help me with the vba code to get this to run please?

 

[dpaton05]

I have got this code but I tried to change the target to be column A of the table CSS_quote instead of the total column A and it didn't like it. What have I done wrong?

Original code that worked but would do this for all cells in column A

If Not Intersect(Target, Range("A:A")) Is Nothing Then

Code that I tried to change it too so that the code only ran for cells in column A, within the table CSS_quote

    If Selection.Count = 1 Then
        If Not Intersect(Target, Range(ListObjects("CSS_Quote").ListColumns(1))) Is Nothing Then
            MsgBox "hello"
        End If
    End If

Can someone show me what is wrong with my syntax please?

 

[dpaton05]

Can someone help me with this please?

 

[eirikdaude]

I have got this code but I tried to change the target to be column A of the table CSS_quote instead of the total column A and it didn't like it.

What didn't it like? Did you get an error message of some kind, and if so what line did you get it on?

Intuitively, the syntax here seems odd:

Range(ListObjects("CSS_Quote").ListColumns(1))

I would expect it to be

ListObjects("CSS_Quote").ListColumns(1).Range

But maybe the first version works too. I don't have excel on this computer, so I can't test it right now...

 

[dpaton05]

At the moment, the sheet is protected and whenever I right click on any unprotected cells, I get the option of Date Picker at the top of the menu. I click on that and a small calendar appears. When I pick a date to insert I get the error message, telling me I need to enable format cells and format columns for it to work and the date to be inserted. The only problem is that I do not want to allow format cells and format columns as that will mean the users can change them and I do not want that.