Excel Command Disable

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Financeguy

Thread Starter
Joined
Oct 16, 2003
Messages
2
I have an Excel worksheet protected, with several cells unlocked.

Unfortunately, if users CUT and paste from one unlocked cell to another, all dependent formulas go #REF.

I would like to disable Ctrl+X or Cut with a VBA routine.

Any great ideas?

Finance Guy
 
Joined
Aug 30, 2003
Messages
2,702
You can disable most anything. May I ask one question first? What happens if one of the unlocked cells is dragged and dropped onto another of the unlocked cells?

Rgds,
Andy
 
Joined
Jul 26, 2003
Messages
2,431
hi Finance Guy.....

you can prevent the user from accessing the cells in the first place :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$B$4" Then
Range("A1").Activate
End If
End Sub


the above will prevent the user from selecting the cell that you do not want them to copy
(it sends 'em packin' to A1 ;) )

or instead of just moving them to A1... you can make it so that it selects a specific range of cells that is acceptable to be copied :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$B$4:$F$7" Then
Range("yourNamedRange").Activate
End If
End Sub


or whatever (get creative ;) )

Does something like this help you ?

:) khaki
 
Joined
Aug 30, 2003
Messages
2,702
>> you can prevent the user from accessing
>> the cells in the first place

My basic premise was that the cells were unlocked so that editing was an option, therefore disabling selection wouldn't do. Maybe I got that wrong?

I believe in XL2K2 you can define which elements (?& maybe functions?) protection applies to -- might be worth looking into.

Rgds,
Andy
 
Joined
Aug 30, 2003
Messages
2,702
Can you give any more info on the ranges & formulas? For example, with 100 in B2 and =B2+500 in E1 ; if I cut B2 to B10, E1 auto-adjusts to =B10+500.

Rgds,
Andy
 
Joined
Jul 26, 2003
Messages
2,431
My basic premise was that the cells were unlocked so that editing was an option, therefore disabling selection wouldn't do. Maybe I got that wrong?
good morning Andy..... (well..... it is here, anyway :) )....

that's why I offered the second option.
It allows for editing of individual cells.... but not the selection of the specified range.

I was only providing a concept.......
Finance Guy would need to enhance and modify the code for his particular application.

:)
 
Joined
Aug 30, 2003
Messages
2,702
Greeting to (?New?) Jersey. The climate here is extraordinarily mild. How do you feel about

Sub DisableCut()
If ActiveWorkbook.Name = "test.xls" Then
MsgBox "Cutting disabled."
End If
End Sub

with a shortcut key of CTRL+X?

FinanceGuy would need to consider disabling rightclick menu options too tho'.

Rgds,
Andy
 
Joined
Jul 26, 2003
Messages
2,431
Originally posted by XL Guru:
Can you give any more info on the ranges & formulas? For example, with 100 in B2 and =B2+500 in E1 ; if I cut B2 to B10, E1 auto-adjusts to =B10+500.

Rgds,
Andy
hi Andy.....
I'm not sure what it is that you are asking.

if you need the formula in E1 to remain as =B2+500...
then you would need to protect B2 from being CUT (and my first option in my earlier post would accomplish that).

If it is necessary to keep B2 editable...
you could have an Input Box appear when B2 is activated....
and then have the value updated in that way (which will prevent the cell from being cut while also allowing for updates/editing)

is that what you were asking about :confused:

:) khaki
 
Joined
Aug 30, 2003
Messages
2,702
Yup, starting to catch up with you now.

>> Finance Guy would need to enhance and
>> modify the code for his particular application.

Perhaps I should flag that
If Target.Address = "$B$4" Then
works for B4 but not A4:B4.

Something like
If Not Intersect(Target, Range("B4:B6")) Is Nothing Then
Range("A1").Select
kicks in if the entire selection includes B4 and/or B5 and/or B6.

Rgds,
Andy
 
Joined
Jul 26, 2003
Messages
2,431
Originally posted by XL Guru:
Greeting to (?New?) Jersey. The climate here is extraordinarily mild. How do you feel about

Sub DisableCut()
If ActiveWorkbook.Name = "test.xls" Then
MsgBox "Cutting disabled."
End If
End Sub

with a shortcut key of CTRL+X?

FinanceGuy would need to consider disabling rightclick menu options too tho'.

Rgds,
Andy
Yes.... NEW JERSEY :)

To protect against right-clicking a cell.....
Finance Guy can try some variation of this :

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If Target.Address = "$B$2" Then
MsgBox "This cell cannot be cut. I'm packin' ya off to A1."
Range("A1").Activate
End If
End Sub


...although that does not protect against CTRL+X


In that case.... maybe he should make it so that the cells can only be updated/edited by using a form or an Input Box.
Something like this :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim B2val As Integer
B2val = Range("B2").Value
If Target.Address = "$B$2" Then
On Error Resume Next
B2val = InputBox("This is the only way to update/edit this cell", , B2val)
Range("B2").Value = B2val
ActiveCell.Offset(1, 0).Activate
End If
End Sub


just some ideas

:) khaki
 
Joined
Jul 26, 2003
Messages
2,431
If Not Intersect(Target, Range("B4:B6")) Is Nothing Then
Range("A1").Select
yup.... that works well to protect the range if the user selects the protected cells OR if the user extends beyond (but includes) the protected cells
(good thinking :) )

I think that Finance Guy has some stuff to play around with now :)

:) khaki
 

Financeguy

Thread Starter
Joined
Oct 16, 2003
Messages
2
Wow fantastic response from both Andy/XL Guru and Khaki. Thank you both.

I want to keep copy and paste functions available as well as data entry.
I want to disable:
*CTRL+X
*EDIT | CUT
*Rt Mouse Cut

Drag and drop usually copies so I can leave that.
Most of the sheet is protected by locked cells, thats the primary lock-out/protection.
I want the disablement to be for all sheets in the workbook, so selected ranges are not something I want to tackle.


I will start with:
1)
Sub DisableCut()
If ActiveWorkbook.Name = "test.xls" Then
MsgBox "Cutting disabled."
End If
End Sub
with a shortcut key of CTRL+X?

AND

2)
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
MsgBox "Cutting disabled."
End Sub

I'll return later to share my results

Thanks again folks.
-FinanceGuy
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top