VBA Code Prevent A User From Changing Cell Contents

May 26, 2009

Is it possible to write VBA code that will prevent a user from changing a cell's contents, depending on the cell's font color?

More specifically, I have a column of text in range B15:B64. Some of the cells will have a black font, others will be blue. Is it possible to lock the black font cells only, leaving the blue cells unlocked for users to change?

View 9 Replies


ADVERTISEMENT

Rewrite Line Of Code To Prevent Changing Number Format

Aug 5, 2007

The following block of code is evaluating the term in the array, if it exists and the value in col. M is a non integer, (which what I am testing for are fractions) then format the cell to a fraction number format.
That is ok, except it is changing the numberformat on text terms. I want it only to change the numberformat on numeric values.
I added the function Isnumeric to this line:

Isnumeric(cells(i, "M").value) Int(cells(i, "M").value)
which I thought would only evaluate numeric cells only, but this was unsuccessful.

Original Code: Unedited.

For i = 4 To LRowf
For Each Item In Array("HAT", "FTWR", "BOOT", "BOOTG", "BOOTY", "HWRISR", "HWBLTS")
On Error Resume Next
If (Cells(i, "F").Value = Item Or Cells(i, "G").Value = Item) And _
Cells(i, "M").Value Int(Cells(i, "M").Value) Then
Cells(i, "M").NumberFormat = "# ?/?"
On Error GoTo 0
Exit For
'End If
End If

Next Item
Next i

View 9 Replies View Related

Interupt Code For User Input: Move The Contents Of One Row To Another

Nov 2, 2008

Need way to interupt my code whilst the user selects a row. ie. if I wanted to move the contents of one row to another I would want to say: "select source row" (and want the user to click on any cell in that row) I could then put the rownumber in a variable with

View 2 Replies View Related

Prevent Changing Value In Cell

Jun 15, 2009

I want to allow users to select a cell in a column, but not allow them to change the contents of the cell. Also, don't want to allow them to select all the cells on the sheet.

I'm guessing that I need to use the Change event, and restore the contents back to what they were before the change.

Is there a better way to do this?

View 10 Replies View Related

Macro To Prevent Changing The Value Within A Cell

Jan 11, 2007

I would like to write a macro that performs the following function:

I have a value in cell A1

In cell A2, I have the formula "=A1"

I want to make it so that cell A2 can never be directly changed; the only way to change the value in A2 is by changing A1. A2 should always equal A1.

I want a message box to pop up and alert the user whenever they attempt to modify A2, instructing them that if they wish to change A2, they have to change A1.

I had written this macro, and it kinda does the trick:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)


If Range("a2") Range("a1") Then
MsgBox "text goes here"
Range("a2").Value = Range("a1")
End If

End Sub

When I attempt to change cell A2, I get the MsgBox popup and it works how I want it to. However, when I alter cell A1, I get the same popup before cell A2 updates (to be precise, the popup occurs after I click another cell). I would like this to be immediate and without a MsgBox.

View 9 Replies View Related

Prevent Cell Referencing Within Formula From Changing

Jul 30, 2009

I've got an issue where Formulas Referencing Cells on another sheet are getting changed automatically to refer the the moved cell data. The formulas on a sheet (2) refer to specific cells on sheet (1) within my workbook. I update Sheet(1) data with a macro. When sheet (1) updates, the present data gets shifted to the right.

In other words, I need to prevent the formulas from changing to reference the moved data values and keep referencing the initial cell with the new values. xample of change that occurs with update:

Correct:

View 3 Replies View Related

Prevent The User From Saving The Data Input From The User Form If Any Of Those Three Fields Is Left Blank

Feb 9, 2010

I have a user form that has a combo box "City" two text boxes one called "Flight" and the other "Date". What I'm trying to do is to prevent the user from saving the data input from the user form if any of those three fields is left blank. The code that I have so far checks all of the required fields, if any are left blank a message notifys which field(s) is left blank and return the focus back to that field. But the rest of code also fires.

What I really need is either to stop the code if any fields are blank and return the focus back to the blank field, the user completes the field(s) and clicks the save again, or better yet, pause the code until all the required fields are completed and then complete the save. (There is actually another 200+ lines of code in this sub, but I deleted it to keep the post a little shorter.)

View 2 Replies View Related

Prevent User From Accessing The Excel File When It Is In Use By Another User

Jul 22, 2013

