Password protection for locking ActiveX Controls???

[MatthewBell]

Hi there

Not sure if this is possible but it's gotta be worth asking.

I'd like to put a command button on a sheet which, when clicked, locks a load of option buttons, text boxes and some cells. Protecting the sheet so that the next user cannot alter it (it's a questionnaire and the customers answers need to be protected). Following the command button click it needs to prompt for a password which is then required to unlock the various ActiveX controls.

Also to further complicate things the cells containing my companies data are already locked and the sheet protected when the questionnaire is sent out to the customer. This can be altered, perhaps the code unprotecting the sheet at the start and protecting it again at the end if necessary.

Any help would be much appreciated, especially if its the code required to protect an ActiveX controls setting.

Many thanks

Matt

 

[Zathras]

What do you want to protect from? Viewing? Alteration? Erasure?

How are you going to access the data if the user sets a password?

You can have the user click a button that executes a macro to hide the rows and then protect the sheet with a pre-defined password. You can make the password a function of the customer number in a non-obvious way. That way each customer would have his own password and couldn't alter the results after clicking the button.

Or if you wanted to, you could show the user the password used in a message box. In that case, you would need a fairly complex algorithm to calculate the password so that one customer couldn't guess another customer's password just by knowing his own. (I.e. don't just use "P" plus 2 times the customer number.)

Hope this helps.

 

[combo]

Hi Matt,
This piece of code can be useful to disable Activex objects on the sheet (here excluding commandbutton):

[tt]Dim olo As OLEObject
For Each olo In Me.OLEObjects
If olo.progID <> &quot;Forms.CommandButton.1&quot; Then
olo.Enabled = False
End If
Next[/tt]

assuming that the code is in the sheet with objects module, there is a commandbutton to lock objects and you add some code to manage password.

combo

 

[MatthewBell]

Hi Again guys.

Thanks for the code combo, it does what its mean't to do like a dream, however I still need a little more help.

In answer to the questions from Zathras.

I don't want to hide the data, just protect it from alteration.
I don't know how i'm going to access the data after the customer protects it, is there a way of finding an ActiveX controls setting when its protected (or set with enabled = False as in combos code)?
The customers password should not be predefined. It is to be chosen by the customer after completion of the form when they click a button to protect their answers. Only they should know it, entering it in an inputbox I guess.

The extra help i'm after is the bit combo breezed past probably assuming i'd know. How would I go about applying a Password to the conrols and cells in question. Say I prompt for a password in an inputbox and store as a string what do I do to make it a password for the controls to stop alteration?

Also with regards to finding a 'protected' ActiveX controls setting the most important example of this is the text boxes used for customer comments. When I use combo's code I can't scroll the boxes and view the text. Is it possible to extract the text?

Many thanks for your time guys.

Matt

 

[combo]

Hi Matt,
As for Acitvex controls - even when they are disabled, they keep values, you can get them using:
[tt]Something = Olbject.Value[/tt]
with Olo from my previous post. You can refer to OLEObjects collection by name (Oleobjects(&quot;Texbox1&quot.Object.Value) to get the user setting.

Password: I imagine that you create a simple UserForm with a textbox and set * to its PasswordChar property. You need some place to keep password and information concerning protection. It is not best solution, but you can have veryhidden worksheet with named cells to keep them. You can hide names (via code, Name has Visible property, when set to False, user can't see it in the Names dialog).
The general structure can be something like that (for click protect button):
If (protected) Then
If (VerifyPassword=True) Then
(Unprotect, i.e. enable controls)
(change protected state info)
(change button's caption to &quot;Protect&quot
Else
(info for user - wrong password)
Else
If (no protection) Then
If (password=&quot;&quot Then
(set and store password)
End If
(protect controls)
(change button's caption to &quot;Unprotect&quot
End If
End If

combo