vba, excel Shapes.AddLabel, Shapes.AddTextbox - locked/uneditable

[shakethingsup]

Hello - I am frustrated. I spent a few hours today on this and thought I would find 1 resource to solve this but I found pieces of it. SO maybe I'm not searching the right term. I"m trying to understand the difference between a textbox vs a label, a 'userform'. I don't know what I actually need.

The easy part is creating the excel add-ins for the users. There will be 2 buttons installed for each user. A "prepare" and a "review"

The flow:
I have a staff who prepares an excel document.
Once complete, they will hit the prepare button.
They will send to me, I will review, I will hit the review button.


The output:

I want each of those outputs to be UNEDITABLE/LOCKED - this is the problem

The coding:

Set Prepare = ActiveSheet.Shapes.AddLabel(msoTextOrientationHorizontal, 180, 169, 131, 43)
Prepare.TextFrame.Characters.Text = "Prepared by:" & " " & Environ("Username") & " " & Format(Date, "dd-mm-yyyy")
Prepare.TextFrame.AutoSize = True

The problem:
I don't know how to lock this?

 

[sykes]

Hi.
For starters - the differences...

A UserForm (uf) is a great piece if kit; it's a completely separate "Window" (for want of a better phrase) - a platform - which the coder can use to build what's best described as a user interface. It resides in a workbook - in the VBA section - and isn't normally visible to the user, until the ".show" method is used. The uf can contain contain all kinds of controls (labels, textboxes, dropdown/edit boxes etc.) all of which can have their own code assigned to them. The uf gives the owner of the workbook more control over how the user interacts with the workbook.

A Label is a simple control (but a very useful one) which allows text to be displayed. It can reside on a uf, or on a worksheet.

A textbox not only displays text, but allows the user to enter text, too - which can then be programmatically used for other purposes.

Hopefully, that's explained a teeny bit about your first question.

WRT your storage of the "prepared by" & "reviewed by" data, do you really need to have this displayed all the time?
As you want to lock it, I'm guessing that you're concerned that someone might want to change it, and if this is the case, wouldn't it be better to just store it out of sight, and out of temptation's way?
My suggestion would be to just store these bits of data in cells on a separate worksheet, then completely hide the worksheet, by using the sheet's veryhidden property e.g.

Sheets("Sheet1").Visible = xlVeryHidden

Sheets hidden thus, can't even be re-displayed by use of the "Unhide" facility in the normal user interface, so to all users, bar those who know that VBA even exists, the sheet doesn't even exist; for those that do, you can lock the whole VBA project too - to add a further layer of protection to your workbook.

 

[shakethingsup]

Thank you for the explanation!!!

as for the suggestion, also thanks!

but the reason why I need it is because our auditors need to see a review of some sort. I need it visible and to limit alteration if possible.

does that make sense?

 

[sykes]

You could still have an "Auditors'" button - which can be easily coded to bring up an inputbox, requiring a password, and upon correct entry, the sheet is unhidden.
Otherwise, perhaps put your info into a visible cell/cells on a worksheet, then lock the cells; you'd also need to password-protect the sheet for the lock to have an effect, though.
Can't think of an easy way to "Lock" a control, though - at the end of the day, they can all be manipulated, if you know how...