Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Apr 2014
    Posts
    37
    Thanks
    2
    Thanked 1 Time in 1 Post

    How can I modify the "Normal" Cells Style using VBA?

    Hello,

    How can I modify the "Normal" Cells Style using VBA?

    The parameter that I need to modify is in:
    - Normal Style
    - Modify
    - Format button
    - Protection tab
    - Uncheck option Locked

    The ideia is to prevent unlocked cells becoming locked via Copy/Paste.

    Many thanks

  2. #2
    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
    It's just:
    Code:
    activeworkbook.Styles("Normal").Locked = false
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Lounger
    Join Date
    Apr 2014
    Posts
    37
    Thanks
    2
    Thanked 1 Time in 1 Post
    Hello Rory,

    It works just fine for a single file but I'm need to apply it for several files in different folders and I'm getting the error message "Unable to set the Locked property of the Style class"

    The code that I'm using is:
    ---------------------------------------------------------
    Option Explicit
    Sub PasteSolved()
    'Prevent Unlocked Cells Becoming Locked Via Copy/Paste

    ' Path - modify as needed but keep trailing backslash
    Const sPath = "D\Documents\"

    Dim sFile As String
    Dim wbkSource As Workbook
    Dim wSource As Worksheet
    Dim ws As Worksheet
    Dim oFSO As Object
    Dim oFolder As Object
    Dim oSubFolder As Object

    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = oFSO.GetFolder(sPath)
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    For Each oSubFolder In oFolder.SubFolders
    sFile = Dir(oSubFolder & "\*.xlsx") 'UPDATE File name or part of it

    Do While Not sFile = ""
    Set wbkSource = Workbooks.Open(Filename:=oSubFolder & "\" & sFile, AddToMRU:=False)

    For Each ws In Worksheets
    'Unprotect sheets
    ws.Unprotect Password:="1"

    'Change Normal cells style
    wbkSource.Styles("Normal").Locked = False
    ws.Range("A1") = "Hello"

    'Protect sheets
    ws.Protect Password:="1"

    Next

    sFile = Dir
    wbkSource.Close SaveChanges:=True

    Loop
    Next


    ExitHandler:
    Application.ScreenUpdating = True
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler

    End Sub

    --------------------------------------------------

    Where the instruction must be inserted?

    Many thanks for your help.


    LL

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    try:

    For Each ws In wbkSource.Worksheets

    Steve

  5. #5
    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
    You may also need to unprotect all the sheets first, then change the style, then reprotect all the sheets.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Lounger
    Join Date
    Apr 2014
    Posts
    37
    Thanks
    2
    Thanked 1 Time in 1 Post
    Hi Steve,

    Sorry... but I'm getting the same error "Unable to set the Locked property of the Style class"

    Any other ideas?

    Thanks

    LL

  7. #7
    Lounger
    Join Date
    Apr 2014
    Posts
    37
    Thanks
    2
    Thanked 1 Time in 1 Post
    It works!

    Many thanks Rory




    LL

Posting Permissions

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