In Excel 2010 I have a multi page spreadsheet.
There is a table page with each row holding a record that needs to
be issued a receipt on a portable printer. This page has a cell with a formula that keeps the coordinates of the current
active cell on that page which is accessed from another page to pull the data off the current row in order to print a receipt
formatted for 2 inch roll paper using the data from the referenced row.
This all works after much effort but when
I went to protect the cell with the coordinates, which is vital to the proper functioning of the application, I discovered
that protecting this cell locks the whole sheet.
It appears to me when this cell is protected whenever you try to
select any cell, protected or not, excel thinks you are trying to access this protected 'coordinates' cell and returns a 1004
error from this piece of code.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("AA1").Value = ActiveCell.Address(0, 0)
If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I can leave the cell unprotected but I am reluctant to do that because the end users are not computer literate.
I have checked the protection status of all cells and all the data entry cells are unprotected correctly. Once I
un-protect that one 'coordinates' cell I again have access to all the unprotected cells correctly.
I am guessing
that having the code above un-protect that cell 'AA1' before updating the value and re-protecting it afterwards would do the
trick. At the end of a long day and being excel illiterate I would appreciate the syntax to do this.