Free Microsoft Excel 2013 Quick Reference

Stop users overwriting cells

I have an issue where i have a workbook which numerous people use, but one user will enter data and another will then go in later and accidently enter data and drag down overtyping the previous users data. Is there a way to either warn the user they are overwritting data or stop them from doing it??

thanks in advance


Post your answer or comment

comments powered by Disqus
I have a worksheet that I have produced for other people to use and so do
not maintain tight control over its application after it leaves me.

I have a number of cells that have validation set to use only "Lists" and
that works fine. The problem is that if someone "Copies" something into one
of those cells, it overwrites the validation control and inserts whatever
the rebellious user wants to insert and the List no longer functions for
that cell.

What can I do to make the limitation tighter so they can't enter anything -
in any way - that is not on the list?

Mack Neff

Hi,

I am currently setting up protection on an excel sheet - I have a proceedure to open a specific worsheet and lock and unlock cells according to whether or not certain information exisit....blah blah

My question is, although I can lock cells and prevent users changing them using code I can't seem to stop them selecting cells in the first place - this is obviously achieveable from the 'Protection'>'protect workbook' menu but I want to do it from code since the sheet is unprotected and reprotected a number of times during a session....

Cheers,

J

Hi,
Help would be apprecitaed:
Within a given workbook i wish to stop users selecting more than one sheet
at once. (To make sure data is not overwritten in both sheets as these cells
are llinked and would be very hard to undo)
Thanks in advance,
Rob

Is it possible, to stop users from being able to click out of the formula bar and only allow "enter" to be pressed?

ie. cell A1 user entres a date.

I don't want the user to be able to click on another cell until enter has been pressed to complete the date value.

is this possible?

Hi
Could anyone tell me how to write VBA to stop user typing in a combobox but only choose from the dropdown list?
SO that to avoice any device I/O error.

Tks.

I am trying to make a address book with checklist and need to use several
tabs stops within a Cell, since there is not key to make tab stop as there is
on a typerwriter, How do I make a tab stop with a cell, or a template for
Excel?

How do I stop the input cell from jumping to the mouse cursor?

I cannot seem to get the "Alert Before Overwriting Cells" option to work.
Although I have the box checked I can overwrite non-empty cells without a
message. Can someone please help???


how do i do using macro for deleting all user defined cell format in a
specific workbook?

sub()
~~~~~~~
end sub

*** Sent via Developersdex http://www.developersdex.com ***

Hi,

is there a way to stop users from deleting rows? I need them to be able to
insert, change but not delete.

thx
wAyne_

I have this alert option checked, but I'm still allowed to overwrite cells
with data in them. I have Excel 2000, SR 9.0.3821 SR-1. Has anyone else had
this problem, and is there a solution? TIA

I am trying to make a address book with checklist and need to use several
tabs stops within a Cell, since there is not key to make tab stop as there is
on a typerwriter, How do I make a tab stop with a cell, or a template for
Excel?

I have a Workbook that has 2 worksheets, one called CALL QUERY and one called CALL LOG. On the CALL QUERY sheet, cell D9 is a user inputted cell with the cell validation set to list. The user uses the dripdown list to pick an office identifier (3-Letter Code).

On the CALL LOG sheet, I have info about individual calls. Column E has the 3-letter office identifier for each call.

I am trying to find a way to automatically auto filter the list on the CALL LOG sheet with the user input in cell D9 on the CALL QUERY sheet.

I was given the following code: (N2 is a cell on the CALL LOG sheet that equals the user inputted cell D9 on the CALL QUERY sheet)

