I have a situation which occurred when a user downloaded a spreadsheet
I created for him. I sent the spreadsheet zipped in a winzip file, and
the user unzipped the spreadsheet and copied it to their desktop. One
of the formulas on one of the sheets in the spreadsheet changed, and I
can't figure out how it changed. The cell was protected, so the user
couldn't possibly have changed it. I also sent the spreadsheet to
other users in the same office and they downloaded the file to their
desktops as well. The formula was correct for the other users. I'm
trying to avoid this situation in the future, and I'm wondering if
anyone else has had a similar thing happen. Everyone is on the correct
version of Excel (2003), macros were enabled, so I can't figure out how
the formulas changed. Any suggestions would be greatly appreciated!

Connie

First, I don't have any real explanation.

But worksheet protection isn't that difficult to break.

And are you sure that all the users got the same version of the file?

Connie wrote:

I have a situation which occurred when a user downloaded a spreadsheet
I created for him. I sent the spreadsheet zipped in a winzip file, and
the user unzipped the spreadsheet and copied it to their desktop. One
of the formulas on one of the sheets in the spreadsheet changed, and I
can't figure out how it changed. The cell was protected, so the user
couldn't possibly have changed it. I also sent the spreadsheet to
other users in the same office and they downloaded the file to their
desktops as well. The formula was correct for the other users. I'm
trying to avoid this situation in the future, and I'm wondering if
anyone else has had a similar thing happen. Everyone is on the correct
version of Excel (2003), macros were enabled, so I can't figure out how
the formulas changed. Any suggestions would be greatly appreciated!

Connie

--

Dave Peterson

Connie,

It may be the type of link in the formula changing. You may have a network
path that included in the formula that has changed to UNC style. Please post
the formula.
--
http://HelpExcel.com

516-984-0252


"Connie" wrote:

I have a situation which occurred when a user downloaded a spreadsheet
I created for him. I sent the spreadsheet zipped in a winzip file, and
the user unzipped the spreadsheet and copied it to their desktop. One
of the formulas on one of the sheets in the spreadsheet changed, and I
can't figure out how it changed. The cell was protected, so the user
couldn't possibly have changed it. I also sent the spreadsheet to
other users in the same office and they downloaded the file to their
desktops as well. The formula was correct for the other users. I'm
trying to avoid this situation in the future, and I'm wondering if
anyone else has had a similar thing happen. Everyone is on the correct
version of Excel (2003), macros were enabled, so I can't figure out how
the formulas changed. Any suggestions would be greatly appreciated!

Connie

Here's the formula:

+IF('Field Rep Time Sheet'!G11=0,"",'Field Rep Time Sheet'!G11)

The users aren't very sophisticated with Excel, so if they broke the
protection it was by accident. However, the user sent me the
spreadsheet and it was the correct version and the protection was on.
So I'm stumped. I'm sure that the users all got the same version of
the file (and as I said I confirmed for myself that the version was
correct). Any help is appreciated!

galimi wrote:
Connie,

It may be the type of link in the formula changing. You may have a network
path that included in the formula that has changed to UNC style. Please post
the formula.
--
http://HelpExcel.com

516-984-0252


"Connie" wrote:

I have a situation which occurred when a user downloaded a spreadsheet
I created for him. I sent the spreadsheet zipped in a winzip file, and
the user unzipped the spreadsheet and copied it to their desktop. One
of the formulas on one of the sheets in the spreadsheet changed, and I
can't figure out how it changed. The cell was protected, so the user
couldn't possibly have changed it. I also sent the spreadsheet to
other users in the same office and they downloaded the file to their
desktops as well. The formula was correct for the other users. I'm
trying to avoid this situation in the future, and I'm wondering if
anyone else has had a similar thing happen. Everyone is on the correct
version of Excel (2003), macros were enabled, so I can't figure out how
the formulas changed. Any suggestions would be greatly appreciated!

Connie

The formula changed to the following:

+IF('Field Rep Time Sheet'!G14=0,"",'Field Rep Time Sheet'!G14)

Connie wrote:
Here's the formula:

+IF('Field Rep Time Sheet'!G11=0,"",'Field Rep Time Sheet'!G11)

