unprotect > run macro > protect - problem

[ivmoz]

Hi, this is my first post & I'm a novice when it comes to vba. I have a spreadsheet that has rows for each working day of the year. each row has columns for data corresponding to that date. Some cells are locked, others that need input are unlocked and there is a need for protection. In cell a3 i have (=today) so that it's always today's date. in column/range a6:a266 i have working days of the year - 1/4/13, 2/4/13 .....etc. I have a macro that when run takes the user to the row that corresponds to "today's" (a3) date:

Sub today()
'will go to the row for today's date'
Dim sh As Worksheet
Dim r As Range
Dim x As Variant
Set sh = Sheet1
x = Range("a3").Value
With sh.Range("a6:a266")
Set r = .Cells.Find(What:=x, After:=Range("a6"), Lookat:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If Not r Is Nothing Then
r.Select
With ActiveCell
Range(Cells(.Row, "a"), Cells(.Row, "n")).Select
End With
Else
MsgBox "value not found: " & x
End If
End With
End Sub

This code works great and highlights columns a:n for the correct row i.e it goes to today's row whatever today is.

But i have a problem when i try to amend the code to run in a protected sheet i.e. unprotect > run > protect. I add the ActiveSheet.Unprotect Password:="****" at the start & the Activesheet.Protect Password="****" at the end of the code. The macro unprotects, runs, protects but then takes me to cell B3, not to the range a:n for the row required. The sheet is protected though.

I've tried putting the protect bit after the first "End With" and also before the "End Sub"

Taking out the protect/unprotect bit, and the macro works again albeit I'm left with a unprotected sheet.

Any ideas on getting the code to work with unprotect/protect and be at the right row, not cell b3?

Tia,

 

[Snakehips]

Tia,

Welcome to Mr Excel.

I don't see anything wrong with the code. If you are not changing any values in locked cells then there should be no need to Unprotect and Protect.

I wonder if it is the protection settings? Try manually protecting the sheet and make sure that that the Allow Users of this Workbook To..... Select UnLocked Cells is ticked.

You can also have Select Locked Cells ticked if you wish. If Neither are ticked then you will have an issue when running the code and locking the sheet.

Hope that helps.