+ Reply to Thread
Results 1 to 3 of 3

VBA for Cell locked please help

  1. #1
    peddie
    Guest

    VBA for Cell locked please help

    Hi

    I don't seem to get any error when opening the workbook however it
    doesnt do the job. I want to allow certain users to be able to amend
    the content in the Worksheet but if they are not the selected users
    then they can only view the data. Could anyone please advice me what I
    have to do.

    Thank you very much in advance. below is my code

    Peddie


    Private Sub Workbook_Open()


    Dim username As String

    username = OSUserName()

    'If username = "John Smith" Then

    lock_cells

    'Else
    'Selection.Locked = True
    End Sub


    Sub lock_cells()
    Sheets("Apr 2006 - Mar 2007").Select
    Cells.Locked = True

    End Sub


  2. #2

    Re: VBA for Cell locked please help

    Sure, you're just missing one more step. In order to block the cells
    from being changed, you not only have to lock the cells, but you have
    to Protect the sheet. Try adding this in:

    ActiveSheet.Protect Password:="yourpassword"

    And to reverse it:

    ActiveSheet.Unprotect Password:="yourpassword"


    peddie wrote:
    > Hi
    >
    > I don't seem to get any error when opening the workbook however it
    > doesnt do the job. I want to allow certain users to be able to amend
    > the content in the Worksheet but if they are not the selected users
    > then they can only view the data. Could anyone please advice me what I
    > have to do.
    >
    > Thank you very much in advance. below is my code
    >
    > Peddie
    >
    >
    > Private Sub Workbook_Open()
    >
    >
    > Dim username As String
    >
    > username = OSUserName()
    >
    > 'If username = "John Smith" Then
    >
    > lock_cells
    >
    > 'Else
    > 'Selection.Locked = True
    > End Sub
    >
    >
    > Sub lock_cells()
    > Sheets("Apr 2006 - Mar 2007").Select
    > Cells.Locked = True
    >
    > End Sub



  3. #3
    peddie
    Guest

    Re: VBA for Cell locked please help

    Hi there
    Thanks for the prompt reply. What you suggested me works great but it
    gives me the same result as protect the sheet from Tools > Protection.

    When trying to edit something the generic error pops up. I aim to
    customise this generic error message to something like "Contact Joe
    Bloggs for the new content' something like that.

    is it possible to override the system error message?

    Many thanks

    Peddie
    [email protected] wrote:

    > Sure, you're just missing one more step. In order to block the cells
    > from being changed, you not only have to lock the cells, but you have
    > to Protect the sheet. Try adding this in:
    >
    > ActiveSheet.Protect Password:="yourpassword"
    >
    > And to reverse it:
    >
    > ActiveSheet.Unprotect Password:="yourpassword"
    >
    >
    > peddie wrote:
    > > Hi
    > >
    > > I don't seem to get any error when opening the workbook however it
    > > doesnt do the job. I want to allow certain users to be able to amend
    > > the content in the Worksheet but if they are not the selected users
    > > then they can only view the data. Could anyone please advice me what I
    > > have to do.
    > >
    > > Thank you very much in advance. below is my code
    > >
    > > Peddie
    > >
    > >
    > > Private Sub Workbook_Open()
    > >
    > >
    > > Dim username As String
    > >
    > > username = OSUserName()
    > >
    > > 'If username = "John Smith" Then
    > >
    > > lock_cells
    > >
    > > 'Else
    > > 'Selection.Locked = True
    > > End Sub
    > >
    > >
    > > Sub lock_cells()
    > > Sheets("Apr 2006 - Mar 2007").Select
    > > Cells.Locked = True
    > >
    > > End Sub



+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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