An Excel file named CLEARING is availble in thr central server system. it will be accessed by 5 or 6 staffs from some other systems what i need is when one person accesses/edits this file the others should not even able to open in the read only file ,when the file is saved and closed ,then only the next person should access it

View 1 Replies View Related

Allow User To Edit Cell Contents Within Macro

Feb 4, 2014

I need to query a column and find the cells that have been incorrectly coded. For example the coding standard is #<reference number>/. Sometimes people forget the "/". It is not as easy as using search and replace because the string may have more contents than the above.

For example it may show: ATM/45678/#789876 dt. 1-2-14/ or it something like CHQ/44384/#78987600.

There are many permutations. So I need to find the cells that don't have a "/" following the #<reference number>, then allow a user to enter the slash. At this stage, I don't need code to find cells containing errors, I think I can figure that out. I do need to know how to allow the user to edit the cell as part of the macro. Once the user corrects the error, the macro should then continue. Is this possible?

View 2 Replies View Related

Excel 2010 :: Add Comment To Cell Without Changing Cell's Contents?

Mar 13, 2012

How, via VBA, would I add a comment from a userform (text box name: txtReason) into a cell that may already have a comment in it? I would like to keep the comment that is in the cell and then have the program add a "/" and the next comment from txtReason..

(using excel 2010)

View 2 Replies View Related

Changing Auto Filter Based On User Inputted Cell

Apr 7, 2007

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 (N2 is a cell on the CALL LOG sheet that equals the user inputted cell D9 on the CALL QUERY sheet)

View 14 Replies View Related

Cracked Vb Code: Prevent People From Accesing The Code I Protected The Code Blocking It From Visualization

Feb 8, 2007

I've developed a little software using Excel Macros & VB. To prevent people from accesing the code I protected the code blocking it from visualization. It seems not enough as an acquaintance of a friend cracked it in 25 minutes. Or so he says. So I'd like to know if there is a better way to protect the font code.

View 8 Replies View Related

Prevent Changing Of Sheet Name

Mar 16, 2007

I have been searching for a way to prevent a user from changing a worksheet name. I ran into some code from the post below (see link) but I find that the code lets the user change the name and save the changed name of a sheet; only when the user selects a different sheet does it change the name back to the set name (sname) from the code.

I'm sure there must be a way to make this happen without deactivating the sheet, but I can't figure it out. Is there some other sort of event that could cause this to happen.

View 9 Replies View Related

How To Prevent User Error

Jan 6, 2009

I have a simple spreadsheet which requires a date (mm/dd/yy) in Column A. Columns B-F use formulas to break this date down into the month, quarter, year, etc. Users frequently need to add new rows at the top of the sheet just below the column headers.

To be sure the formulas in Columns B-F are included when adding new rows, I put together a simple macro which copies the top ten rows, inserts them at the top of the sheet, and then clears existing data from the new rows (keeping formulas intact).

I put a button on the sheet to run this macro. However, sometimes users insert new rows without using the macro. This results in no data in Columns B-F for these new rows because the formulas weren’t copied.

How can I prevent this from happening? If I protect the sheet and prevent users from inserting new rows, the macro won’t run. Is there something I can add to the macro that will allow it to run properly while the sheet is protected? Below is the macro code.

Private Sub CommandButton1_Click()
Rows("3:12").Select
Selection.Copy
Rows("3:3").Select
Selection.Insert Shift:=xlDown
Range("A3:A12").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("G3:K12").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A12").Select
End Sub

View 9 Replies View Related

Prevent User From Typing On Worksheet?

Feb 11, 2009

I have tables on a spreadsheet and userforms created. Is there anyway to prevent users from just typing on the worksheet so they will have to use the userform?

View 4 Replies View Related

Prevent A User From Opening Another Workbook

Feb 12, 2009

How can I prevent a user from opening another workbook in an Excel session "from the outside"?

I'm fairly new to VB. I'm developing (in VB 2003) a simple but SECURE Excel environment which will allow a user to update a hidden Master_Records workbook. I need to keep the user's Excel session secure...for example, I've disabled all Excel Toolbars and Command Buttons, effectively preventing the user from doing anything except filling in some cell values and clicking on a few custom buttons in the worksheet. But how can I prevent the user from opening another workbook into the active session from his desktop and introducing some malicious code into the session via that route?