The users aren't very sophisticated with Excel, so if they broke the
protection it was by accident. However, the user sent me the
spreadsheet and it was the correct version and the protection was on.
So I'm stumped. I'm sure that the users all got the same version of
the file (and as I said I confirmed for myself that the version was
correct). Any help is appreciated!

galimi wrote:
Connie,

It may be the type of link in the formula changing. You may have a network
path that included in the formula that has changed to UNC style. Please post
the formula.
--
http://HelpExcel.com

516-984-0252


"Connie" wrote:

I have a situation which occurred when a user downloaded a spreadsheet
I created for him. I sent the spreadsheet zipped in a winzip file, and
the user unzipped the spreadsheet and copied it to their desktop. One
of the formulas on one of the sheets in the spreadsheet changed, and I
can't figure out how it changed. The cell was protected, so the user
couldn't possibly have changed it. I also sent the spreadsheet to
other users in the same office and they downloaded the file to their
desktops as well. The formula was correct for the other users. I'm
trying to avoid this situation in the future, and I'm wondering if
anyone else has had a similar thing happen. Everyone is on the correct
version of Excel (2003), macros were enabled, so I can't figure out how
the formulas changed. Any suggestions would be greatly appreciated!

Connie

Any chance that the 'field rep time sheet' worksheet is not protected and the
user moved cells around on that sheet (or inserted some rows)?

I've never seen excel change formulas like this.

Connie wrote:

The formula changed to the following:

+IF('Field Rep Time Sheet'!G14=0,"",'Field Rep Time Sheet'!G14)

Connie wrote:
Here's the formula:

+IF('Field Rep Time Sheet'!G11=0,"",'Field Rep Time Sheet'!G11)

The users aren't very sophisticated with Excel, so if they broke the
protection it was by accident. However, the user sent me the
spreadsheet and it was the correct version and the protection was on.
So I'm stumped. I'm sure that the users all got the same version of
the file (and as I said I confirmed for myself that the version was
correct). Any help is appreciated!

galimi wrote:
Connie,

It may be the type of link in the formula changing. You may have a network
path that included in the formula that has changed to UNC style. Please post
the formula.
--
http://HelpExcel.com

516-984-0252


"Connie" wrote:

I have a situation which occurred when a user downloaded a spreadsheet
I created for him. I sent the spreadsheet zipped in a winzip file, and
the user unzipped the spreadsheet and copied it to their desktop. One
of the formulas on one of the sheets in the spreadsheet changed, and I
can't figure out how it changed. The cell was protected, so the user
couldn't possibly have changed it. I also sent the spreadsheet to
other users in the same office and they downloaded the file to their
desktops as well. The formula was correct for the other users. I'm
trying to avoid this situation in the future, and I'm wondering if
anyone else has had a similar thing happen. Everyone is on the correct
version of Excel (2003), macros were enabled, so I can't figure out how
the formulas changed. Any suggestions would be greatly appreciated!

Connie



--

Dave Peterson

That's entirely possible; I hadn't thought of that. There are cells in
Field_Rep_Time_Sheet which are not protected as the user is entering
data on that sheet. The formula that changed is on another sheet.
Hmmmm....I'll ask the user if it's possible that he inadvertently moved
one of the lines of data. He would have had to move it, as copying the
data would not have changed the formula. That must be it. I'm not sure
how to safeguard against this for the future, as there's only so much
you can do. Perhaps I should have designed the sheet with a form for
the user input rather than having them type the data in cells. Oh
well. I'll have to inform the users not to move any data on the
Field_Rep_Time_Sheet worksheet.

That's a big help. Thanks. I appreciate it.


Dave Peterson wrote:
Any chance that the 'field rep time sheet' worksheet is not protected and the
user moved cells around on that sheet (or inserted some rows)?

I've never seen excel change formulas like this.

Connie wrote:

The formula changed to the following:

+IF('Field Rep Time Sheet'!G14=0,"",'Field Rep Time Sheet'!G14)

Connie wrote:
Here's the formula:

+IF('Field Rep Time Sheet'!G11=0,"",'Field Rep Time Sheet'!G11)

The users aren't very sophisticated with Excel, so if they broke the
protection it was by accident. However, the user sent me the
spreadsheet and it was the correct version and the protection was on.
So I'm stumped. I'm sure that the users all got the same version of
the file (and as I said I confirmed for myself that the version was
correct). Any help is appreciated!

galimi wrote:
Connie,

