Excell VBA questions

[hams]

HI,

I've seen alot of posts on "how to cut and paste", but I actually want to prevent it. We have a worksheet that gets changed by multiple people. Although I've taken steps to lock and protect the worksheet, the borders around the cells still keep getting rearranged or remove when somebody cuts and pastes.

I would like my coworkers to cut/copy and paste the contents of a cell, but NOT cutting and pasting the borders. Disabling CUT and not COPY would probably work to.



Is this possible?

---------------------------------------------------------------------------------

I also have a macro button that will let my coworkers make some text red in a protected worksheet. My question is,
how do I add a password to this code to protect with a pass?

Sub Red()
    CelFont 3
End Sub

Function CelFont(ByVal sglColor As Single)
    If ActiveCell.Locked Then
        MsgBox "Cell is locked."
    Else
        ActiveSheet.Unprotect
        ActiveCell.Font.ColorIndex = sglColor
        ActiveSheet.Protect
    End If
End Function

 

[SkipVought]

Hi hams,

1. Create a custom Main Menu WITHOUT Edit/Cut and replace the Default Main Menu

2. Create a custom Standard Toolbar WITHOUT the Cut icon

3. Attach cntl+X to a null macro to disable the keyboard shortcut for CUT.

Have fun!

Skip,
metzgsk@voughtaircraft.com

 

[xlbo]

For password - could use either an inputbox:

myPass = inputbox("Enter Password&quot
if myPass = "Therightpassword"
then do stuff
Else
msgbox "Incorrect Password"
exit sub
end if

The inputbox method has more properties than this so you can make it look better but I would go with creating a simple form with a textbox 'cos then you have more control - plus, you can mask the text entry characters

HTH
Geoff

 

[vwhite]

Is changing the Menus and Toolbars the only way to do this?

I also have this problem - most of my worksheet is password protected. The cells that aren't have formating, range names and data validation - all of which get rearranged if the user cuts and pastes. However I don't think they will be very impressed if I take away the cut and paste option entirely.

Can you change the standard cut and paste to just paste values?

VW.....

 

[Loomah]

Hi
There's usually more than one way to skin a cat! The following will disable CUT from all menus, unfortunately I never forund out how to disable CTRL+X, so I would suggest Skip's suggestiion is the way to go. Doesn't prevent drag and drop either!

Put the disable code in the workbook activate event and re-enable in the workbook deactivate event. This will ensure that the menus are available in workbooks where thay are required. You could even break it down further to disable only when certain sheets are active.

Sub DisableCut()
Dim ctl
Dim myControls
    Set myControls = CommandBars.FindControls _
        (Type:=msoControlButton, ID:=21) 'ID 21 = CUT
    For Each ctl In myControls
        ctl.Enabled = 0 '1 to enable
    Next ctl
End Sub

Other IDs are
COPY - 19
PASTE - 22
PASTE SPECIAL - 755

As Skip said, Have Fun!!
;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[Nikita6003]

Loomah, vwhite

Not elegant nor foolproof, but:

If you want to disable an existing shortcut such as ctrl-x just create an empty macro & assign it to the shortcut key combination you want to disable


Cheers
Nikki

 

[vwhite]

thanx Loomah, Skip and Nikita6003

i have disabled the cut control and assigned the Null macro to CTRL-X...all working well....drag and drop still available...chances are they won't use it - but then again.....

VW...

 

[vwhite]

I've got one more problem with this solution. It works in Excel 2000 but not in Excel 97. I get an error message on CommandBars.FindControls - Method of data member not found. I assume it is because it does not belong to the Excel Version 8.0 Object Library....

Is there any way to do the same thing in version 8.0?

VW....

 

[vwhite]

i think i may have answered by own question....

it seems to work in V8.0 with this code:

Dim myControl
Set myControl = CommandBars.FindControl _
(Type:=msoControlButton, Id:=21)
myControl.Control.Enabled = 0


VW....