Disable ClearContents

Nov 30, 2006

I found this great code on this forum to disable copy/paste.

I would like to also disable ClearContents..

I can gather from the code that I just need to add a line similar to
EnableControl 755, False ' pastespecial

but change the # for the clearcontents command. But How can I find that number?


Sub DisableCopyCutAndPaste()
EnableControl 21, False ' cut
EnableControl 19, False ' copy
EnableControl 22, False ' paste
EnableControl 755, False ' pastespecial
Application.OnKey "^c", ""
Application.OnKey "^v", ""
Application.OnKey "+{DEL}", ""
Application.OnKey "+{INSERT}", ""
Application.CellDragAndDrop = False
Application.OnDoubleClick = "Dummy"
CommandBars("ToolBar List").Enabled = False
End Sub

View 9 Replies


ADVERTISEMENT

Trying To Better Understand ClearContents Loop

Mar 11, 2008

I inteded to look at the contents for column B from row 16 down to whatever had data and clear any contents. The firs time I ran the loop, it worked nicely. However, the second time I ran it, it cleared contents from row 16. The third time I ran it, it cleared data from row 15, so forth and so on. In other words, the loop keeps moving up in rows clearing data that I want to keep. Is there any way to tell the code that I only want to whatever data there is BELOW row 16?

View 11 Replies View Related

Speed Up Clearcontents Macro

Nov 4, 2008

The macro clears specific columns in a row when you click anywhere on the row and then hit the command button. It clears the first range and 2nd range in 2 distinct steps, and takes up to 3 seconds.

View 5 Replies View Related

ClearContents Of Merged Cell Causes VB Error

Apr 24, 2002

I can select the merged cell, right click and ClearContents with no problem. But VB aborts with message "Cannot change part of a merged cell".

View 2 Replies View Related

How To Disable #N/A

Jul 23, 2007

I have this following codes on cell G6

=VLOOKUP(G5,'P1'!K133:L135,2)

i want it to show an empty cell instead of #N/A if it returns 0 value...

View 9 Replies View Related

Disable Save As PDF Or XPS

Apr 16, 2013

Is there a way to disable 'Save as PDF or XPS' with the macro? I have tried disabling 'Save as' with the below VBA code but it does not disable 'Save as PDF or XPS'.

VB:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI Then
MsgBox "Save As is disabled", vbInformation
Cancel = True
End If
End Sub

Also I have a macro which saves the excel report in a PDF format with 'Save as PDF or XPS' utility. I would like to enable 'Save as PDF or XPS' when this macro is started and again disable 'Save as PDF or XPS' when this macro stops.

View 1 Replies View Related

Disable A Button

Feb 1, 2010

I have a button on excel form. I need to disable it once user has clicked on it.

I couldnt find name of it on the form anywhere or how to refer it to in the macro.

View 9 Replies View Related

Disable Row Deletion

Mar 13, 2009

Excel 2003 - VBA - How do you detect when a row has been deleted in VBA? Better yet, how do you detect when a row is about to be deleted? I also would like to know the row number. I do not see an event handler that will inform me of the "Delete" event.

View 2 Replies View Related

Disable Macro Once Run

May 26, 2009

Is it possible to disable macro until next day once clicked to run

View 2 Replies View Related

Disable Cut And Drag Only Using VBA?

Jan 5, 2013

I need to disable cut and drag only in a workbook. The idea is to avoid formula reference errors when unlocked cells are cut and pasted or dragged. I can find many macros online but none that do only these two things.

View 2 Replies View Related

Always Disable Zero Values?

May 27, 2013

I am looking for a way that I can ensure that Zero Values are never shown, either when you open a new workbook or open a new window. Preferable, one which I can install for any user who needs this. (such as via Book.XLA I think) but AutoExec is fine as long as it is alwys loaded.

View 1 Replies View Related

How To Disable Read Only

Jul 10, 2013

I've got an excel sheet from a guy at work who decided to lock the entire document down so I can't do anything with it. Virtually all the ribbon options are greyed out, there isn't anything locking it in the code, I've tried checking the save as "read-only" option and it's not there. How I can actually save this is editable? I've looked in windows explorer and it's not read-only in there either. I tried save-as and even tried making it an XLS instead of an XLSM and it still won't let me edit it

View 1 Replies View Related

Disable Right Click. Can It Be Done?

May 1, 2007

Need code or way of disabling the Right Click feature in Excel? I think you can change it in the computer settings but I only want to disable it for a particular workbook so I am hoping there is a VBA Code that will tell the user "Right Click Not Allowed, Please use Cut and Paste or Ctrl +V and Ctrl +C"

View 9 Replies View Related

Disable Comments

Mar 3, 2008

I am sure this can be done - I found a similar thread but in a different context to what I need.

I have a user of a workbook who insists on putting irritating red cornered comments in cells, rather than using the handy (and rather large) comments field I put in, especially for her.

View 9 Replies View Related

How To Disable The X On A Form

Mar 18, 2008

i know in VB6 i could disable the X in the corner so someone can't X out of my form, and i see that's not an option in VBA.

So how can i stop the form from terminating when the user clicks that? i want them to be forced to use one of my buttons to close out and finish.

i tried adding something simple like Exit Sub to the Userform_Terminate() sub, but nothing. i can make code run when they click that, but i can't stop it from closing. what to do?

also, the form is being called by macro that does alot of other stuff before and after the form, so just opening a new instance of the form won't help unless you can stop my other code from continueing until the form is actually done...

View 9 Replies View Related

Way To Disable Help Feature

May 22, 2009

I just want to know if there is anyway to disable the help feature in Microsoft Excel?

I have to administer some exams.

