I wouldn't expect Select method to work anyhow as it's a method geared
towards the interaction with the interface. If the interface that it's
trying to interact with is hidden, then it wouldn't work. It may work for
when only a row or column is hidden, but not so much on a hidden worksheet.
Of course, that is dependent on how the security of the worksheets is setup,
if the worksheet is password protected.

Me personally, I don't like having passwords in files like these, but I can
certainly see Danny's viewpoint. In my production reporting system on the
client side, I also have the worksheets, workbook, and VBA code password
protected on the client side with the code unprotecting then protecting on
an as needed basis, which also introduces potential issues. However, those
passwords are put in more so to keep the users from trying to fudge the
numbers than it is to keep them from doing malicious activities. If the
latter was the case, they would be escorted out and lose their employment.

If anything, this production reporting system that I created for them has in
many respects helped everyone out that's impacted by this program. Numbers
are no longer recorded by hand, thus with the data checks and calculations
automated as opposed done by hand prior, it not only knocked out a lot of
the potential for human errors, but also allowed for the data to be more
real time and much more accurate. Believe it or not, this program was built
and debugged over a 3 week period along with the operators and assistants
trained over the last 3 days of that same 3 week time period. It's been in
full use since the start of July 2001, which was only meant as an
intermediate program, and still is being used today. Operators/Assistants
use the client side, and the server side updates our main DB system, which I
got around a lot of the issues in Excel that you would expect to face. That
was using XL97, SR2, which I hated that version cause of all the technical
issues that I faced with that version. XL2K though fixed a pretty majority
of those issues that I faced, which MS did send to me as a fix to the bugs
that I faced in XL97 and they confirmed as bugs, but wasn't going to fix in
97 as they were already fixed in XL2K. With XL2K, I did face some new
issues, but nothing too major that would have prevented me from using it for
my own purpose. However, due to the compilation differences between XL2K
and XL97, if anything involved VBA and others were going to use it, I had to
use XL97 for that purpose. However, for the last 4 years after we got
upgraded to W2K environment, we been using XLXP. The 2 biggest reasons why
we

Couldn't print in landscape mode over the network without having to put a
spool driver on the individual computer for the printer the user was
printing to (what a royal pain that was)

If the XL97 file contained VBA codes, no such file had been opened on the
user's system since the W2K Pro installation, and the user was marked as a
"Standard User" in the W2K environment, when XL97 would attempt to not only
access the registry file, but also write to it, it would come back as a VBA
permission denial error message. A "Power User" or higher would have to
open a such file one time on the system, then close it out before a
"Standard User" would be allowed to open a such file, as this was a one time
write to the registry file.
--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

"JMB" wrote in message
...
I know what you mean, but Find works okay for me on XL2000 (dunno about
previous versions). I know Find( ).Select won't work on a hidden sheet.



"Ronald Dodge" wrote:

The only reason why I included the 2 lines of code as JMB refered,
certain
things doesn't work on hidden objects, and if I recalled correctly, Find
is
one of those methods that doesn't work unless that been fixed in a later
version. I ran into that issue head on when our scheduler couldn't do
certain things cause the code wouldn't do the specific action on the
hidden
object as it was setup to.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

"JMB" wrote in message
...
I would probably set the veryhidden property of a worksheet and use code
similar to what I posted (using vlookup) or Find (as Ronald suggests).
Although, I would remove the following two lines from Ron's code as it
is
unnecessary to unhide/rehide the worksheet (and if the code crashes or
gets
interrupted - you don't want the macro to end before your password
worksheet
is unhidden).

wshDatePW.Visible = xlSheetVisible
wshDatePW.Visible = xlSheetVeryHidden


If you want the passwords entirely in VBA, you could build an array
w/dates
and passwords. For example:

Sub test()
Dim arrPwords(1 To 3, 1 To 2) As Variant
Dim strPWord As String

arrPwords(1, 1) = #8/20/2007#
arrPwords(2, 1) = #8/21/2007#
arrPwords(3, 1) = #8/22/2007#
arrPwords(1, 2) = 1234
arrPwords(2, 2) = 2345
arrPwords(3, 2) = 3456

strPWord = CStr(Application.VLookup(Date, arrPwords, 2, 0))

MsgBox strPWord

End Sub

Personally, I don't go through much trouble to try to password anything
in
XL as it's security (for lack of a better word) is weak. VeryHidden
worksheets can be unhidden w/a simple macro, worksheet and workbook
protection can be broken easily w/a macro, VBA project passwords can be
broken w/a hex editor or software available online, password to open
can
also
be cracked w/software available online. I only use passwords to keep
out
casual lookers or to prevent people from accidentally overwriting
things.
If
someone wants to break your protection, you should assume they will.


"Danny" wrote:

Hi JMB,

Say, I'd like to set up the passwords for the following dates:

8/20/07 = 1234
8/21/07 = 2345
8/22/07 = 3456
etc.

Is there a way to change the macro below If PassWord = "1234" Then
to "3456" automatically because today is 8/22/07?

I think the series of dates and corresponding passwords above should
be
in a
module
instead of a worksheet in the workbook.

Is there a way to do it?

Your macro below works perfectly. As usual, I keep all these macros
for
future references.

Thanks again and have a nice day!










"JMB" wrote:

You could use vlookup to identify the password for the current date:

Sub test()
Dim rngData As Range
Dim strPWord As String

Set rngData = Worksheets("Sheet1").Range("A:B")
strPWord = CStr(Application.VLookup(CLng(Date), rngData, 2, 0))
MsgBox strPWord

End Sub

you may want to pay attention to data type coercion. I made sure
vlookup
returns text with CStr (although it should not be necessary since
strPWord is
dimmed as string) in my example. So however you get the info from
the
user
and perform your comparison - I would ensure it is also text before
comparing
it to whatever vlookup returns (or ensure both are numeric - but
keep
in mind
the Inputbox function returns text).




"Danny" wrote:

Hi JMB,

I'm sorry I did not make my self very clear. The passwords are
random
numbers.
In a separate worksheet on Col. A are dates in order starting witn
8/20/07.
On Col. B are corresponding random numbers.

I was wondering how I can one could write a macro for these
information Col
A (date) = Cob B (password).

I will keep the macro you sent for future reference.

Thank you



"JMB" wrote:

One suggestion:


Const BaseDate As Date = #8/20/2007#
Const BasePword As Long = 1234

Sub test()
Dim pword As Long

pword = Date - BaseDate + BasePword
MsgBox pword

End Sub


The date literal is mm/dd/yyyy (U.S. short date format).


"Danny" wrote:

Hi,

I came up with this macro (from this NG) in my workbook.
How can I change the password based on a date?
Example:

8/20/07 = 1234
8/21/07 = 1235
8/22/07 = 1236
Etc.

Thank you.


Private Sub Workbook_BeforePrint(CANCEL As Boolean)

If Date = Now() Then
CANCEL = True

Exit Sub
End If
PassWord = InputBox("Enter Password")
If PassWord = "" Then
CANCEL = True
Exit Sub
End If
If PassWord = "1234" Then

CANCEL = False

'CountPrinting 'Limit printing even with correct date & PW,
still
on the works
Range("$A1").Select

Else
MsgBox " Try again ?", vb, "Wrong password !"
CANCEL = True
Exit Sub
End If
End Sub