+ Reply to Thread
Results 1 to 6 of 6

code is not locking workbooks..

Hybrid View

  1. 01-29-2015, 03:10 PM #1
    Jenkins27
    Jenkins27 is offline
    Forum Contributor
    Join Date
    01-27-2015
    Location
    New hampshire
    MS-Off Ver
    excel 2013
    Posts
    143

    code is not locking workbooks..

    what im trying to get happen in this code is after it copys the value into cell H13 in each workbook I want it to lock the sheet "measurements" in each workbook..
    but then it must unlock then the code is run again
       MyNum = ThisWorkbook.Sheets("Measurements").Range("H13").value
On Error Resume Next
For Each MyWb In Array("eqdcs 1.xlsm", "eqdcs 2.xlsm", "eqdcs 3.xlsm", "eqdcs 4.xlsm", "eqdcs 5.xlsm", "eqdcs 6.xlsm")
    Application.ScreenUpdating = False
    Set wbOPEN = Workbooks(MyWb)
    
    If Not wbOPEN Is Nothing Then
        WasOpen = True
    Else
        Set wbOPEN = Workbooks.Open(ThisWorkbook.Path & Application.PathSeparator & MyWb)
    End If
    ThisWorkbook.Sheets("Measurements").Unprotect Password:="password"
    wbOPEN.Sheets("measurements").Range("H13").value = MyNum
 
  
    If WasOpen Then
    
    wbOPEN.Protect Password:="password"
    wbOPEN.Save
    Else: wbOPEN.Close True
     Set wbOPEN = Nothing
    WasOpen = False
 
     End If
    
        Next MyWb
        ThisWorkbook.Save
    Reply With QuoteRegister To Reply
  2. 01-29-2015, 03:26 PM #2
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,774

    Re: code is not locking workbooks..

    ThisWorkbook relates to the workbook with the code in it, not the Active Worrkbook, which is the workbook you have just opened.

    Use wbOPEN or ActiveWorkbook to refer to the workbook with the Measurements sheet in it. Preferably, wbOPEN to be consistent.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 01-29-2015, 03:48 PM #3
    Jenkins27
    Jenkins27 is offline
    Forum Contributor
    Join Date
    01-27-2015
    Location
    New hampshire
    MS-Off Ver
    excel 2013
    Posts
    143

    Re: code is not locking workbooks..

    I tried this and is still did not work

       MyNum = ActiveWorkbook.Sheets("Measurements").Range("H13").value
On Error Resume Next
For Each MyWb In Array("eqdcs 1.xlsm", "eqdcs 2.xlsm", "eqdcs 3.xlsm", "eqdcs 4.xlsm", "eqdcs 5.xlsm", "eqdcs 6.xlsm")
    Application.ScreenUpdating = False
    Set wbOPEN = Workbooks(MyWb)
    
    If Not wbOPEN Is Nothing Then
        WasOpen = True
    Else
        Set wbOPEN = Workbooks.Open(ThisWorkbook.Path & Application.PathSeparator & MyWb)
    End If
    wbOPEN.Sheets("Measurements").Unprotect Password:="password"
    wbOPEN.Sheets("measurements").Range("H13").value = MyNum
   
    If WasOpen Then wbOPEN.Save Else wbOPEN.Close True
    Set wbOPEN = Nothing
    WasOpen = False

Next MyWb
    Reply With QuoteRegister To Reply
  4. 01-29-2015, 04:16 PM #4
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,774

    Re: code is not locking workbooks..

    Please don't bump your thread after 20 minutes. The people providing answers on this forum do so on a free and voluntary basis. Please be patient if the answer takes a little while. If you have not had an answer or an update after 24 hours, or so, THEN bump the thread.
    Reply With QuoteRegister To Reply
  5. 01-29-2015, 04:10 PM #5
    Jenkins27
    Jenkins27 is offline
    Forum Contributor
    Join Date
    01-27-2015
    Location
    New hampshire
    MS-Off Ver
    excel 2013
    Posts
    143

    Re: code is not locking workbooks..

    any other ideas anybody?
    Reply With QuoteRegister To Reply
  6. 01-29-2015, 04:14 PM #6
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,774

    Re: code is not locking workbooks..

    The .Protect in the original code is against the workbook, not the sheet. In the updated code, you don't have .Protect at all.

    Untested, but try:

    'Option Explicit

Sub Test()
   
Const csPassword As String = "password"
'define variables for compilation
'Dim MyNum
'Dim MyWb
'Dim wbOPEN As Workbook
'Dim wasOpen As Boolean

MyNum = ThisWorkbook.Sheets("Measurements").Range("H13").Value

On Error Resume Next
Application.ScreenUpdating = False

For Each MyWb In Array("eqdcs 1.xlsm", "eqdcs 2.xlsm", "eqdcs 3.xlsm", "eqdcs 4.xlsm", "eqdcs 5.xlsm", "eqdcs 6.xlsm")
    Set wbOPEN = Workbooks(MyWb)
    If Not wbOPEN Is Nothing Then
        wasOpen = True
    Else
        Set wbOPEN = Workbooks.Open(ThisWorkbook.Path & Application.PathSeparator & MyWb)
    End If
    
    With wbOPEN
        With .Sheets("Measurements")
            .Unprotect Password:=csPassword
            .Range("H13").Value = MyNum
            .Protect Password:=csPassword
        End With
        .Save
        ' if it wasn't open originally, close it
        If Not wasOpen Then
            .Close True
        End If
    End With
    
    Set wbOPEN = Nothing
    wasOpen = False
    
Next MyWb
' done
ThisWorkbook.Save

End Sub

    Regards, TMS
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Locking Formula links from Other Workbooks
    By dkomin in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 08-08-2012, 04:24 PM
  2. [SOLVED] Locking the use of Macros from other Workbooks
    By Maudise in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-17-2012, 05:53 AM
  3. Locking and Unlocking Excel workbooks
    By KarmaPolice in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-18-2007, 07:05 PM
  4. Shared Workbooks Locking Up
    By rastus in forum Excel General
    Replies: 0
    Last Post: 07-19-2005, 05:05 PM
  5. locking workbooks
    By Nick in forum Excel General
    Replies: 0
    Last Post: 07-06-2005, 11:05 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1