×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

.CustomDocumentProperties Save Only question

.CustomDocumentProperties Save Only question

.CustomDocumentProperties Save Only question

(OP)
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:

CODE

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 

CODE

Sub add_cust_doc_prop()

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


End Sub 

CODE

Sub rename_cust_doc_prop()


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


End Sub 

Thanks,

Mike

RE: .CustomDocumentProperties Save Only question

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.

RE: .CustomDocumentProperties Save Only question

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

RE: .CustomDocumentProperties Save Only question

(OP)
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?

RE: .CustomDocumentProperties Save Only question

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

RE: .CustomDocumentProperties Save Only question

Quote (remeng)

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

RE: .CustomDocumentProperties Save Only question

(OP)
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

RE: .CustomDocumentProperties Save Only question

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close