It may be the type of link in the formula changing. You may have a network
path that included in the formula that has changed to UNC style. Please post
the formula.
--
http://HelpExcel.com

516-984-0252


"Connie" wrote:

I have a situation which occurred when a user downloaded a spreadsheet
I created for him. I sent the spreadsheet zipped in a winzip file, and
the user unzipped the spreadsheet and copied it to their desktop. One
of the formulas on one of the sheets in the spreadsheet changed, and I
can't figure out how it changed. The cell was protected, so the user
couldn't possibly have changed it. I also sent the spreadsheet to
other users in the same office and they downloaded the file to their
desktops as well. The formula was correct for the other users. I'm
trying to avoid this situation in the future, and I'm wondering if
anyone else has had a similar thing happen. Everyone is on the correct
version of Excel (2003), macros were enabled, so I can't figure out how
the formulas changed. Any suggestions would be greatly appreciated!

Connie



--

Dave Peterson

Dave - Is there any way to test that the user is trying to move data so
I can display a message box if they're trying to move data? Thanks.

Connie wrote:
That's entirely possible; I hadn't thought of that. There are cells in
Field_Rep_Time_Sheet which are not protected as the user is entering
data on that sheet. The formula that changed is on another sheet.
Hmmmm....I'll ask the user if it's possible that he inadvertently moved
one of the lines of data. He would have had to move it, as copying the
data would not have changed the formula. That must be it. I'm not sure
how to safeguard against this for the future, as there's only so much
you can do. Perhaps I should have designed the sheet with a form for
the user input rather than having them type the data in cells. Oh
well. I'll have to inform the users not to move any data on the
Field_Rep_Time_Sheet worksheet.

That's a big help. Thanks. I appreciate it.


Dave Peterson wrote:
Any chance that the 'field rep time sheet' worksheet is not protected and the
user moved cells around on that sheet (or inserted some rows)?

I've never seen excel change formulas like this.

Connie wrote:

The formula changed to the following:

+IF('Field Rep Time Sheet'!G14=0,"",'Field Rep Time Sheet'!G14)

Connie wrote:
Here's the formula:

+IF('Field Rep Time Sheet'!G11=0,"",'Field Rep Time Sheet'!G11)

The users aren't very sophisticated with Excel, so if they broke the
protection it was by accident. However, the user sent me the
spreadsheet and it was the correct version and the protection was on.
So I'm stumped. I'm sure that the users all got the same version of
the file (and as I said I confirmed for myself that the version was
correct). Any help is appreciated!

galimi wrote:
Connie,

It may be the type of link in the formula changing. You may have a network
path that included in the formula that has changed to UNC style. Please post
the formula.
--
http://HelpExcel.com

516-984-0252


"Connie" wrote:

I have a situation which occurred when a user downloaded a spreadsheet
I created for him. I sent the spreadsheet zipped in a winzip file, and
the user unzipped the spreadsheet and copied it to their desktop. One
of the formulas on one of the sheets in the spreadsheet changed, and I
can't figure out how it changed. The cell was protected, so the user
couldn't possibly have changed it. I also sent the spreadsheet to
other users in the same office and they downloaded the file to their
desktops as well. The formula was correct for the other users. I'm
trying to avoid this situation in the future, and I'm wondering if
anyone else has had a similar thing happen. Everyone is on the correct
version of Excel (2003), macros were enabled, so I can't figure out how
the formulas changed. Any suggestions would be greatly appreciated!

Connie



--

Dave Peterson

Remember that they don't actually have to move the cell. They could
insert/delete a row/column to change that cell's address. But if they do move
the cell, then excel will adjust the formula to make sure it uses the "correct"
cell.

If I wanted to stop people from moving cells or inserting/deleting rows, I'd
protect that worksheet, too.

But you could use a worksheet_Change event to issue a warning.

But didn't the adjusted formula continue to work the way you wanted?



Connie wrote:

Dave - Is there any way to test that the user is trying to move data so
I can display a message box if they're trying to move data? Thanks.

Connie wrote:
That's entirely possible; I hadn't thought of that. There are cells in
Field_Rep_Time_Sheet which are not protected as the user is entering
data on that sheet. The formula that changed is on another sheet.
Hmmmm....I'll ask the user if it's possible that he inadvertently moved
one of the lines of data. He would have had to move it, as copying the
data would not have changed the formula. That must be it. I'm not sure
how to safeguard against this for the future, as there's only so much
you can do. Perhaps I should have designed the sheet with a form for
the user input rather than having them type the data in cells. Oh
well. I'll have to inform the users not to move any data on the
Field_Rep_Time_Sheet worksheet.

