Consulting

Results 1 to 6 of 6

Thread: Same code - one set works but other does not...

  1. #1
    VBAX Contributor Glaswegian's Avatar
    Joined
    Sep 2004
    Location
    Glasgow, Scotland
    Posts
    196
    Location

    Same code - one set works but other does not...

    Hi

    I wonder if anyone can explain this for me.

    I've created a workbook with various functions etc etc and, to preserve a record of changes, I have a succession of different versions.

    In the most recent version, users can click a command button to view details on a hidden sheet. This sheet is locked down so that users cannot change any of the data. All controls (all controls are Toolbox controls) should also be locked, except for the 'View' button - they can click this for a second time and return to the start sheet. This is part of the code that now does not work:
    With Sheets(mySht)
        .Visible = True
        .Unprotect Password:="+RADIation4/3?"
            For Each ole In .OLEObjects 'ensure all OLE objects are locked
                ole.Object.Locked = True
            Next ole
            .Label5.Caption = "Click here to close this view"
            For Each c In myRng 'ensure all cells are locked
                c.Locked = True
            Next c
            .cmbView.Object.Locked = False 'unlock the button so that users can close this view
        .Protect Password:="+RADIation4/3?"
    End With
    mySht is a variable passed from a user form and myRng is set to a specific list of cells to which users may input data.

    I was a bit puzzled when the code stopped on the red line, so I opened a previous version and found this code:
    With Sheets(mySht)
        .Visible = True
        .Unprotect Password:="+RADIation4/3?"
            For Each ole In .OLEObjects
                ole.Object.Locked = True
            Next ole
            For Each c In myRng
                c.Locked = True
            Next c
            .cmbView.Object.Locked = False
        .Protect Password:="+RADIation4/3?"
    End With
    This second piece of code runs fine every time - no problems and no errors.

    If I remove 'object' from the line in red, the code runs fine but does not lock the controls.

    I'm stumped on this one - the only change has been the addition of the 'caption' line, so why does it not work?
    Iain - XL2010 on Windows 7


  2. #2
    VBAX Contributor Glaswegian's Avatar
    Joined
    Sep 2004
    Location
    Glasgow, Scotland
    Posts
    196
    Location
    Very strange...now the first piece of code is working correctly. I did close and re-open Excel - perhaps that has cleared memory? Actually both pieces of code are now working...
    Iain - XL2010 on Windows 7


  3. #3
    VBAX Contributor Glaswegian's Avatar
    Joined
    Sep 2004
    Location
    Glasgow, Scotland
    Posts
    196
    Location
    Spoke too soon - back to square one again....
    Iain - XL2010 on Windows 7


  4. #4
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Can you post a sample?
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  5. #5
    VBAX Contributor Glaswegian's Avatar
    Joined
    Sep 2004
    Location
    Glasgow, Scotland
    Posts
    196
    Location
    Hi Malcolm

    Looks like this will have to wait until next week - it's school mid term break and I'll be away for the rest of the week. I won't have time to create a sample today - my apologies.

    Further testing indicates it might be something to do with the hiding/unhiding of the data sheet - but that's just a guess at the moment.

    As some further background, users only see a sheet called 'Start'. They input data into specific cells (all others are locked and the sheet is protected) and then click a button to 'complete' the file. The Start sheet is then copied, renamed using 'Data' plus the date and time and then hidden. This ensures that users cannot make further changes. There are buttons for other departments to unlock specific textboxes on the hidden sheet so that they can add comments. Other departments have a password for access. Once they have added comments, they click the same button and the workbook is saved, with the Data sheet being hidden once again.

    I've created similar workbooks in the past with no issues, so this one is doubly annoying!
    Iain - XL2010 on Windows 7


  6. #6
    VBAX Contributor Glaswegian's Avatar
    Joined
    Sep 2004
    Location
    Glasgow, Scotland
    Posts
    196
    Location
    Hi again

    Managed to create a sanitised version - you'll likely receive plenty of errors as I've had to remove a sheet that contains the named ranges for the green cells - data validation. I've also changed all passwords to 'password' - all lower case.

    Thanks again for any help.
    Iain - XL2010 on Windows 7


Posting Permissions

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