One respondent in another forum (the only one, in fact) suggested that I look into "instantiating workbook level events" so that I can detect when other workbooks are open. I'm not sure what that means, is there someone here who could give me some guidance into that solution?

View 13 Replies View Related

Prevent The User From Scrolling In A Sheet?

May 14, 2007

I have an Excel sheet that displays 50 rows and 20 columns when it is opened the first time. These rows and columns are the only ones that I want the user to be able to see. The user shouldn't be able to navigate the sheet by using the scroll bars or the wheel on the mouse. I have of course already removed the scroll bars but I don't know how to handle the scroll wheel problem.

View 2 Replies View Related

Code To Clear Cell Contents For Rows?

Sep 12, 2012

Small piece of code to clear the contents of

Rows 33 thru 100 , then
Rows 142 thru 209 , then
Rows 251 thru 318 , then upto
Rows 43524 thru 43591 then finish

View 5 Replies View Related

Prevent Other Cells From Changing Their Dates And Time

Mar 30, 2009

When I enter this : =now(). it gives the local date and time in my PC in that exact cell. assume it is in (A2). when I enter in Any cell in the spreedsheet the sam formula =now(). then the previous formula (A2) is changed to the new date and time.

View 5 Replies View Related

Prevent Formula From Changing On Copy / Paste

Jun 17, 2014

I have a vlookup which checks another sheet and imports data. The formula is as such:

=IF(ISNA(VLOOKUP(B17,Data!A$1:$C$40,3,FALSE)),"",T(VLOOKUP(B17,Data!A$1:$C$40,3,FALSE))) .

The problem I have is when I paste and amend data on sheet Data! it changes the formula to

=IF(ISNA(VLOOKUP(B17,Data!A$1:$B$40,3,FALSE)),"",T(VLOOKUP(B17,Data!A$1:$B$40,3,FALSE))) .

How do I prevent it from doing this?

View 4 Replies View Related

Prevent Formula Being Deleted When Visible To User?

Apr 20, 2013

I am using following code to effect a change which works fine:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Closed Flts").Unprotect "abcd"
Sheets("Faults").Unprotect "abcd"

[Code].....

But when the sheet is unprotected to effect this change the formula in cells ( row I and L) are visible to user and can delete them. Or they can even delete the cell itself or modify formula.

