+ Reply to Thread
Results 1 to 13 of 13

Password Controlled Cell Entry and Subsequent Actions

  1. #1
    Registered User
    Join Date
    05-25-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2019
    Posts
    27

    Question Password Controlled Cell Entry and Subsequent Actions

    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
    Attached Files Attached Files
    Last edited by DrEcosse; 06-06-2012 at 05:39 PM. Reason: I've updated the example spreadsheet.

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Password Controlled Cell Entry and Subsequent Actions

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

    Please Login or Register  to view this content.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Registered User
    Join Date
    05-25-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2019
    Posts
    27

    Re: Password Controlled Cell Entry and Subsequent Actions

    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

  4. #4
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Password Controlled Cell Entry and Subsequent Actions

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

  5. #5
    Registered User
    Join Date
    05-25-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2019
    Posts
    27

    Thumbs up Re: Password Controlled Cell Entry and Subsequent Actions

    Quote Originally Posted by royUK View Post
    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! ;-)

  6. #6
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Password Controlled Cell Entry and Subsequent Actions

    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.

  7. #7
    Registered User
    Join Date
    05-25-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2019
    Posts
    27

    Re: Password Controlled Cell Entry and Subsequent Actions

    Quote Originally Posted by royUK View Post
    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

  8. #8
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Password Controlled Cell Entry and Subsequent Actions

    can you upload what you have so far

  9. #9
    Registered User
    Join Date
    05-25-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2019
    Posts
    27

    Re: Password Controlled Cell Entry and Subsequent Actions

    Quote Originally Posted by royUK View Post
    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
    Last edited by DrEcosse; 06-06-2012 at 05:40 PM.

  10. #10
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Password Controlled Cell Entry and Subsequent Actions

    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.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    05-25-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2019
    Posts
    27

    Re: Password Controlled Cell Entry and Subsequent Actions

    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
    Attached Files Attached Files

  12. #12
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Password Controlled Cell Entry and Subsequent Actions

    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
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    05-25-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2019
    Posts
    27

    Thumbs up Re: Password Controlled Cell Entry and Subsequent Actions

    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
    Attached Files Attached Files
    Last edited by DrEcosse; 06-11-2012 at 05:48 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1