Moved cell & Ref
 

Hi,

I'm not sure how to solve this.

My workbook's structure is protected. The sheets are protected as well. I
am using Excel 2003.

I have a group of cells B360:B389 which are user input cells. My formulas
use VLookup. The trouble is the user can drag the cells within the user
range and this trashes the VLookups.

Is there a way to restrict their ability to drag cells? Or perhaps another
solution?

I have tried this with and without the $ before the 360 but the formulas
still Ref.

Column B:
User input

Column E:
=IF($B360<"",VLOOKUP($B360&"*",$E$44:$O$346,1,FAL SE),"")

Column H:
=IF($B360<"",VLOOKUP($B360&"*",$E$44:$O$346,11,FA LSE),"")

--
Thanks for your help.
Karen53

Moved cell & Ref
 

The problem involves Cut and Paste as well as Drag and Drop as they have the
same effect. on your formulas.

The 'solution' involves a macro that disables both as well as disabling the
user's ability to turn D&D back on through Tools, Options. So, assuming you
know what to do with these:

''To be called by Auto_Open or Workbook_Open
Sub DisableCuts()
Dim oCtls As CommandBarControls, oCtl As CommandBarControl
Set oCtls = CommandBars.FindControls(ID:=21) ''Cut
If Not oCtls Is Nothing Then
For Each oCtl In oCtls
oCtl.Enabled = False
Next
End If
Set oCtls = CommandBars.FindControls(ID:=522) ''Options
If Not oCtls Is Nothing Then
For Each oCtl In oCtls
oCtl.Enabled = False
Next
End If
With Application
.OnKey "^x", ""
.OnKey "+{Del}", ""
.CellDragAndDrop = False
End With
End Sub

''To be called by Auto_Close or Workbook_BeforeClose
Sub EnableCuts()
Dim oCtls As CommandBarControls, oCtl As CommandBarControl
Set oCtls = CommandBars.FindControls(ID:=21)
If Not oCtls Is Nothing Then
For Each oCtl In oCtls
oCtl.Enabled = True
Next
End If
Set oCtls = CommandBars.FindControls(ID:=522)
If Not oCtls Is Nothing Then
For Each oCtl In oCtls
oCtl.Enabled = True
Next
End If
With Application
.OnKey "^x"
.OnKey "+{Del}"
.CellDragAndDrop = True
End With
End Sub


--
Jim
"Karen53" wrote in message
...
Hi,

I'm not sure how to solve this.

My workbook's structure is protected. The sheets are protected as well.
I
am using Excel 2003.

I have a group of cells B360:B389 which are user input cells. My formulas
use VLookup. The trouble is the user can drag the cells within the user
range and this trashes the VLookups.

Is there a way to restrict their ability to drag cells? Or perhaps
another
solution?

I have tried this with and without the $ before the 360 but the formulas
still Ref.

Column B:
User input

Column E:
=IF($B360<"",VLOOKUP($B360&"*",$E$44:$O$346,1,FAL SE),"")

Column H:
=IF($B360<"",VLOOKUP($B360&"*",$E$44:$O$346,11,FA LSE),"")

--
Thanks for your help.
Karen53

Moved cell & Ref
 

Thank You, Jim!
--
Thanks for your help.
Karen53


"Jim Rech" wrote:

The problem involves Cut and Paste as well as Drag and Drop as they have the
same effect. on your formulas.

The 'solution' involves a macro that disables both as well as disabling the
user's ability to turn D&D back on through Tools, Options. So, assuming you
know what to do with these:

''To be called by Auto_Open or Workbook_Open
Sub DisableCuts()
Dim oCtls As CommandBarControls, oCtl As CommandBarControl
Set oCtls = CommandBars.FindControls(ID:=21) ''Cut
If Not oCtls Is Nothing Then
For Each oCtl In oCtls
oCtl.Enabled = False
Next
End If
Set oCtls = CommandBars.FindControls(ID:=522) ''Options
If Not oCtls Is Nothing Then
For Each oCtl In oCtls
oCtl.Enabled = False
Next
End If
With Application
.OnKey "^x", ""
.OnKey "+{Del}", ""
.CellDragAndDrop = False
End With
End Sub

''To be called by Auto_Close or Workbook_BeforeClose
Sub EnableCuts()
Dim oCtls As CommandBarControls, oCtl As CommandBarControl
Set oCtls = CommandBars.FindControls(ID:=21)
If Not oCtls Is Nothing Then
For Each oCtl In oCtls
oCtl.Enabled = True
Next
End If
Set oCtls = CommandBars.FindControls(ID:=522)
If Not oCtls Is Nothing Then
For Each oCtl In oCtls
oCtl.Enabled = True
Next
End If
With Application
.OnKey "^x"
.OnKey "+{Del}"
.CellDragAndDrop = True
End With
End Sub


--
Jim
"Karen53" wrote in message
...
Hi,

I'm not sure how to solve this.

My workbook's structure is protected. The sheets are protected as well.
I
am using Excel 2003.

I have a group of cells B360:B389 which are user input cells. My formulas
use VLookup. The trouble is the user can drag the cells within the user
range and this trashes the VLookups.

Is there a way to restrict their ability to drag cells? Or perhaps
another
solution?

I have tried this with and without the $ before the 360 but the formulas
still Ref.

Column B:
User input

Column E:
=IF($B360<"",VLOOKUP($B360&"*",$E$44:$O$346,1,FAL SE),"")

Column H:
=IF($B360<"",VLOOKUP($B360&"*",$E$44:$O$346,11,FA LSE),"")

--
Thanks for your help.
Karen53