That's a big help. Thanks. I appreciate it.


Dave Peterson wrote:
Any chance that the 'field rep time sheet' worksheet is not protected and the
user moved cells around on that sheet (or inserted some rows)?

I've never seen excel change formulas like this.

Connie wrote:

The formula changed to the following:

+IF('Field Rep Time Sheet'!G14=0,"",'Field Rep Time Sheet'!G14)

Connie wrote:
Here's the formula:

+IF('Field Rep Time Sheet'!G11=0,"",'Field Rep Time Sheet'!G11)

The users aren't very sophisticated with Excel, so if they broke the
protection it was by accident. However, the user sent me the
spreadsheet and it was the correct version and the protection was on.
So I'm stumped. I'm sure that the users all got the same version of
the file (and as I said I confirmed for myself that the version was
correct). Any help is appreciated!

galimi wrote:
Connie,

It may be the type of link in the formula changing. You may have a network
path that included in the formula that has changed to UNC style. Please post
the formula.
--
http://HelpExcel.com

516-984-0252


"Connie" wrote:

I have a situation which occurred when a user downloaded a spreadsheet
I created for him. I sent the spreadsheet zipped in a winzip file, and
the user unzipped the spreadsheet and copied it to their desktop. One
of the formulas on one of the sheets in the spreadsheet changed, and I
can't figure out how it changed. The cell was protected, so the user
couldn't possibly have changed it. I also sent the spreadsheet to
other users in the same office and they downloaded the file to their
desktops as well. The formula was correct for the other users. I'm
trying to avoid this situation in the future, and I'm wondering if
anyone else has had a similar thing happen. Everyone is on the correct
version of Excel (2003), macros were enabled, so I can't figure out how
the formulas changed. Any suggestions would be greatly appreciated!

Connie



--

Dave Peterson

--

Dave Peterson

The input data is from C9:F15. These are the only cells on the
spreadsheet which are unlocked. The rest of the sheet is protected, so
the user can't move an entire row. There is a second page in the
workbook which links to each of the input cells and performs some
calculations. What the user did, I believe, is move a range within the
unlocked cells, say C9:F9, to another area within the unlocked cells,
say C10:F10. The formula which linked to C9:F9 correctly points to
C10:F10 after the move, but the formula which links to C10:F10 contains
a #REF in the cell.

I'm sure I can find it if I look around, but do you know what
function/syntax I should put on the worksheet_change event page to
detect that a user is trying to move cells within the unloced cells?
I'd like to simply display a message box to the user that they should
not do the move. I can't protect the entire sheet; otherwise the users
can't enter the required data. The sheet which contains the formulas
is, of course, protected.

I can't believe I spent a lot of time developing the spreadsheet and
within an hour, the users had broken it. What a lesson learned!

Thanks again.

Connie


Dave Peterson wrote:
Remember that they don't actually have to move the cell. They could
insert/delete a row/column to change that cell's address. But if they do move
the cell, then excel will adjust the formula to make sure it uses the "correct"
cell.

If I wanted to stop people from moving cells or inserting/deleting rows, I'd
protect that worksheet, too.

But you could use a worksheet_Change event to issue a warning.

But didn't the adjusted formula continue to work the way you wanted?



Connie wrote:

Dave - Is there any way to test that the user is trying to move data so
I can display a message box if they're trying to move data? Thanks.

Connie wrote:
That's entirely possible; I hadn't thought of that. There are cells in
Field_Rep_Time_Sheet which are not protected as the user is entering
data on that sheet. The formula that changed is on another sheet.
Hmmmm....I'll ask the user if it's possible that he inadvertently moved
one of the lines of data. He would have had to move it, as copying the
data would not have changed the formula. That must be it. I'm not sure
how to safeguard against this for the future, as there's only so much
you can do. Perhaps I should have designed the sheet with a form for
the user input rather than having them type the data in cells. Oh
well. I'll have to inform the users not to move any data on the
Field_Rep_Time_Sheet worksheet.

That's a big help. Thanks. I appreciate it.


Dave Peterson wrote:
Any chance that the 'field rep time sheet' worksheet is not protected and the
user moved cells around on that sheet (or inserted some rows)?

I've never seen excel change formulas like this.

Connie wrote:

The formula changed to the following:

+IF('Field Rep Time Sheet'!G14=0,"",'Field Rep Time Sheet'!G14)

Connie wrote:
Here's the formula:

+IF('Field Rep Time Sheet'!G11=0,"",'Field Rep Time Sheet'!G11)

The users aren't very sophisticated with Excel, so if they broke the
protection it was by accident. However, the user sent me the
spreadsheet and it was the correct version and the protection was on.
So I'm stumped. I'm sure that the users all got the same version of
the file (and as I said I confirmed for myself that the version was
correct). Any help is appreciated!

galimi wrote:
Connie,

It may be the type of link in the formula changing. You may have a network
path that included in the formula that has changed to UNC style. Please post
the formula.
--
http://HelpExcel.com

516-984-0252


"Connie" wrote:

I have a situation which occurred when a user downloaded a spreadsheet
I created for him. I sent the spreadsheet zipped in a winzip file, and
the user unzipped the spreadsheet and copied it to their desktop. One
of the formulas on one of the sheets in the spreadsheet changed, and I
can't figure out how it changed. The cell was protected, so the user
couldn't possibly have changed it. I also sent the spreadsheet to
other users in the same office and they downloaded the file to their
desktops as well. The formula was correct for the other users. I'm
trying to avoid this situation in the future, and I'm wondering if
anyone else has had a similar thing happen. Everyone is on the correct
version of Excel (2003), macros were enabled, so I can't figure out how
the formulas changed. Any suggestions would be greatly appreciated!

Connie



--

Dave Peterson

--

Dave Peterson

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Me.Range("a1")) Is Nothing Then
Exit Sub
End If

MsgBox "Hey, you're changing: " & Target.Address(0, 0)

End Sub

But I think you'll have better luck just training the user not to use cut|paste.

Connie wrote:

The input data is from C9:F15. These are the only cells on the
spreadsheet which are unlocked. The rest of the sheet is protected, so
the user can't move an entire row. There is a second page in the
workbook which links to each of the input cells and performs some
calculations. What the user did, I believe, is move a range within the
unlocked cells, say C9:F9, to another area within the unlocked cells,
say C10:F10. The formula which linked to C9:F9 correctly points to
C10:F10 after the move, but the formula which links to C10:F10 contains
a #REF in the cell.

I'm sure I can find it if I look around, but do you know what
function/syntax I should put on the worksheet_change event page to
detect that a user is trying to move cells within the unloced cells?
I'd like to simply display a message box to the user that they should
not do the move. I can't protect the entire sheet; otherwise the users
can't enter the required data. The sheet which contains the formulas
is, of course, protected.

I can't believe I spent a lot of time developing the spreadsheet and
within an hour, the users had broken it. What a lesson learned!

Thanks again.

Connie

Dave Peterson wrote:
Remember that they don't actually have to move the cell. They could
insert/delete a row/column to change that cell's address. But if they do move
the cell, then excel will adjust the formula to make sure it uses the "correct"
cell.

If I wanted to stop people from moving cells or inserting/deleting rows, I'd
protect that worksheet, too.

But you could use a worksheet_Change event to issue a warning.

But didn't the adjusted formula continue to work the way you wanted?



Connie wrote:

Dave - Is there any way to test that the user is trying to move data so
I can display a message box if they're trying to move data? Thanks.

Connie wrote:
That's entirely possible; I hadn't thought of that. There are cells in
Field_Rep_Time_Sheet which are not protected as the user is entering
data on that sheet. The formula that changed is on another sheet.
Hmmmm....I'll ask the user if it's possible that he inadvertently moved
one of the lines of data. He would have had to move it, as copying the
data would not have changed the formula. That must be it. I'm not sure
how to safeguard against this for the future, as there's only so much
you can do. Perhaps I should have designed the sheet with a form for
the user input rather than having them type the data in cells. Oh
well. I'll have to inform the users not to move any data on the
Field_Rep_Time_Sheet worksheet.

That's a big help. Thanks. I appreciate it.


Dave Peterson wrote:
Any chance that the 'field rep time sheet' worksheet is not protected and the
user moved cells around on that sheet (or inserted some rows)?