The formule in Row I is
Code:
=IF(A12="","",IF(G12=1,A12,IF(G12=2,A12,IF(G12=3,A12+1,IF(G12=4,A12+5,IF(G12=5,A12+28,"priority?"))))))
And in Row L is
Code:
=IF(A13="","",IF(I13

View 1 Replies View Related

Delete The Contents Of Every Cell Without Deleting Any Code From My Module

Apr 16, 2009

I want to delete the contents of every cell without deleting any code from my module. The reason I ask is, I'm reading from a text file into excel.

I want to run my code to read text into excel one time through. Then i want to delete all the text, make some alterations to my code, and run the code again. That way each time the worksheet is fresh and clear before i run the code.

View 4 Replies View Related

VBA Code To Copy Contents Of Cell A1 To End Of All Rows In Worksheet?

Nov 17, 2011

i have a workbook with over 600 worksheets and any vba code to do the following.... (each worksheet contains different number of lines)

At the moment the data is in columns a to d

What i need is the data currently in cell a1 (in each worksheet) to appear beside every line in that worksheet

Then i need to take all this data and put it onto one single worksheet .

View 2 Replies View Related

Prevent Changing Data In Column But Allow Row Insertions / Deletions

Jul 31, 2012

I am trying to create a worksheet_change event that will prevent a user from changing or deleting data in the totals column of my worksheet yet allow them to insert rows and delete rows on the worksheet. I have a few issues I can't get resolved. First, if I delete a row and then click the undo message, I get my error message about trying to change the totals. Second, I cannot copy and insert copied rows. I can only insert blank rows. Here's my code and data.

Private Sub Worksheet_Change(ByVal Target As Range)
Static lngRow As Long
Dim iStrtRow As Integer, iEndRow As Integer, rRowMrkr As Range

[Code]....

Defined range names: "Header" in cell A1 and "Totals" in cell A12

View 1 Replies View Related

Prevent Formula References Changing When Add/Delete Rows

Jul 11, 2006

On my excell I have a lot of data on sheet 2 that i automatically pull form a database.

I have this fomula in a cell on sheet 1.

= SUMPRODUCT(--( 'Sheet2'!P5:P50000=1),--('Sheet2'!T5:T50000>TIMEVALUE("01:00")))

The problem is that when the data on sheet 2 gets updated and more lines added, the computer changes my formula for the cell on sheet 1 as well. the new formula will change to

=SUMPRODUCT(--( 'Sheet2'!P5:P50126=1),--('Sheet2'!T5:T50000>TIMEVALUE("01:00")))

when there is 126 lines of new data addes in on Sheet2. Id only 50 lines were added in on sheet 2 then the Formula would change to

=SUMPRODUCT(--( 'Sheet2'!P5:P50050=1),--('Sheet2'!T5:T50000>TIMEVALUE("01:00")))

View 9 Replies View Related

Prevent Formula References Changing When Deleting Rows

Apr 21, 2008

What I have is basically a form on the main sheet. Here other users can add and/or delete information in a form. I refer to the information in another sheet with formulas.

Now everything was great when using protections/ validations, to the where they fill in the information, but the problem occur when they are allowed to delete and remove rows.

This is something that I want to allow them, even though I do realise that this is something that many advice against it.

To begin with I found the post about changing relative references to absolute references (ConvertFormula). Still the formulas changes when rows are added or deleted and I would have to redo all formulas and run the macro again. I then found posts advicing on the use of the INDIRECT forumla. This formula was exactly what I needed as the formulas would not alter when rows were added/deleted. Now the problem is a combination of the use of INDIRECT and the the ConvertFormula. Now if I write a formula, for example:

=INDIRECT("'Sheet1'A1")

Then when I try to expand the formula over to the rest of the cells, then the references stay pointing at A1, when I want them to continue pointing at A2, A3, etc...

Now what I am looking for is some way to tell all my formulas, after expanding the original formula, that they should all have the INDIRECT function. If it was possible it would be great if you could implement it in the same way as with the absolute reference, for example:

For i = 1 To MyRange.Areas.Count
MyRange.Areas(i) = Application.ConvertFormula(MyRange.Areas(i).Formula, xlA1, xlA1, xlIndirect)
Next i

Now the above code sample does not work of course, as there should be xlAbsolute instead of xlIndirect, but I hope you understand my intention. Do anyone have the answer to my problem? Is there an easy way to implement the INDIRECT function into a big amount of formulas?

View 4 Replies View Related

How To Prevent User From Clicking On Days In Calendar Control 8.0

Jun 22, 2013

Is there a way to prevent a user from clicking on the days in the Calendar Control 8.0? I am trying to place 12 calendar controls on a worksheet for reference only. I am using a linked cell to reflect what date the Calendar is to display.

View 2 Replies View Related

Prevent User To Enter Duplicate Using Data Validation?

Dec 27, 2013

I want to prevent user from entering duplicate text or numbers in a cell using the Data Validation.

View 9 Replies View Related

Check User Input To Prevent Run-Time Error

Dec 28, 2007

what I want. It relies on an input box asking the user to enter a part code, which when found creates a duplicate copy. However, if the user enters a part that doesn't exist I get a run-time error. Can anybody point me in the right direction so when a user enters an incorrect part they get a message telling them it doesn't exist.

Private Sub SortTest_Click()
Dim s As String
Dim r As Excel. Range

Range("A2").Activate
s = InputBox("Enter the number you wish to find")
If StrPtr(s) = 0 Then
MsgBox "You must enter an existing part number!"
Else
Set r = Cells.Find(What:=s, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

Range(Cells(r.Row, "A"), Cells(r.Row, "AH")).Copy
Sheets("APL").Cells(r.Row, "A").Insert Shift:=xlDown


Application.CutCopyMode = False
Application.Goto Sheets("APL").Cells(r.Row, "H")
Selection.Offset(-1, -5).ClearContents
Selection.Offset(-1, 0).Select
End If

End Sub

View 7 Replies View Related

Prevent User Seeing Senstive Information In Hidden Rows/Columns

Aug 19, 2008

I have a feeling the answer to this will be 'no' based on the searching I've done, but is it possible to allow formatting of rows/columns on a protected worksheet, yet prevent the user from unhiding some rows/columns I don't want them to see? I've allowed them to format rows/columns so they can "size" them to fit the text they enter, but that has opened up the ability for them to unhide columns. Using Shrink To Fit would be cumbersome due to the number of cells involved.

View 5 Replies View Related







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