I have a workbook with particular worksheets that should never be deleted. If they are deleted by the user the workbook will become unusable.
The users need the freedom to edit data within the sheets (so protecting them is not an option), however, I need to restrict this freedom so that they cannot under any circumstance delete the sheet. Is there such code that I could use in the worksheet code to stop this?
I have a shared spreadsheet which contains only very simple "sum" formula's and I am trying to stop users accidently deleting them when they are inputting numbers.
I have tried using data validation, but that only stopped people overwriting the formula and not deleting them.
I also tried protecting the sheet, leaving only a cell range which users were allowed to edit, which worked... BUT I also have several rows which are grouped and when I protect the sheet, I cannot collapse and/or expand these groups.
Is it possible to prevent a particular sheet from being deleted?
I have a workbook that contains sheets (of course). One of the sheets
serves as a "Help" sheet. I don't want users to accidentally (or intentionally)
delete just this sheet.
Is it possible to make it so that a user cannot delete or add sheets to a work book, except through buttons assigned to macros? I've tried simply locking them, but these sheets need to be accessed to input and manipulate data. I have the macros necessary to delete/add sheets, but right now they are redundant since the user can simply do it manually.
View 3 Replies View RelatedI have a "userform" with a "Delete button ", "Close button" and a " combobox". Combobox gets populated with all sheet names using this code.
Private Sub UserForm_Initialize()
' Add sheet names to combobox
Dim intLast As Integer
Dim wSht As Worksheet
intLast = ActiveWorkbook.Sheets.Count
For Each wSht In Worksheets
frmCalDays.ComboBox1.AddItem wSht.Name
Next wSht
End Sub
Then if user wishes to delete a sheet he simply selects a sheet name from the combobox, clicks on delete button and sheet gets deleted using this code.
Private Sub cmdDeleteEmployee_Click()
On Error Resume Next
Application.DisplayAlerts = False
Dim strDelSht As String
strDelSht = ComboBox1.Value
ThisWorkbook.Unprotect Password:=""
Sheets(strDelSht).Delete
Application.DisplayAlerts = True
ThisWorkbook.Protect Password:=""
On Error Goto 0
End Sub
But ("here's my problem") my userform unloads without me clicking on the close button. I would like to have the userform stay loaded after clicking the delete button.
I have a Sheet with some Macro Buttons for the boss to use.
How can I protect the sheet from any Data Entry changes, but still allow the Buttons to be used?
I tried a few scenarios and in all, the Buttons can't be used.
I have a sheet and want users to be able to type in a set of cells but not able to move the cell how do i do this?
View 9 Replies View Relatedway of protecting a very hidden sheet, I will still need to open the sheet myself everytime I use the program. The only person to open this very hidden sheet is myself.
View 9 Replies View RelatedI want to run a macro that puts information from one sheet into another. The destination sheet is protected so I want to unprotect the sheet, do the copy and pasting then reprotect the sheet. The sheet has a password and I can't work out how to put that into the macro. It works fine without a password.
View 9 Replies View RelatedI am going to use Excel sheets as computer exam forms. What I need to know is: Is there a way of protecting parts of an excel worksheet from alteration? I want a sheet that will accept answers in specific areas only, and will not accept entries or alterations in other areas.
Excel 2002
I have a sheet that has some cells already protected by a password. I want another person to be able to make changes to the unprotected cells and then be able to push a button (macro) that will then unlock the sheet activesheet.unprotect ("mypassword"), change all the cells to "locked" and then have the protect sheet window pop open so that they can type in their own password and save it. Is that possible?
i have produced a spreedsheet on ms excel.
On sheet 2 is all of the information and tables etc.. for example prices and information for the combo boxes an such...i need to protect this sheet so no one can change anything on it
the problem is when i do so, and go back onto sheet 1 and say tick a checkbox or select something from the combo box is doesnt allow me to, the protection message comes up
i dont want this to come up on sheet 1, only on sheet 2!
i think its because i have links between the pages? like all of the info for the combo box is on sheet 2
I need is for the Roster sheet, which has the exact same column and row headings as the Leave sheet, to read the data from the Leave sheet and change dynamically if the Leave sheet is changed. If there is data in any cell then I need to protect just that cell in the Roster sheet so that the user creating the staff roster is unable to edit it out.
I have created a plain cut and paste macro on open from Leave to Roster but this doesn't give me the dynamic linking, and doesnt help solve the problem of needing to check each and every cell in Roster against a corresponding cell in Leave, but also if its blank in Leave to allow the user to update that cell in the Roster sheet.
I need to protect a sheet with following access to users.
1. data can be entered in column a only... rest is automatically updated with formulas
2. users can insert/ hide rows. but can not delete any rows and can not hide/ insert/ delete columns
Is there a way to lock out a spread sheet if it is sent to an unauthorized e-mail address or forwarded to another e-mail address?
View 9 Replies View RelatedA1 is number of days available
this sheet goes to a routing recepient from me who fills A2 and B2. C2 and D2 are calculated.
A2 has starting date
B2 has ending date
C2 is days between these two dates
D2 is number of days left i.e. A1-C2
I want to lock and protect cells A2,B2,C2 and D2 so that these cannot be changed when I route it back to get next set of dates to be filled by the same person.
I have a macro that duplicates a excel file into how ever many files I need... so If I need 150 copies of the the file, it will make it 150 times.
However, each file the first tab called "Clients" has information that I want protected so users can't alter it. I can't protect it before I run the duplication because the data is added by a group of people and I need it unprotected until they add the information.
What I am looking for is a macro that I can click and run, allows me to select multiple files, then it will automatically Protect Sheet 1 "Clients" on all the files I select so I don't have to open all 150 files after data is entered and Protect sheet 1 manually.
What is the best way to protect a Data sheet, but still be able to use a FrontEnd Userform.
View 4 Replies View RelatedMacros are just not my strong suit. I can usually find my answers here but this one has me stumped. I have rewritten this thing everywhich way but loose and this is my latest version.
Sub SetProtect()
'
' protect Macro
' prompts for password protection
'
' Keyboard Shortcut: Ctrl+p
'
Dim Response As String
Dim Answer As Integer
SetProtect:
Response = InputBox("Enter password")
If Response = "safety2" Then.......
I am trying to write a VBA code that prevents the user from deleting row 8.
I do not want to use the sheet protection with password approach.
I simply want a code that prevents the user from deleting row 8 and notifies him via MSGBOX that such action is prohibited.
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 RelatedI have an Excel sheet that is similar to the following:
Customer----- contract ----- value
XYZ ------- 123 ------------- $50
ABC ------- 987 ------------- $150
XYZ ------- 123 ------------- $-50
XYZ ------- 123 ------------- $200
In the above example, I want to delete Row 1 and 3... it is an accounting issue, and when the values of the same contract match (50 and -50) then it needs to be deleted from the Excel sheet (whole row).
I want to leave rows 2 and 4.
I am working on a large Audit project for one of my many bosses and I am a little stumped so I came to the experts. I have a list that is organized randomly and we need to have 95% of the cells deleted. I don't want to have to go through each time and calculate that out each time.
I am sure that I would have to find the last cell used in one of the columns and then multiply that by 95% and then delete from that row down in the sheet. (Since the sheet is already organized randomly it can delete the bottom 95% of rows.) I am not sure how to put it all together.
I have read several posts on this subject as well as the two common methods recommended by Dave. Hovever, I have not been able to incorporate the other thread recommendations to my specific problem. I want to delete entire rows based on the absence of a matching criteria from a list on another tab. Example: Tab1 contains a list of names in A1:A15. Tab2 contains rows of data wherein column C lists names that may or may not be in the list on Tab1. I want to delete rows that have a name in column C that does not match any of the names in the list on Tab1 A1:A15. Here is the code I have pieced together thus far:
Sub Delete_elimntd_players()
Application. ScreenUpdating = False
Sheets("QB").Select
Range("C2").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value <> 'NEED CODE HERE' Then
ActiveCell.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
Application.ScreenUpdating = True
End Sub
I have been pulling my hair out for hours now trying to figure this out. I want is to figure out is how to compare an address in Column C to an Address in Column H, If they don't match I want excel to delete the row. I have 7500 rows to compare. I have read lots of posts and looked at the code for delete row conditional but I don't seem to be able to put it all together.
View 2 Replies View RelatedI have a SS in 2007 with 7 text boxes on it. When I go into developer and select them, they all have the same name in the name box of TextBox 68.
I would like to be able to clear all the text in the boxes and have yet to come up with any code to tackle this. Why do they all have the same name?
I have some code that works fine below. It deletes an entire row based on the value in column J.
Ideally I would like to give the user the option to choose which column to base the deletion on. i.e. some kind of drop down box or input parameter where they could choose a different column "A", "B", "C" etc...
Private Sub CommandButton1_Click()
'Removes values less than 0
Dim rng As Range, cell As Range, del As Range
Set rng = Intersect(Range("J1:J1000"), ActiveSheet.UsedRange)
For Each cell In rng
If (cell.Value)
I currently have a macro set up to delete rows if a certain user selected value is not found in a certain column. It works fine if the sheet isn't overly large but the problem is we have some spreadsheets with 25,000+ rows and it takes time to loop through - I'm not sure if there is even a way to make it faster.
Currently it works by looping backwards on the sheet checking each value in the cell versus an array containing the user selected values. If a match isnt found it deletes this row.
'y = long value representing row
'x = counter for each item in user selected array
'wsSheet = worksheet we are using
'rowLast = last row on spreadsheet
'arrSearch() = string containing user selected items
i have screenupdating/calculations/events turned off - i just didnt know if there was a faster way of looping through
i just thought about instead of doing an array maybe joining the array into 1 string and using a "like" comparison check to see if a match is found - would save me from having to constantly loop x * y amount of times - not sure if this would work or not. ill post back if it doesn't
I am currently using this code which works:
If Me.txtNSNumber.Value = "1" Then
Me.Height = 198
End If
This is the original code I found and believe it will give me what I am trying to achieve (I have tested and works):
Dim ctl As Control
For Each ctl In Me.Controls
If Left(ctl.Name, 3) ="txt" Then
ctl.Text =""
End If
Next ctl
I have tried to modify to suite my requirements however it fails to work correctly (no error messages just does nothing). Basically I am trying to combine them so that the various textboxes named as txtNS1 (with varying characters after the numerical digit) would clear the data dependant on the value of txtNSNumber.
This is my attempt at modifying (from my basic knowledge) which I have tried to altered the string start and character elements without success:
Dim ctl As Control
For Each ctl In Me.Controls
If Me.txtNSNumber.Value = "1" Then
If Mid(ctl.Name, 6, 0) > "1" Then
ctl.Text = ""
End If
Me.Height = 198
End If
Next ctl
i have a workbook with 6 worksheets in it. the user can put data into any cell in any worksheet. what i want to be able to do is prevent the user from accidently deleting any of the worksheets
View 8 Replies View Related