Private Sub
Worksheet_SelectionChange(ByVal Target As Range)
   If Range("N2") = "" Then
      If ActiveSheet.FIlterMode Then
         ActiveSheet.ShowAllData
      End If
   Else
      Worksheets("Sheet1").Range("E:E).Autofilter _
         field:=1, _
         Criterial:=Range("N2").Value, _
         VisibleDropDown:=False
   End If
End Sub
I opened the VBA window using ALT-F11 and I double-clicked on the sheet that I wanted the auto filtering to apply to, and I entered this code. Once I closed the VBA window, I did multiple cahnges to the user inputted field and nothing happened on the auto filter sheet.

Is there something I am doing wrong? I was told this code works on the worksheet's SelectionChange event so whenever the user changes the input, it will autofilter the data. I am new to the SelectionChange event. Thanks for any help. - John

Is there a way I can make a macro which copies USER-selected cells to a new worksheet?

PS - My ultimate task is to do mail merge with these User-selected cells..

thx

Is there any way to stop a user from accidentally saving an Excel workbook with the same name as my file and thereby overwriting my file?

This user must have access to my workbook, which she uses every day to do her job.

Each day she runs an export and saves it as “Workbook 1”.
Then she opens my workbook “Workbook 2” which contains an enormous amount of code, opening & closing other workbooks, printing out various forms, then closing itself and all others.
Well, she accidentally saved her export as “Workbook 2” instead of “Workbook 1” and overwrote my workbook!

Hello again, it's been a while - but I have a new weird and wonderful problem:

Amongst the several sheets contained in my workbook, there is one called 'Inspection Report'. Users fill in whatever data is required in the other sheets, and once they get to this one, they are supposed to enter a number from 1 to 3 into Cell X1 (which is currently selected) before they select anything else. Unfortunately, I am currently unable to stop them from doing what they should not be doing.

So, I would like to have a notification of some sort pop up into their face if they click or move the selection anywhere else while Cell X1 is still empty. Something like a validation would be nice.

Thanks for anything you guys might come up with - Juda

Hello all,

I am in need of protecting a few cells from user overwriting that contain VBA scripting. Basically, I have a set of checkboxes that, when clicked, will enter a value into an adjacent cell. The problem: A user can override the value and enter whatever they want. I need to prevent this from happening whereas, the cell contains the value associated with the checkbox, or a 0 value with no possibility of user intervention.

I have tried to protect the worksheet, lock the cell, etc however, this does not seem to do the trick. As such, I am wondering if something needs to be added to the macro for that box that prevents anything being entered other than an assigned value or 0

Any help you can provide would be greatly appreciated,

Snakeyes

I have a template excel file that is loaded with macros. When the file is opened, a userform opens and the user makes several different selections. Based on the selections, the excel file is populated with information and pictures. I want to make sure that the user does not accidentally save over the template file when he/she is finished with the document.

My original solution was: when the user opened the excel file, it would automatically save under a different name in a temporary location. This would stop the user from being able to overwrite the source file. The users were not happy because the excel file is very large and takes a long time to save.

Any suggestions on another method to stop the user from overwriting the source file? using code? or another method?

Thanks.

I created an account form in Excel that is filled in by others when they want
a new account to be set up.
There is one cell in particular that they have to fill in, but very often do
not. I was hoping there was something in Excel that would stop them from
saving the spreadsheet unless this cell is filled in.
Any help would be appreciated.

Thanks so much.

Hi,

I have an xls that I have set up with validation lists in some cells. I need
to stop users from ignoring the options in the list and just typing whatever
the want in the cell.

Anyone help? I am sure it is easy, but can't see how to do it.

Thnx,

Smf

Using Excel 2000

I have created a report sheet that must be filled in on a monthly
basis. There is one column per month. One of the cell is a comment box.
As columns will be hidden/displayed at each month change and for
presentation purposes, I have added a textbox control on the sheet (
not moved nor resized ).

The linked cell reference of the textbox comes from a dynamic name that
returns the reference of comment cell of the reporting month.

I have created the following local named range
comment = OFFSET('Sheet1'!$A$1,0,'Sheet1'!mth_offset)

In the linked cell property of the text box
linked cell = 'Sheet1'!comment

mth_offset is another local named range linked to a spinbutton. As I
spin up and down, the text box correctly displays the content of the
cells in row 1, as you would expect from the 'comment' formula.

The strange thing is that if as soon as I edit the content of the text
box, the dynamic link stops working, i.e. the textbox no longer updates
its content as I spin up/.down.

I have found a workaround: in the spin button change event procedure, I
add the code: "TextBox1.LinkedCell = TextBox1.LinkedCell". With that
the text box updates correctly.

Has anyone faced this problem ?
Is there a workaround that does not rely on code ? - getting users to
activate macros can be a pain.

Thanks for your help.
Regards

Hi All,

I'm trying to stop users from using paste and overwriting cell validation
and formatting. However, I can't disable the paste function due to
requirements. I'm trying to find out if, and how, I can capture the paste
event so I can replace it with paste special in VBA.

Also, the users could be pasting from a number of different programs. How
do I identify if Paste Special/Values or PasteSpecial/Text is needed for each
instance, or do I not have to differentiate.

Thanks in advance for you assistance,

DonC

Sorry to be back so soon.

I am looking to stop the user saving the sheet if certain fields are not complete.

The sheet has headers on line 4 and runs from A4 to M500

In the M cells there is a counta function to check the records are complete.

M4:M500 shows either

Complete - All records comlpete
Incomplete - something missing
Blank - nothing entered yet.

What i want to do is, if one of the cells in M shows incomplete not allow them to save the sheet until its complete.

I tried the folowing (was my attempt to modify another post)

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If Range("M5:M500") = ("Incomplete") Then
    Cancel = True
    msg = MsgBox("Please complete record to continue ", vbOKOnly)
Else
    If Range("M5:M500") = ("Complete") Or Range("M5:M500") = ("Blank") Then
    Cancel = False
    End If
End If
End Sub
Any help appreciated.

Thanks again.

I have a user defined function that performs a calculation on a range of
cells. If a row or column outside of the range used by the function is
deleted the function automatically recalculates. How do I stop the function
from recalculating? I have tried using the Application.Volatile (False)
statement but the function still will recalculate whenever a row or column is
deleted. I only want the function to recalculate when the one of the cells
it is referencing changes.


No luck finding an answer? You could always try Google.