Macros and Locking

Question

Sorry all I feel like I'm up here every week with a new question, but you guys always come back with great answers!

I'm using Excel 2010 version for work. I created a worksheet with a couple macros that now is to be saved onto the work computer for several other people to use. I wanted to use 'protect worksheet' to be able to lock certain cells that, if messed with unintentionally, would stop working. I was able to lock the cells and protect the worksheet, but I found that using the protect worksheet made my macros stop working. my formulas etc work still, but nothing from the macros. when I return to 'unprotected' mode the macros go back to working no problem.  Am I doing something wrong or is this something that is not capable with my version or in general with excel? thanks

    -Tiffany

Answer 1

The worksheet code is to replace your original code.

The workbook code is to be copied to Thisworkbook module.

But. . . . . . .

How will you keep BD and BH locked but have users continually add more numbers in those columns?

From your explanation in second post I thought the users were entering the numbers but looks like I have misunderstood your code which does sorting on those columns as numbers are added.

Where and how are the numbers being entered?

Gord

Answer 2

Thank you just a couple questions, Do I add this code to the beginning of my current macro? also the table with the sorting numbers, which is being moved by the macros is what im keeping locked because if anyone inputs into this table directly it will mess with the formulas and macros. Does this make a difference?

Answer 3

Assuming the columns BD and BH  are currently unlocked,

add this event code to Thisworkbook module.

Change "yoursheet" to the correct sheetname. and edit the password.

Private Sub Workbook_Open()

    Worksheets("yoursheet").Protect Password:="gord", _

   UserInterFaceOnly:=True

End Sub

This sheet event code revision replaces the existing worksheet event code.

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error GoTo reset_events

    Application.EnableEvents = False

    With Columns("BD:BD")

        .Sort Key1:=Range("BD2"), _

                Order1:=xlAscending, Header:=xlYes, _

                OrderCustom:=1, MatchCase:=False, _

                Orientation:=xlTopToBottom

    End With

    With Columns("BH:BH")

        .Sort Key1:=Range("BH2"), _

                Order1:=xlAscending, Header:=xlYes, _

                OrderCustom:=1, MatchCase:=False, _

                Orientation:=xlTopToBottom

    End With

reset_events:

    Application.EnableEvents = True

End Sub

Gord

Answer 4

Ok thanks, the  following is my macro. I dont know how to code these are just a copy and paste of different codes provided and suggested that actually made my sheet work so forgive me if this seems like a strange one. the macro is intended to take a column of numbers that are continually changing by  employee overtime. the macro auto sorts the column (i have 2 colums for 2 types of employees which is why you see bd and bH) from smallest to largest number and continually does this as more numbers are added to the sheet. 

Sub JustInCase()

  Application.EnableEvents = True

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error Resume Next

    Range("bd1").Sort Key1:=Range("bd2"), _

      Order1:=xlAscending, Header:=xlYes, _

      OrderCustom:=1, MatchCase:=False, _

      Orientation:=xlTopToBottom

    On Error Resume Next

    Range("bh1").Sort Key1:=Range("bh2"), _

      Order1:=xlAscending, Header:=xlYes, _

      OrderCustom:=1, MatchCase:=False, _

      Orientation:=xlTopToBottom

End Sub

Answer 5

Usual method is add code in your macro to unprotect, do the work then re-protect.

Post your code.

Gord