Input box for signing off work

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

Dim ans as String, res as Variant

ans = InputBox("enter password")
res = Application.Match(ans,worksheets("PasswordList").Range("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" <[email protected]> wrote in
message news:[email protected]...
>
> 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.

Chris

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

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