Hi all,

This solution would probably work better with a user form but i'm
relatively new at VBA so am avoiding user forms right now....

I have a worksheet where users have to enter in produce delivered,
quantities etc. When items have been entered an "Archive" macro is run
to keep all the info.
What i need is something that when a user runs the macro, he/she is
promted to enter a password. This password is then copied to any cells
where say Range.(A) is a non null value.

E.g:

Date Product Signature

04/12/05 A

04/12/05 B

In this case when the macro is run, the user enters a password (from a
list sourced hidden elsewhere off the page), verifies the password is
valid and pastes either the password or username to the Signature
column. THen when everything is archived it can be determined who
checked those items.

Any help is much appreciated - as always and always!

Thanks in advance,

Chris


--
chris100
------------------------------------------------------------------------
chris100's Profile: http://www.excelforum.com/member.php...o&userid=25166
View this thread: http://www.excelforum.com/showthread...hreadid=490702

Dim ans as String, res as Variant

ans = InputBox("enter password")
res = Application.Match(ans,worksheets("PasswordList").R ange("A1:A50"),0)
if not iserror(res) then
with worksheets("Produce").Range("B1:B100")
if .Value = "A" then
.offset(0,1).Value = ans
end if
end with
else
msgbox "No valid"
Cancel = True
End if

This would be in the BeforeSave Events

http://www.cpearson.com/excel/events.htm

for information on events if you are not familiar.

--
Regards,
Tom Ogilvy


"chris100" wrote in
message ...

Hi all,

This solution would probably work better with a user form but i'm
relatively new at VBA so am avoiding user forms right now....

I have a worksheet where users have to enter in produce delivered,
quantities etc. When items have been entered an "Archive" macro is run
to keep all the info.
What i need is something that when a user runs the macro, he/she is
promted to enter a password. This password is then copied to any cells
where say Range.(A) is a non null value.

E.g:

Date Product Signature

04/12/05 A

04/12/05 B

In this case when the macro is run, the user enters a password (from a
list sourced hidden elsewhere off the page), verifies the password is
valid and pastes either the password or username to the Signature
column. THen when everything is archived it can be determined who
checked those items.

Any help is much appreciated - as always and always!

Thanks in advance,

Chris


--
chris100
------------------------------------------------------------------------
chris100's Profile:
http://www.excelforum.com/member.php...o&userid=25166
View this thread: http://www.excelforum.com/showthread...hreadid=490702

Thanks Tom,

I'll give it a go and post here.

Chri

--
chris10
-----------------------------------------------------------------------
chris100's Profile: http://www.excelforum.com/member.php...fo&userid=2516
View this thread: http://www.excelforum.com/showthread.php?threadid=49070

Hi Tom,

I set up the worksheets exactly as in the example using the correct
worksheet names. Unfortunately when i run the macro i get a "type
mismatch" error for this line:

If .Value = "A" Then


Any ideas?

Thanks,

Chris


--
chris100
------------------------------------------------------------------------
chris100's Profile: http://www.excelforum.com/member.php...o&userid=25166
View this thread: http://www.excelforum.com/showthread...hreadid=490702

Anyone??

I don't want to sound impatient but i'm on a roll with this program and
just want to keep going.

Thanks for all the help from people like Tom Ogilvy and many others who
have shown me what you can actually do with excel (ok, enough sucking up
now).

Chris


--
chris100
------------------------------------------------------------------------
chris100's Profile: http://www.excelforum.com/member.php...o&userid=25166
View this thread: http://www.excelforum.com/showthread...hreadid=490702