Code To Stop A User Adding Or Deleting Sheets
Aug 31, 2007
I need to stop users from adding and deleting sheets in a workbook
My idea was obviously to disable the command bars to add or delete sheets in open event and then put back in before close.
But then i thought...whats stopping them from right clicking the sheet tab and inserting a sheet, can i remove that menu to...?
I also wasnt sure if there were short cut keys to add or delete sheets.?
Some of my users use excel alot so i want to account for an tips they know that i might not.
View 9 Replies
ADVERTISEMENT
Sep 12, 2007
Is there a way (via Menues and or VBA) to cancel the pop-up of the Alert Window when the user deletes a sheet(s) ?
(I mean sheets that contain Data)
View 9 Replies
View Related
Jul 21, 2008
I have a spreadsheet with 20 or so columns but that number can change at any time. I need my code to by dynamic so if a column is deleted (or added) my code will still work.
Each column has a name in row 2 and I'm trying to use that in order to make everything dynamic. For example, my code counts the number of blue cells in colums D4:G68, but now if someone adds a column before column D, everything gets messed up and it will still count the cells in D4:G68 but I want to use E4:H64 now. Does that make sense? I have made code that searches the column names and returns the column number to correspond to the name. How would I change this?
Range ("C8") = "= CountBlue(E4:G68)"
the code doesn't look exactly like this, I believe it is actually in R1C1 format
View 9 Replies
View Related
Apr 4, 2014
I have a few excel files in a common shared folder. I want to allow other users to access this folder to make changes in the worksheet but should never allow them to delete the file. How can i do this?
View 5 Replies
View Related
Jul 23, 2014
I am looking at not allowing users to select the header and footer info. I don't want them to delete it. I have tried to protect the sheet and workbook but not working.
View 1 Replies
View Related
Sep 12, 2006
I'm protecting a spreadsheet with Excel 2000. For that i used the Excel functionality : Data -> " Validation"
Unfortunately, I can not protect the all sheet or workbook (Requirement). So even if the cells are protected by "Validation", any user can delete the cells using the key DELETE or BACKSPACE on the keyboard !
VBA Macro (which could be activate at each change on the sheet for example...) ? or if it's possible to avoid cells deleting with the "Validation" in the Excel Data menu ?
If it's a macro, it would do :
1 ) see if a user is pushing DELETE or BACKSPACE on the keyboard
2 ) see if the cell contains formulas
3 ) if yes, make a box appears to say that is not possible to delete this cell
4 ) return on the Excel sheet without changes
View 9 Replies
View Related
Sep 18, 2008
I want to stop the user selecting an entire row by clicking on the row header.
How can I do this?
View 9 Replies
View Related
Jan 19, 2007
Is there a general way to disable a user from changing row size or adding new rows.
View 7 Replies
View Related
May 11, 2007
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.
View 3 Replies
View Related
Jun 2, 2009
Adding msgbox to stop error
i have came up with its simple code
View 5 Replies
View Related
Apr 3, 2009
I have attached a sheet that I'm having an issue with. Columns D and E on the Dim Calculator sheet have a data validation formula in the cell. It works in the sense that it pops up if they are outside the parameters that I want them to hit. However, if the user clicks the Cancel button they can just keep going on even though the entry in the cell is aganist the validation.
Example: Columns D and E cannot be larger than Column C. Currently now if Columns D or E are larger than Column C the validation error pops up. But I can just click on cancel and keep going. That is what I want to prevent.
Is there a way to grey out the cancel button?
Should I do something else within the validation itself?
View 3 Replies
View Related
Oct 3, 2008
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.
on another method to stop the user from overwriting the source file? using code? or another method?
View 9 Replies
View Related
Feb 11, 2013
I am working on a worksheet where users are asked by a userform combo box to select a product. When they select the product a picture of that product appears in the top left corner of the worksheet.
I have managed to get the picture to appear however delete any pictures that were in that cell before pasting a new one, cause when changes the product a picture is pasted over on top of the previous one however if the pictures are not the same size you may see the previous under the current.
delete any previous pictures. Here is the code I have so far.
Sub GetPicture()
Set Rng = Sheets("Products").Range("A2", Sheets("Products").Range("A2").End(xlDown)) '.Resize(, 10))
[Code].....
View 3 Replies
View Related
Nov 8, 2006
In a userform i have created an textbox. The user types some text in it and after clicking an OK-Button this text must be copied to a cell To allow multiple lines (enter = new line in textbox) i have changed the textbox property EnterKeyBehavior to True. The problem is that after copying this textbox1.text to a cell in see square blocks in the cell.
line1[]
line2
instead of
line1
line2
I use the following code to copy the text into a cell:
Private Sub CommandButtonOK_Click()
Dim TextboxText As String
TextboxText = TextBox1.Text
ActiveCell.Value = TextboxText
Unload Me
End Sub
how to avoid this [] (should be like alt-enter in a cell)
View 6 Replies
View Related
Jul 9, 2013
I have a spreadsheet with multiple similar entries I would like to add up. What would work well for me is to be able to input a column like a and a target column like e.
So in this case it would search throughout column a and wherever it finds a duplicate entry add column e and then delete one of the rows. I could then run it again under different column criteria if needed.
So if I had the below sheet:
I could search by column a and add up column h to read like this:
View 1 Replies
View Related
Oct 24, 2006
I am trying to create two macros. The first macro will add a button, and the second macro will delete the button. I am noticing that when I add the button in the first macro, Excel is automatically naming the button (i.e. Button 26), and then when I try to delete the button with the second macro, this button name is not recognized. How can I name or set the button as a variable so that I can successfully run these two macros?
View 6 Replies
View Related
Aug 9, 2007
I have been running a simulation for about 18 hours now and just received:
Run-time error '1004':
Method 'Add' of object ' Sheets' failed
I have been creating new sheets, importing data, pulling some values from the data then deleting the respective sheet. I am using:
ActiveWorkbook.Sheets.Add after:=Sheets(Sheets.Count)
The sheet is actually being added to the workbook, seemingly before the error. I resume the code, and a new sheet is placed in the workbook and it errors again. The Debugger stops and highlights on the code above.The sheet count number was 10895 at the error, just as an indicator of how many times the simulation has performed successfully. I am hoping this is something I can fix without having to start over...
View 9 Replies
View Related
Nov 9, 2008
I am using several formulas to conditionally format and fill in rows from column A to column BS. These are the formulas and application areas:
=$BS4<>"" Applied to =$A$3:BS$350
=$G3<>"" Applied to =$A$3:BS$350
=OR($J3="Regional Manager",$J3="Assistant Manager",$J3="Manager") Applied to =$A$3:BS$350
=OR($J3="Recruiting Manager",$J3="Owner",$J3="District Manager",$J3="Office Manager") Applied to =$A$3:BS$350
If I do not add or delete any rows, everything works just fine. The problem I am running into is that this list is constantly updated and rows are being added and deleted. When I do this, it duplicates my rules and changes my applied to ranges. Here are some examples of the changes:
=$N$3:$BS$3
=$A$3:$M$63
=$N$4:$BS$64
This creates much excess work, when I have to go back and change everything back to the way it should be. how I can either modify my formulas or application areas to correct this situation?
View 4 Replies
View Related
Jul 21, 2014
I currently have this formula to deal with this:
=SUM(J5:INDEX(J:J,ROW()-1))
However, whenever I delete the top row...I get a reference error and have to re-adjust...
View 7 Replies
View Related
Mar 26, 2014
I have a Productivity Report that contains very basic formulas that provide totals for 4 columns (B6:E6) and an average for one column (F). I have included two command buttons, one to add a new row and the other to delete a row.
I need to be able to add or delete rows depending on how many employees' productivity I will be tracking on any given week; each row represents a separate employee. I need the following functionality out of my form:
1) formula in column F needs to copy and paste with each new line
2) when a new line is copied and pasted I need the contents to be cleared
3) I need the user to be blocked from deleting the first row (3 on this form) in the table
The code I'm using for my "Add" button is:
[Code].....
The code I'm using for my "Delete" button is:
[Code] .....
The buttons add and delete rows as I'd like them to but content is not clearing, with each row added the contents provide a sub total. I've tried various lines of code (some more complicated and some less) before I recorded my own macro (see above).
Attached File : Productivity Report (HH).xlsm
View 8 Replies
View Related
Jul 21, 2014
I currently have this formula to deal with this:
=SUM(J5:INDEX(J:J,ROW()-1))
However, whenever I delete the top row...I get a reference error and have to re-adjust...
View 9 Replies
View Related
May 21, 2006
ColumnA in my data base contains dates. How can I delete the entire row if the date is before today's date and add a new date at the end of the range to replace the deleted row.
View 9 Replies
View Related
Mar 4, 2014
I'm trying to get a macro to work that will delete a number of worksheets that are located in between a sheet called 'start' and one called 'end'
I have got this code - which deletes both 'start' & 'end' too, i've been struggling to get it to work so it doesn't
Code:
Sub Macro_delete_worksheets()
Dim i As Long
Application.DisplayAlerts = False
[Code]...
View 7 Replies
View Related
Jun 16, 2008
Will excel allow text to be permanently be positioned in a specific cell even if rows or columns are added?
View 6 Replies
View Related
Nov 13, 2009
Stop sheets switching. I have a simple autofill macro
View 4 Replies
View Related
Jun 21, 2006
I have a macro that will open all the workbooks in a specified folder, regardless of the actual number of workbooks...All worked fine, until I tried to move a particular worksheet, from each wbk, into another specified wbk....The files open correctly, and copy / move the sheets correctly to the assigned wbk...but...it won't stop inserting sheets..it starts over and continues the process....?????
Option Explicit
Sub rbaOpenAll()
Dim x As Integer
Dim WB As String
Dim wbk As Workbook
For x = 1 To 100
WB = "G:ClaimsXtenTESTRBARBA " & x & ".xls"
On Error Resume Next
Set wbk = Workbooks.Open(Filename:=WB)
Worksheets("Current Rules - 1").Activate..............
View 5 Replies
View Related
Mar 18, 2014
I currently have the following Macro for one of my many checkboxes in 2007 Excel:
[Code] .....
It works perfectly until additional rows are added/deleted before the indicated rows in the code (It changes the number sequence in the workbook). The number sequence stays the same in the code which means I am now hiding rows either before (delete rows) or after (insert rows) the intended rows I want to be hidden. Is there a way to change the above code to remain with the assigned rows regardless of the adding/deleting of rows before it?
View 1 Replies
View Related
Sep 26, 2006
I'm using Conditional Formatting for an entire column to check the value of each cell and compare it to the value of a cell in the same row but a different column. (Cell Value equals =$D2) It works great until I insert or delete a cell in the formatted column. The reference does not change as one would expect. I've played around with formulae such as =$D2<>$P2 but the reference only changes for the P2, not the D2. I've also tried using =CELL("contents", ADDRESS(Row(),4)) but this causes excel to complain.
View 3 Replies
View Related
Jul 9, 2008
Checks if there is a sheet name and if there is a sheet of that name then it is deleted.
the sheet name is a number entered through an inputbox and "Average"
View 10 Replies
View Related
Dec 8, 2009
In writing Macros with Excel 2003, is there a way to have all other worksheets deleted except the present one, other than listing every other sheet?
View 9 Replies
View Related