I've never seen excel change formulas like this.

Connie wrote:

The formula changed to the following:

+IF('Field Rep Time Sheet'!G14=0,"",'Field Rep Time Sheet'!G14)

Connie wrote:
Here's the formula:

+IF('Field Rep Time Sheet'!G11=0,"",'Field Rep Time Sheet'!G11)

The users aren't very sophisticated with Excel, so if they broke the
protection it was by accident. However, the user sent me the
spreadsheet and it was the correct version and the protection was on.
So I'm stumped. I'm sure that the users all got the same version of
the file (and as I said I confirmed for myself that the version was
correct). Any help is appreciated!

galimi wrote:
Connie,

It may be the type of link in the formula changing. You may have a network
path that included in the formula that has changed to UNC style. Please post
the formula.
--
http://HelpExcel.com

516-984-0252


"Connie" wrote:

I have a situation which occurred when a user downloaded a spreadsheet
I created for him. I sent the spreadsheet zipped in a winzip file, and
the user unzipped the spreadsheet and copied it to their desktop. One
of the formulas on one of the sheets in the spreadsheet changed, and I
can't figure out how it changed. The cell was protected, so the user
couldn't possibly have changed it. I also sent the spreadsheet to
other users in the same office and they downloaded the file to their
desktops as well. The formula was correct for the other users. I'm
trying to avoid this situation in the future, and I'm wondering if
anyone else has had a similar thing happen. Everyone is on the correct
version of Excel (2003), macros were enabled, so I can't figure out how
the formulas changed. Any suggestions would be greatly appreciated!

Connie



--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Maybe you could make it better.

If you name each cell that gets input (insert|Name|define), then you could use
this kind of thing:

Kind of like this (with only 3 named cells):

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myNames As Variant
Dim myAddresses As Variant
Dim iCtr As Long
Dim TestRng As Range
Dim ErrorFound As Boolean

myNames = Array("Input1", "Input2", "Input3")
myAddresses = Array("A1", "C9", "E7")

For iCtr = LBound(myNames) To UBound(myNames)
Set TestRng = Nothing
On Error Resume Next
Set TestRng = Me.Range(myNames(iCtr))
On Error GoTo 0

If TestRng Is Nothing Then
'deleted
ErrorFound = True
Exit For
Else
If LCase(TestRng.Address(0, 0)) < LCase(myAddresses(iCtr)) Then
'moved
ErrorFound = True
Exit For
End If
End If
Next iCtr

If ErrorFound = True Then
With Application
.EnableEvents = False
.Undo
End With
MsgBox "Please don't move these cells." & vbLf & _
"Your changes have been reset!"
End If

Application.EnableEvents = True

End Sub

Dave Peterson wrote:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Me.Range("a1")) Is Nothing Then
Exit Sub
End If

MsgBox "Hey, you're changing: " & Target.Address(0, 0)

End Sub

But I think you'll have better luck just training the user not to use cut|paste.

Connie wrote:

The input data is from C9:F15. These are the only cells on the
spreadsheet which are unlocked. The rest of the sheet is protected, so
the user can't move an entire row. There is a second page in the
workbook which links to each of the input cells and performs some
calculations. What the user did, I believe, is move a range within the
unlocked cells, say C9:F9, to another area within the unlocked cells,
say C10:F10. The formula which linked to C9:F9 correctly points to
C10:F10 after the move, but the formula which links to C10:F10 contains
a #REF in the cell.

I'm sure I can find it if I look around, but do you know what
function/syntax I should put on the worksheet_change event page to
detect that a user is trying to move cells within the unloced cells?
I'd like to simply display a message box to the user that they should
not do the move. I can't protect the entire sheet; otherwise the users
can't enter the required data. The sheet which contains the formulas
is, of course, protected.

I can't believe I spent a lot of time developing the spreadsheet and
within an hour, the users had broken it. What a lesson learned!

Thanks again.

Connie

Dave Peterson wrote:
Remember that they don't actually have to move the cell. They could
insert/delete a row/column to change that cell's address. But if they do move
the cell, then excel will adjust the formula to make sure it uses the "correct"
cell.

If I wanted to stop people from moving cells or inserting/deleting rows, I'd
protect that worksheet, too.

But you could use a worksheet_Change event to issue a warning.

