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
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.)
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
I have a worksheet which periodically is cleared down to allow new data to be added. i.e. if 40 columns are in use 20 might be deleted. There is a calculation that depends on a named range which always changes when these columns are deleted. s there a way to remind the user to expand the named after any columns are deleted? Columns are deleted from Column H.
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
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?
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?
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.
I have a text field at the bottom of a user form that remains hidden (i.e. visible = false) until the user clicks the "Ok" button. At that point, I want the text field to appear as the macro is running (it's a large macro, so the text field just says "processing, please wait...").
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?
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.
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)
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.
I have a spreadsheet that has a formula placed from cells B21 through to AT21. This formula is present in the sheet 95% of the time, but occassionally the user has to enter his/her own value in one of the cells.
How can I check or the user be reminded that he/she has deleted a formula and replaced it with a user defined value. ie a message box(only as a warning) to be displayed just before the workbook is printed. The user should be able to acknowledge the message displayed and carry on.
I'm not sure if this is impossible (surely not) or if I'm just Googling the wrong terms (most likely). I have a sheet that we use to record staff shifts and when they take their lunch breaks. By default most of our employees have a 1 hour lunch and they take it 4 hours after they start. So far so good, I've set up a formula to work out what time their lunch would start and end based on this.
N237: =IF(AND(L237>0,L237<>"NWD"),SUM(L237+(1/6)),0) - Lunch start 4 hours after start time in L237 (NWD = Non working day) M237: =IF(N237>0,SUM(N237+(1/24)),0) - Lunch ends 1 hour after lunch start time in N237
However, not everybody follows the default - some people take lunch later, or don't take as long due to working shorter hours.
What I'd like to do is allow users of the sheet to overtype the times generated by the formulas when applicable, but if they delete their own custom value for lunch breaks I would like the formulas to re-appear and thereby return lunch times to the default values.
I could do this through VBA with a button to look for blank cells, but I would prefer it to happen automatically. Is this possible and if so what code would I need to make the magic happen?
I should maybe have mentioned that this sheet has space for 250 names and an area for each day of the week, so these formulas would appear in 1,250 different places
I would like to use the code below such that column "A" contains unit list pricing, Column "B" calculates the discount from the net pricing in Column "C." For example, A1 = 100, B1 = 1- (C1/A1), and C1 by default is equal to A1 but if the user enters in a net rate, say 85 in C1, it retains the 85 in C1 but if then later deleted, the C1 automatically reverts back to =A1. The code below works great for C1 but for the life of me, I cannot figure out how to modify the code and have it work correctly for a range of cells in Column "C" or all of Column "C."
Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Address(False, False) = "C1" Then If IsEmpty(.Value) Then Application.EnableEvents = False .Formula = "=A1" Application.EnableEvents = True End If End If End With End Sub
I have written two VBA programs around the same time. Both run on open and pull external data and create graphs. My problem is that I want the end user to be able to run the report multiple times by choosing the name of the macro from the Excel macro menu (i.e. Tools>Macro>Macros) but only one of the workbook macros shows up on the menu. why the other macro is not visible on this menu???
We have created a macro that basically looks for rows that contain an "H" and hides the row if it does.
Users can add new rows throughtout the year to this spreadsheet. and based on certain criteria, an H or U will be placed in a hidden column which the macro looks at and hides any row it finds an H.
The user has to click on the button that has the macro assigned to it once they have finished working on the spreadsheet.
The problem we're finding is that for users who insert/delete rows, once they click the button it takes up to 15 seconds to run through macro (which is ok). However, users who haven't added or deleted any rows and who click the button, they have to wait upto 5 minutes (which isn't ok) for the macro ro run.
We can't figure out why the macro takes longer to run when no changes have been made?
Need to correct code to resize all visible rows on a sheet based only on the text in the visible columns. I have tried the below code but when it resizes it is using the largest amount of text in the rows including that in the hidden columns.
What formula can I use to accomplish the following, if it can be done.
If B9 is 100% all the rest of the % cell inputs would be 0%
If B9 is less 100% ,lets say 50% all the rest of the % cell inputs would be less than or equal to 50% until any sum of those cells equal to 100% in which case if I enter 50% on B9 and 25% on D9 and 25% on F9 their total sum would be 100%.
If I try to enter a % on H9 it should not allow me to do it because then the total sum on O9 would be more the the original amount on A9
The attached file contains hypothetical data on a basketball player's success from the free throw line. It lists the number of free throws attempted and the number made and then divides the number made by the number attempted to calculate the free throw percentage. However, I keep getting a # DIV/0! error in the games free throws weren't taken. How do I write the code so that when the error occurs, a message is displayed to the effect that no percentage can be reported because no free throws were attempted by checking the cell in column D? How do I use the IsError function to figure this out?
I'm creating a maths workbook in Excel. I want the user to workout the answer in his/her head and enter the answer into Excel. The only problem is, of course, that there is nothing stopping a user from simply typing a formula to obtain the answer! I have tried all data validation, apart from Custom which I can't figure out. Is there a way to prevent a user entering a formula on a worksheet?
i have made a document for other users to use. how can i limit the paste function to ONLY ALLOW PASTE SPECIAL-- i wish to aviod damage to formatting and formula etc
I have a column of cells (say N7:N149) for which I would like to count the number of times text is visible, as some are blank. Normally I would go =COUNTA(N7:N149), but in this instance the cells are only blank because I have related them to adjacent cells and nominated "" if those adjacent cells are blank, therefore when I use my =COUNTA(N7:N149) formula it gives me a total of 143 (149-7).
I want my macro to transform the cells whom values are visible into pure value no formula "behind them". I have simple multiplication formulas in those cells. If The result of a multiplication is 0 and I format that cells to not show me that 0 with "0.000;-0.000;;@" the macro still deletes the formula from that cell.
I have a spreadsheet that includes a column with location names and a column with location numbers. What I need to do is count the visible unique numbers and names (seperately) in a certain column when using a filter. I have found the formula to count the unique values and a formula to count the visible cells, but not a formula that does both.
