1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Command Disable

Discussion in 'Business Applications' started by Financeguy, Oct 16, 2003.

Thread Status:
Not open for further replies.
Advertisement
  1. Financeguy

    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
     
  2. XL Guru

    XL Guru

    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
     
  3. khaki

    khaki

    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
     
  4. XL Guru

    XL Guru

    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
     
  5. XL Guru

    XL Guru

    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
     
  6. khaki

    khaki

    Joined:
    Jul 26, 2003
    Messages:
    2,431
    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.

    :)
     
  7. XL Guru

    XL Guru

    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
     
  8. khaki

    khaki

    Joined:
    Jul 26, 2003
    Messages:
    2,431
    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
     
  9. XL Guru

    XL Guru

    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
     
  10. khaki

    khaki

    Joined:
    Jul 26, 2003
    Messages:
    2,431
    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
     
  11. khaki

    khaki

    Joined:
    Jul 26, 2003
    Messages:
    2,431
    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
     
  12. Financeguy

    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
     
  13. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/172477

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice