Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Help with Lock cell code

    Hi Loungers

    I'm after some assistance with some code.

    In the attached spreadsheet you can see what I'm after, but essentially I want the cells in a row in the table to lock (unlock via password) progressively on tabbing to next row or on save, also move the most recent date in date column to the cell D10 and over right the previous date in the cell and lock the cell.

    I hope that make sence.

    Thanks for your assistance once again.

    Regards
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Verada,

    Ok here's your file with code that does what I think you are asking for.

    Test File: Verada - Woody Register test.xlsm

    Please take note that there is code located in 3 places of the VBA Project.
    1. Sheet1(Cover Page) - Basic code to lock cells.
    2. Modules-Tools Code to support locking/unlocking the sheet w/password,
    3. ThisWorkbook Code to trap Save & Exit routines and make sure sheet is in a state to allow that.


    Hope this works for you if not post back and we'll try to adjust.

    The current Password on the Cover Sheet is TEST
    You'll need to replace this in the Tools Module if you change it.
    You may also want to Password Protect the VBA module and Hide it so sharp users can't discover the password by looking at the module code.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thanks RG

    I tied your test file but is returning a Warning Message when I tab out of the last row (rev 7). Please see attached.

    Any thoughts? or am I doing something wrong?

    Regards
    Attached Images Attached Images

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Verada,

    The problem here is that the cursor jumped to row 35 because the rows from 20 to 34 were protected {I didn't know this} and the sheet protection did not allow selection of protected cells! I had been thinking about this problem because a person could have selected via mouse any row and not just the next row to be used. The code checks to see if the prior row has a complete entry {Cols C,D,E filled in} and won't continue until they are. Obviously, when the cursor jumped the previous (row above the current row) did not meet this condition thus the error message produced by my code.

    I have unprotected the rows down to 200 something and the problem has gone as far as the cursor jumping. However, we still have the problem of the user manually clicking in a row other than the next blank row. I'm working on this but don't have a solution yet. As long as the user uses the TAB key to move there shouldn't be a problem. They can always be instructed to do this or if they get the message and the row number displayed in the message is other than the next row just instruct them to click in Col C of the proper row after dismissing the message.

    I'll get back to you when I have a solution.

    Here's the file with the unused entry area unprotected: Verada - Woody Register test.xlsm
    Test it out.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thanks you very much RG

    I can instruct then to use the tab key and how to select the proper row.

    But I'd be very interested to see what solution you can up with.

    This is a great help

    Regards

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Verada,

    I found an error in the protection scheme you need to make the changes in BLUE in the code below located in the Tools module.

    Code:
    Sub UserProtect()
    
        ActiveWindow.WindowState = xlMaximized
        If Not ActiveSheet.ProtectContents Then _
          ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _
                              Scenarios:=True, userinterfaceonly:=True, _
                              Password:="TEST"
    BTW: I'm having quite a problem with the user clicking anywhere other than in Col C! I've tried a couple of things but wind up in endless loops and having to kill excel!. I'll keep working on it.

    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thanks once again

    I'll update the code in the module

    Don't bother too much about the Col C problem, I'll just instruct the user to click there.

    Your help is very much appreciated

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Verada,

    Ok, I've made some minor changes to the code and I think the Col C thing won't be a problem because:
    1. If the user clicks anywhere in C the code will automatically move the cursor to the first unused row in Col C
    2. If the user clicks anywhere else when they attempt to Save or Close the workbook they'll get a message telling they they have to click in Col C.


    FYI: If you want to work on the sheet it's a good idea to go to the VBE's Immediate Window {Toggles on/off with Ctrl+G} and enter this command.
    Application.EnableEvents=False
    This will turn off the checking code so it doesn't fire every time you click on a cell.
    When you're done with your changes just enter the command:
    Application.EnableEvents=True

    I've placed a visual indication on the worksheet so you know if they are on or off. Press F9 to check. If they are ON there will be nothing showing this is how you want it for users to use. If the events are OFF it will look like this.
    ApplicationEvents.JPG

    Final File: Verada - Woody Register test.xlsm
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. The Following User Says Thank You to RetiredGeek For This Useful Post:

    verada (2015-02-26)

  10. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Verada/RG

    Here's another way, just to show another method:
    I have set it up so that the User can only enter data in the 'next' available row.
    If they click on any cell, the cellpointer will move to the data entry row
    The Rev Number will be entered automatically when they have entered the required three data items.
    (This is to prevent User entry of an 'invalid' number or silly value)
    The Date column has data validation to prevent entry of future dates or dates earlier than the last value entered.(i.e. Revisions are assumed to be in date order)
    (they can still enter the same date as the last date though).
    The data validation uses the hidden cell in [D2] to validate date entered.
    The password is stored in 'hidden' cell [H2] rather than directly in the VBA code.

    I have added two padlock images with vba routines assigned, to simplify locking and unlocking the sheet.

    I haven't added a 'Before save' check.

    zeddy
    Attached Files Attached Files

  11. The Following User Says Thank You to zeddy For This Useful Post:

    RetiredGeek (2015-02-27)

  12. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Zeddy,

    Still learning after all these years. Thanks for the excellent code on the use of all the features of the Sheet Protect/UnProtect methods.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  13. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi RG

    Many thanks. Me, I'm learning too.
    When I was young I could remember everything, whether it happened or not.

    zeddy

  14. #12
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Verada,

    Here's another version just to show you another way to tackle this type of USER INPUT problem.

    The attached workbook uses a User Input Menu to gather the data vs having the user place the data directly in the cells.
    verada.JPG
    The menu event code initializes the menu to automatically contain the next Revision Number and the date of the last revision (in case there is more than one revision on a given date), of course the user can change the whole data or any part of it. Upon exiting the date field the date is checked for validity and that is equal to or greater than the last date and pops up an error message is it is in error. Likewise, if the User box is empty a message will be displayed and the user can not continue until some input is supplied.

    The Save button will not appear on the form until all required data has been entered and validated.

    When the save button is clicked the sheet will be unprotected the data entered in the next available row and then reprotected.

    I've also set header part of the form to remain on the screen while the user scrolls through the data in the table.

    HTH

    FILE: Verada - Woody Register Menu Version test.xlsm
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  15. The Following User Says Thank You to RetiredGeek For This Useful Post:

    verada (2015-03-23)

  16. #13
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Hi RG,

    Apologies for not replying and thanking you for your post. I must have missed it.

    That looks really good, just one request. Can the date format in the form match the date format in the cell, ie dd/mm/YYYY not mm/dd/yyyy.

    Regards

  17. #14
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Verada,

    Ok, here's the changed file: Verada - Woody Register Menu Version V2 test.xlsm

    What I did was to reformat the sheet with a custom date format (I'm in the US so this isn't the natural format for this location).
    Then I had to manipulate the form date to both display properly and then to read and save properly. If you windows is setup to use different location parameters this may or may not work! I'm not exactly sure how VBA/Forms/Excel interact with the windows location settings.

    VBA Changes:
    Code:
    '                         +---------------------+                  +----------+
    '-------------------------| UserForm_Initialize |------------------| 03/23/15 |
    '                         +---------------------+                  +----------+
    
    Private Sub UserForm_Initialize()
    
      Dim lRevNo  As Long
      
    
      iOrderCntr = iOrderCntr + 1
      Debug.Print "UF_Initialize: " & Format(iOrderCntr, "###")
      
      lLastRow = [c13].End(xlDown).Row
      dteLast = Cells(lLastRow, 4)
    
      With tbRevNo
          .Enabled = True
          .Value = Cells(lLastRow, 3) + 1
          .Enabled = False
      End With
      
      With tbRevDate
          .Value = Format(dteLast, "dd/mm/yyyy")
      End With
      
      cmdSave.Visible = False
      
    End Sub    'UserForm_Initialize
    Code:
    '                           +------------------+                  +----------+
    '---------------------------|   cmdSave_Click  |------------------| 02/28/15 |
    '                           +------------------+                  +----------+
    
    Private Sub cmdSave_Click()
    
       Dim lDataRow  As Long
       Dim vDateParts As Variant
    
       UserUnprotect
       vDateParts = Split(tbRevDate.Value, "/")
       lDataRow = lLastRow + 1
       Cells(lDataRow, 3).Value = tbRevNo.Value
       Cells(lDataRow, 4).Value = vDateParts(1) & "/" & _
                                  vDateParts(0) & "/" & _
                                  vDateParts(2)
       Cells(lDataRow, 5).Value = tbRevUser.Value
       Cells(lDataRow, 6).Value = tbComments.Value
       UserProtect
       
      Unload Me
       
    End Sub    'cmdSave
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  18. #15
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If you use CDate when reading from/writing to the cell, it will respect the user's regional settings, which is usually what you want.
    Regards,
    Rory

    Microsoft MVP - Excel

  19. The Following User Says Thank You to rory For This Useful Post:

    RetiredGeek (2015-03-23)

Page 1 of 2 12 LastLast

Posting Permissions

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