But didn't the adjusted formula continue to work the way you wanted?



Connie wrote:

Dave - Is there any way to test that the user is trying to move data so
I can display a message box if they're trying to move data? Thanks.

Connie wrote:
That's entirely possible; I hadn't thought of that. There are cells in
Field_Rep_Time_Sheet which are not protected as the user is entering
data on that sheet. The formula that changed is on another sheet.
Hmmmm....I'll ask the user if it's possible that he inadvertently moved
one of the lines of data. He would have had to move it, as copying the
data would not have changed the formula. That must be it. I'm not sure
how to safeguard against this for the future, as there's only so much
you can do. Perhaps I should have designed the sheet with a form for
the user input rather than having them type the data in cells. Oh
well. I'll have to inform the users not to move any data on the
Field_Rep_Time_Sheet worksheet.

That's a big help. Thanks. I appreciate it.


Dave Peterson wrote:
Any chance that the 'field rep time sheet' worksheet is not protected and the
user moved cells around on that sheet (or inserted some rows)?

I've never seen excel change formulas like this.

Connie wrote:

The formula changed to the following:

+IF('Field Rep Time Sheet'!G14=0,"",'Field Rep Time Sheet'!G14)

Connie wrote:
Here's the formula:

+IF('Field Rep Time Sheet'!G11=0,"",'Field Rep Time Sheet'!G11)

The users aren't very sophisticated with Excel, so if they broke the
protection it was by accident. However, the user sent me the
spreadsheet and it was the correct version and the protection was on.
So I'm stumped. I'm sure that the users all got the same version of
the file (and as I said I confirmed for myself that the version was
correct). Any help is appreciated!

galimi wrote:
Connie,

It may be the type of link in the formula changing. You may have a network
path that included in the formula that has changed to UNC style. Please post
the formula.
--
http://HelpExcel.com

516-984-0252


"Connie" wrote:

I have a situation which occurred when a user downloaded a spreadsheet
I created for him. I sent the spreadsheet zipped in a winzip file, and
the user unzipped the spreadsheet and copied it to their desktop. One
of the formulas on one of the sheets in the spreadsheet changed, and I
can't figure out how it changed. The cell was protected, so the user
couldn't possibly have changed it. I also sent the spreadsheet to
other users in the same office and they downloaded the file to their
desktops as well. The formula was correct for the other users. I'm
trying to avoid this situation in the future, and I'm wondering if
anyone else has had a similar thing happen. Everyone is on the correct
version of Excel (2003), macros were enabled, so I can't figure out how
the formulas changed. Any suggestions would be greatly appreciated!

Connie



--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Connie,

We've run into the same situations. We have taken to writing VBA
that disables the various cut options as well as the drag and drop
funtionality when they open the workbook. Trouble is that they have to
enable macros for that to happen. In our case it isn't a big deal
since they need the macros to get full functionality but may be an
issue with yours. I can certainly provide our code.
Anyone know if the next Excel version will have a way of addressing
this without VBA?

Robert

Robert -

Thanks so much for your response. We have developed a "workaround";
however, it's not pretty, and the user must actually begin with a new
spreadsheet. Disabling the cut and paste features would be much
better. If you don't mind providing your code, I'd certainly love to
have it. Thanks again.

Connie

wrote:
Connie,

We've run into the same situations. We have taken to writing VBA
that disables the various cut options as well as the drag and drop
funtionality when they open the workbook. Trouble is that they have to
enable macros for that to happen. In our case it isn't a big deal
since they need the macros to get full functionality but may be an
issue with yours. I can certainly provide our code.
Anyone know if the next Excel version will have a way of addressing
this without VBA?

Robert

Robert -

Thanks so much for your response. We have developed a "workaround";
however, it's not pretty, and the user must actually begin with a new
spreadsheet. Disabling the cut and paste features would be much
better. If you don't mind providing your code, I'd certainly love to
have it. Thanks again.

Connie

wrote:
Connie,

We've run into the same situations. We have taken to writing VBA
that disables the various cut options as well as the drag and drop
funtionality when they open the workbook. Trouble is that they have to
enable macros for that to happen. In our case it isn't a big deal
since they need the macros to get full functionality but may be an
issue with yours. I can certainly provide our code.
Anyone know if the next Excel version will have a way of addressing
this without VBA?

Robert