I have a worksheet with the Visible property set to xlSheetVeryHidden that
contains a Named Range, say "Password"

On an unprotected sheet is there any way to stop a user entering a formula
"=Password" and / or to stop "Password" appearing in the Named Range drop
down list?

Thanks

Matt

Hi Matt,

On an unprotected sheet is there any way to stop a user entering a formula
"=Password" and / or to stop "Password" appearing in the Named Range drop
down list?


Range("password").Name.Visible = False

will remove the name from the dropdown list.

I do not know how to avoid using "password" in a formula, except that you
use a name that cannot be guessed by the user. If the range is in a
hidden/protected sheet there's no way to find out what the name is. So make
it something like "myRngPWord!".

arno

I new it would be something simple!

Thanks very much

Matt

"arno" wrote in message ...

If the range is in a
hidden/protected sheet there's no way to find out what the name is.

Are you sure about that? Try this in the VBE Immediate Window:

For Each n in ThisWorkbook.Names : ? n.Name, N.RefersTo : Next

--

Are you sure about that?
yes.

Whatever you do in Office Applications can be hacked by some who knows how
to. We try to hide and protect anything from "normal" users as good as
possible knowing that there's always a way to crack the file, if you have
access to the folder, access to the PC, access to the server, access to the
building.

Office applications are not secure, so do not use them ;)

arno

"arno" wrote in message ...

Whatever you do in Office Applications can be hacked by some who knows how
to. We try to hide and protect anything from "normal" users as good as
possible knowing that there's always a way to crack the file, if you have
access to the folder, access to the PC, access to the server, access to the
building.

If the user can *legitimately* open a workbook then they automatically
have legitimate access to the Visual Basic Editor. Do you really think
enumerating a public collection is a hack/crack?

If you want to hide something from a 'normal' user, I'd consider a
hidden Name to be inadequate, for the reason I have given i.e. hidden
Names are still very visible IMO. Putting a password in VBA code and
giving the VBA project a password I *would* consider adequate (but not
ideal). A VBA developer could discover this password but surely no one
would argue that this would not be hacking/cracking.

--

Hi,

again, it is clear that someone who knows vba can do whatever he likes with
office files, however, the average user does not even know what a macro is.
We can assume that we use alle the possibilities office gives us to hide and
protect knowing that it is not absolutely secure. So, do not use office
files to store secret data like real important passwords.

Hiding/protecting names, sheets, vba-code, formulas is all that can be
done, and this will for sure lock out 99% of users because they are simply
not skilled enough. Another 0.9% are administrators who have access to
everything anyway.

A password on the VBA-Modules does not help at all, eg
Sub test()
ActiveWorkbook.Sheets(1).Visible = True
End Sub
shows a very hidden sheet from a "protected" file.

regards

arno

"arno" wrote in message ...

A password on the VBA-Modules does not help at all, eg
Sub test()
ActiveWorkbook.Sheets(1).Visible = True
End Sub
shows a very hidden sheet from a "protected" file.

Indeed, a very hidden sheet is inadequate. That's why I said "[put] a
password in VBA code and [give] the VBA project a password".

--

password in VBA code and [give] the VBA project a password".


the trick is, i can run lot's of code from workbook A accessing the
vba-protected workbook B directly from workbook __A__! I simply have to open
workbook B and the rest of the macro works with "activeworkbook.whatever".

I tried this approach but still couldn't get at password in the VBA
code e.g.

? ActiveWorkbook.VBProject.VBComponents(3).CodeModul e.Lines(1,5)

worked on an unprotected VBA project, but if I protected the VBA
project with a password I get a run-time error, 'Can't perform
operation since the project is protected.'

--

"arno" wrote in message ...
password in VBA code and [give] the VBA project a password".


the trick is, i can run lot's of code from workbook A accessing the
vba-protected workbook B directly from workbook __A__! I simply have to open
workbook B and the rest of the macro works with "activeworkbook.whatever".

I do not know how to crack the VBA password, which appears to be a "real"
password compared to the sheet-protection password etc.

If you have a hidden sheet1 in WorkbookB use this code in Workbook A. I am
not accessing the vba-module here but i can make sheet1 visible:

Sub test()
Workbooks.Open Filename:="c:\WorkbookB.xls"
ActiveWorkbook.Sheets(1).Visible = True
'''here you could also make the names visible etc.
End Sub

arno

(onedaywhen) wrote in message . com...
I tried this approach but still couldn't get at password in the VBA
code e.g.

? ActiveWorkbook.VBProject.VBComponents(3).CodeModul e.Lines(1,5)


It is not possible to propramatically manipulate a locked VBA project.

http://www.intertek.org.uk

(Alert) wrote in message . com...

It is not possible to propramatically manipulate a locked VBA project.

http://www.intertek.org.uk

Erm... wasn't that *my* point?

--