View 9 Replies View Related

Disable Checkbox In VBA

Oct 6, 2009

I have a sheet with a number of checkboxes, and I need to conditionally enable/disable some of them.

For example, if the user checks Box1, then the question for Box2 becomes relevant and Box2 becomes available. If Box1 is unchecked, Box2 needs to be unchecked and disabled.

View 9 Replies View Related

Disable The X Button

Oct 22, 2009

I want to disable the X button on the top right hand corner in excel, but still allow the user with the option of closing the workbook via File and then scrolling down to EXIT. (Not close). I have disabled most of the functions in the Menu bar Close being one of them.

So only Exit is now enabled. When I click on the X it give me the following message as shown below advising me to go to the File menu to Exit, this bit I am happy with. However this is were my problem begins, when I go to File and Exit I get the same message as below and can not exit the application.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Cancel = True
MsgBox "Please select EXIT from the File Menu.", vbCritical, "Cannot Close"
End Sub

View 9 Replies View Related

Keyboard Disable

Apr 26, 2006

Does anyone know if there is anyway that we can command Excel to avoid accepting aNY KEYBOARD ENTRIES? what intend to do is restrict a certain region on a worksheet for Users not to be able to type anything but but there are commandbuttons on the header which use: Range("C65536").End(xlUp)(2, 1) = "Value for the last cell" code to get the input into the cell. The purpose is to make use of the cmdbuttons with mouse but the data notto be keyed in !!!

View 2 Replies View Related

Disable CMB_Change

Jun 21, 2006

I'm running a module macro which cause a CMB change (and an additional macro run) in one of the sheets.

Since this interrup the main macro run (and also waste unnecessary time) I would like to disable it.

View 9 Replies View Related

Disable Sorting

Jun 23, 2006

I would like to be able to disable a workbook from being able to be sorted, but at the same time allow fitering.

I have tried the following code, but have not got it to work correctly, does any one see what I need to add or remove to get this to work.

Option Explicit

Private Sub Workbook_NewSheet(ByVal Sh As Object)

With Application
. ScreenUpdating = False
.DisplayAlerts = False
ActiveSheet.Sort
MsgBox "You Can't sort sheets in this workbook!"
.DisplayAlerts = True
.ScreenUpdating = True
End With

End Sub

View 3 Replies View Related

Msgbox Disable X

Jan 30, 2007

i have this code to disable X in a MsgBox.......

MsgBox "Closing from X is Disabled", 20, "Action"

Is there another way that the MsgBox shows OK only botton without showing yes/no botton

View 4 Replies View Related

Disable Mouse

Jun 27, 2007

Is it possible prevent the use mouse (disable mouse) while a macro is running? I made a program which starts with animation of Shapes. If you click on shape while macro is running it stops animation.

View 7 Replies View Related

Disable A Macro With Apostrophe?

Nov 6, 2012

,I have the following Macro:

VB:
Private Sub Workbook_Open()Worksheets("Sheet1").ActivateRange("$B$1").SelectSheets("Sheet1").ScrollArea = "A1:z25"End Sub

Now I want to disable the above such that I am able to add an Apostrophe ( ' ) in front of every line of this Macro. Is there a shortcut for this or do I need to Manually add apostrophe in front of every line? And then after disabling if I need to re-enable it is there a shortcut to that too?

View 9 Replies View Related

Disable Copy + Paste

Nov 16, 2008

Is there anyway to turn off the ability to use the copy and paste commands in excel (versions 2003 and/or 2007)?

View 6 Replies View Related

How To Disable Cut / Copy And Paste Without Using VBA

May 22, 2013

I am trying to disable Cut,Copy and Paste without using VBA can it be done

View 3 Replies View Related

Disable All Cells Not In Use On A Sheet?

Oct 8, 2013

I have several cells in a column for the user to input data, and then a "submit" button. It is a very basic sheet to take user input and store it on another sheet and then manipulate(pivot) and display(graphs) on respective sheets.

Is it possible to remove gridlines and disable all cells on the input sheet except for those that take input, currently validation cells. The main reason is when the user hits tab or lets say enter it moves to the next field for data entry as opposed to through the two empty cells between that are there for spacing. Also to disable them from editing the text that poses the questions(labels): "

View 2 Replies View Related

Lock Or Disable Textbox?

Feb 28, 2014

Can we lock or disable text box editing in excel without protecting sheets?

View 1 Replies View Related

Disable Print For The Set Of Worksheets

May 11, 2014

Is there any chance to cancel print for set of worksheets? For example in workbook with Sheet1 to Sheet4, i wish to allow printing only for sheet4 and all other sheets to disable print.

View 6 Replies View Related

How To Disable Paste Into Certain Cells (using VBA)

Jun 3, 2014

How to Disable Paste into Certain Cells (using VBA)

(ctrl + v, right click paste & paste special, menu paste/paste special)

I want every type of paste possible to be disabled for those cells listed below. (the cells cant be locked, its ok for the user to "TYPE" in the cell just not paste.)

T15,V15,AI17,AJ17,AK17,AI18,AJ18,AK18,AI19,AJ19,AK19,AI20,AJ20,AK20,AI21,AJ21,AK21,AI22,AJ22,AK22
,AP17,AQ17,AP18,AQ18,AP19,AQ19,AP20,AQ20,AP21,AQ21,AP22,AQ22,AV17,AW17,AX17,AV18,AW18,AX18,AV19,AW19,
AX19,AV20,AW20,AX20,AV21,AW21,AX21,AV22,AW22,AX22,BC17
,BD17,BC18,BD18,BC19,BD19,BC20,BD20,BC21,BD21,BC22,BD22

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved