Password Controlled Cell Entry and Subsequent Actions

[DrEcosse]

I've now managed to get the spreadsheet working ALMOST how I want it. The only outstanding problem, is getting the signatures onto the spreadsheet WITHOUT user intervention. RoyUK's subroutine below is great if you want to load any picture manually. However, as this is a password-controlled entry, I need to get them into the cells automatically. I thought that using Private or Global variables would do the trick, or moving code into a standard module instead of worksheet macros would work. Not so! The two variables are: BtnLoc for the button location where the signature should be (coincidentally the location of the topleft of the action button) and SigFile for the signature file (currently pointing to the address where I've stored samples). I could easily have stored the images in those cells!

Any suggestions? BTW: Type in "Manager" for User name and "mgr" for password, without the quotes of course. This gives full access to the spreadsheet.

Many thanks in advance.

DrEcosse

[royUK]

This code will insert the selected picture to a specific range & size it to fit

Please Login or Register  to view this content.

[DrEcosse]

Thanks Roy.

That should do the trick for inserting the pictures. How do I get the password VBA to load from a Macro Button on the spreadsheet, rather when I open the workbook? I notice that it is called from Private Sub Workbook_Open().

I have to find out who is logging in (done via existing framework, using your VBA) and for what purpose. It will either be two retail staff, to check the till seal number and then sign/countersign the float in (am) and the cash out (pm). Or it will be two office staff checking the cash the following day. I need to give them all access individually to the spreadsheet to sign (retail) or count/enter/confirm (office) data.

Cheers, DrEcosse

[royUK]

Just remove the code from the open event & edit it to work from a button

[DrEcosse]

Just remove the code from the open event & edit it to work from a button

Done Roy. However, I've got a problems with getting the signatures to load automatically. See my update above.
You'll note I'm not a programmer but am willing and keen to learn! ;-)

[royUK]

If you only have 4 signatures then you can just add the signature using the user name. I don't know how your signatures are stored.

[DrEcosse]

If you only have 4 signatures then you can just add the signature using the user name. I don't know how your signatures are stored.

The picture files are jpegs in my picture directory. However, the usernames are used in the frmPW form, so like the other variables I tried to treat unsuccessfully as Public/Global, how do I pass them to the main module/worksheet macro(s)? There are over 20 different people who could sign the form, mainly 4 but up to 6 office staff and the balance are retail.
BTW: This is not the actual form, only a sample. Once I get it working fully, I'll port it over to my work system and integrate into the real form.

Thanks again.

DrEcosse

[royUK]

can you upload what you have so far

[DrEcosse]

can you upload what you have so far

Hi Roy

I did that yesterday evening. I modified my original post, with an updated spreadsheet and all macros.

I've since moved the Public variables to the top of the standard module and everything works OK, except BtnLoc passing its value to the 'InsertLogoInRange' subroutine. SigFile passes its value OK. Is it because BtnLoc is declared as a String but then is used in Range(BtnLoc) to create the range the picture is inserted? If so, how do I solve this anomaly?

I get the following error:

Run-time error '1004':
Method 'Range' of object '_Global' failed

Cheers, DrEcosse

[royUK]

You shouldn't amend earlier posts with attachments, it makes a nonsense of subsequent posts.

I can't test this ithout the signatures but I;ve used the form to select the correct path to the image based on the name selected. Then used that in the inset picture code.

[DrEcosse]

Hi Roy

Thanks for the update. Sorry about the error in posting. I thought it would be better for overall simplicity to keep the most up-to-date working copy on your server. However, I totally agree that there's no real flow in the progress that way. So, lesson learned and applied!

As for your change, I get exactly the same error '1004' as mentioned in my earlier post. I've attached the sample picture files (zipped) for you to test with.

Many thanks for your input, which is much appreciated.

Cheers, DrEcosse

[royUK]

The problem is BtnLoc, it's declared twice - Public & with the Sign_Click procedure which cancels the previous value assigned.

Also it's a string, in the insert signature part you use it as if it's a Range, it needs to be Range(BtnLoc)

I've changed how the image is selected.

1. There's a sub folder with the images needed in the Folder that the main workbook is stored.
2. When the user is selected the associate signature is picked (SigFile)
3. The variables in Sign_Click weren't properly declared

This works now

[DrEcosse]

Hi Roy

Brilliant! It works a charm. Initially, I didn't get it to work because PicPath was concatenated to your .Path statement. However, I soon discovered the problem and also removed PicPath, since it was redundant. I've still got some work to do, to utilise the Float sheet first, which then becomes the Cash sheet after the floats are installed in (AM), then removed from (PM), the tills. I've attached the updated CashSheet workbook. You'll note I've added ButLoc2 so that Office staff only sign once (i.e. across two cells) not twice.

How do I protect the workbook but still run the macros, since when the macro is live, staff can manipulate the pictures and access the macro?

BTW: New password for Manager is "m4nag3r"

Cheers, DrEcosse