Moved cell & Ref

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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,FALSE),"")

Column H:
=IF($B360<>"",VLOOKUP($B360&"*",$E$44:$O$346,11,FALSE),"")
 
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
 
Thank You, Jim!
--
Thanks for your help.
Karen53


Jim Rech said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top