.CustomDocumentProperties Save Only question

[remeng]

Hi Folks,

I am trying to use the .CustomDocumentProperties method to carry over values from the current workbook thru to the next time the workbook is opened. I'd like to make the file READ ONLY so that no other changes can be made permanently.

The way I'd like to use .CustomDocumentProperties is by connecting it to a Control Panel sheet in the workbook. The user is going to be loading new data and exporting data into the file.

I have concerns that people will "break" the workbook by doing something that they shouldn't be.

Is there a way to allow the user to change the settings in the Control Panel and save only those changes and still leave the file as read only?

General Reference Code Lines:

Sub doc_props()


rw = 1
Worksheets("test").Activate
For Each p In ActiveWorkbook.CustomDocumentProperties
    Cells(rw, 1).Value = p.Name
    Cells(rw, 2).Value = p.Value
    rw = rw + 1
Next



End Sub

Sub add_cust_doc_prop()

ThisWorkbook.CustomDocumentProperties.Add Name:="Control Panel Test", LinkToContent:=False, Type:=msoPropertyTypeString, Value:="Test"


End Sub

Sub rename_cust_doc_prop()


ThisWorkbook.CustomDocumentProperties.Item("Control Panel Test").Value = "bob"


End Sub

Thanks,

Mike

 

[mintjulep]

You seem to be asking for "Read-only, except for". That's not a thing.

One way to allow VBA to change things while making it hard for normal people to change the same things is by using veryhidden worksheets.

 

[combo]

Wouldn't be easier to protect worksheets and structure, add very hidden sheet that stores settings, and finally work with workbook data only from code, repeating protection with UserInterfaceOnly:=True when opening the workbook?

combo

 

[remeng]

Hi,

Combo, that is something I've never come across. I'll definitely look more into UserInterfaceOnly .

What are .CustomDocumentProperties used for then? Is there any specific application that it is used for?

 

[strongm]

If feeling particulalrly adventurous, you could consider using an Alternate Data Stream (ADS) ...

 

[combo]

What are .CustomDocumentProperties used for then? Is there any specific application that it is used for?

For me, they are rather labels, in addition to built-in ones (as author and other), used to mark the workbook, as version number, project data, contact data, dates, specific features etc. Of course they can be used to transfer data too.

Data in excel can be stored in names too. A value can be assigned to name directly, without involving range. After setting Visible property to False it will not appear in name manager.


combo

 

[remeng]

Thanks Combo!

Here is another approach I wanted to pitch and see what everything thinks.

I have possibly 2 options to go forward with:

1 - I have a specific user that can only enter data into specified and unlocked cells. They are the only user that cannot edit the rest of the workbook.
2 - I can put the workbook on SharePoint.

Is there a way to limit that particular user to only be able to edit the unlocked cells and cannot change anything else?

Thanks,

Mike

 

[combo]

The last tab in cell format dialog refers to protection, you can set how selected cells will behave when worksheet is protected. A cell can be locked/unlocked and have contents visible/not visible in formula bar.
Next, the protection of worksheet can be set, either from the pop-up menu of the tab with worksheet name or, for active sheet, in File>Information page. In the protection dialog one can set password and scope of protection of the worksheet. The protection and behaviour of cells depend on both settings.
The workbook structure protection prevents from adding and deleting sheets. The access to it is also from information page.
All the above impact all users.

There is also "allow users edit ranges" option in excel, you define users for given ranges and let them work with the ranges in protected workbook without password. It is similar to the worksheet protection described above, but with ranges unlocked individually for users.

combo

 

[remeng]

Hi All;

I did find my answer here:

[URL unfurl="true"]https://www.officetooltips.com/excel_2016/tips/allow_specific_users_to_edit_ranges_in_a_protected_spreadsheet.html[/url]


This uses the "Allow Edit Ranges" protection property that is already built into Excel.