Prevent Sheet Insertion & Deletion

Sep 14, 2007

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


ADVERTISEMENT

Prevent Sheet Deletion

Sep 3, 2005

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.

View 10 Replies View Related

Prevent Deletion Of Row

Jun 11, 2014

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.

View 1 Replies View Related

Prevent Some Worksheets From Deletion

Jan 5, 2007

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

Prevent Deletion & Allow Entry

May 2, 2007

I am trying to complete a spreadsheet that will be shared by several users but have come across a slight problem. I need them to be able to enter text into blank cells but when the the text has been enetered for them not to be able to delete or edit it again in columns A & B. They should also not be able to delete rows or columns. I have tried with other peoples help but as soon as the spreadsheet becomes a shared document I get a run-time error 1004 when entering in any box

View 9 Replies View Related

Prevent Deletion Of Formula & Other Cells

Sep 12, 2007

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

View 3 Replies View Related

Allow Insertion Of Row In Protected Sheet

Sep 4, 2013

I wanted to know:

Q1. How can we insert new rows in a protected sheet?

For example: In the attached sample protected excel sheet "insert rows.xlsx" , if for Employee ID and Name fields, the user has more no. of records than provided, how can he insert new rows considering the fact that Company ID and Name field are locked.

Or any way to address this issue via some other method like adding a scrollbar,etc.

View 5 Replies View Related

Conditional Insertion Of Object In Sheet

Nov 23, 2007

I need to insert an arrow in a sheet, in a location and a direction which can be controlled by me (I don't mind hard coding the location and direction), based upon a cell value being positive, negitive or 0. I realise that it will require some code but can't find any infomation on doing anything like this through google or in the various VBA texts I own.

View 2 Replies View Related

Prevent Deletion Of A Cell With No Cell Protection

Oct 29, 2008

I have a worksheet with multiple entry boxes in a row. Depending on the value in one row, another row will either yield the result from a formula, or remain blank, and this cell needs to be locked. I was able to do this by first putting the formula into the destination cell, and then using custom data validation that says the cell value = "". So this prevents anyone from typing in that cell, no matter if it yields the formula result or must stay blank. What it doesn't protect against is someone deleting the contents of the cell. The reason I don't want to protect the cell is because I want people to be able to highlight the whole row past that cell and delete it all at once. So either using Data Validation, a formula, or VB programming I need to not allow someone to do ANYTHING to that cell, even delete the contents, without locking the cell. Hopefully it can be done, thanks!

For example, if E17 = "X", then K17 = INPUT!$M$28*J17. If E17 <> "X" then K17 is blank. Either way, I don't want any typing or deleting of K17. This applies for range K17:K91, and corresponding E17:E91.

View 14 Replies View Related

Protecting A Sheet From Deletion

Mar 26, 2007

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?

View 12 Replies View Related

UserForm Unloads On Sheet Deletion

Jan 5, 2008

I 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.

View 9 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

Row Insertion And Sorting

Jul 27, 2007

I have a spreadsheet with data in 4 different sections with a summary section at the top. Data titles are in column A with the data to the right, arranged by month.

I have created macros to sort the data in each section. Occasionally I need to add a new row to each section for a new data set. When I do this, it screws up my sorting macros for the sections below. It will sort the same rows that it sorted before, not taking into account that the data is now one row lower.

View 9 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

Prevent Sheet Names Changes & Moving

Dec 27, 2007

I'm an intern working at an aerospace manufacturing house and I need to automate a lot of the excel files I created last summer.

Anyway, here is my problem:

I've been making macros but I am worried about people moving the worksheets around or changing their names which will have the potential to mess up the macros. What preventive measures can be taken/ changes in the code? I feel like there is something very simple I am missing.

View 5 Replies View Related

Row Insertion Based On Cell Value

Jun 27, 2012

I am working on a project of 4000+ entries. We will be identifying the number of seasons in a year. We would like to add a row in another worksheet for each of the seasons identified.

So say we had data in row 1 of worksheet 1. That row stated there were 3 seasons. Is there a way to automatically insert 3 rows into worksheet 2?

View 1 Replies View Related

Formula Insertion Into Column Using VBA

Jan 31, 2013

I have a need to insert formulas into a column using vba. I need to skip a row, when inserting the formulas, so a simple drag using vba would not work without some modification.

Here is the formula, that I need to insert... into column "BJ"

Example Cell BI11...=SUM(BH11-BH10)

Cell BI13...=SUM(BH13-BH12), the next cell needing a formula would be ..
Cell BI15...

The formula would be inserted, based on the last row used in column "C".

View 3 Replies View Related

Preventing Insertion Of Rows

Jun 5, 2008

I would like to prevent the user from inserting rows. I know that ater versions of Excel have built in "Protection" features which would easily accomplish this. Unfortunately, our company uses Excel 2000.

I have keyed in a constant value in cell B100 ("bottom" of the practical range). I have tried ...

Private Sub Worksheet _Change (ByVal Target As Excel.Range)
MsgBox "Range" & Target.Address & "Was Changed."
If Range ("B100").Value = "" Then
Application.Undo
MsgBox ("Do Not Insert or Delete Rows")
EndIf
End Sub.

The problem with the above is: The "Change" macro does not seem to detect the insertion of rows. It does, however, detect the deletion of rows.

View 9 Replies View Related

Prevent To Take Me To Another Sheet When Clicking On A Locked Cell.

Jul 11, 2009

I have one issue with my protected worksheet. Some locked cell contents are linked to cell contents located in another sheet. When I double clicked on these cells, a warning window poped up but also opened the sheet in which the corresponding cell is located. This is quite annoying to have to go back to the former sheet. How can I prevent it ?

View 3 Replies View Related

Prevent Sheet Rename By Double-click

Jan 30, 2008

Is there a method by which I can prevent a user from renaming a worksheet by doubleclicking on the sheet tab ? Please note that I do not wish to use workbook protect method.

View 9 Replies View Related

Prevent Hyperlink Breaking On Sheet Name Change

Aug 23, 2007

I have a workbook with a template worksheet. I can generate new worksheets using the Template, and create hyperlink to the new worksheet (Let's call it "New"). However, if an user changes the name of the worksheet from "New" to "SomethingElse", then the hyperlink breaks. How do I get the hyperlink to be automatically updated to "SomethingElse"?

View 4 Replies View Related

Freezing A Column From Insertion Of Other Columns

Dec 16, 2013

click on cell A4 -right click - insert cell- shift to right, so figure 2 moves to C4 pushing the yellow cell to D4 right?

what am trying to do is to make column c to be frozen or locked or whatever you want to call it, so if I insert a cell in A4 shifting to the right, the value in B4 jumps to D4 without shifting the yellow cell

View 2 Replies View Related

TOC: Main Menu Column Insertion

Sep 24, 2008

I've been using this code to make menus for all my Excel sheets since NateO made it back in 2003.

- The workbook has 370 sheets, 365 of them are one day per month with the tab named ddmmmyy.
- The Main Menu in this file puts the months Jan, Feb, Mar, etc in cells A3:L3.
- In the single cells under each month it put the month day tab name (ddmmmyy) and hyperlinks it to the sheet tab.

I would like to do two new things.
1. Put the name of the month in everyother cell in row 2, so Jan would be in A2, Feb in C2 and Mar in E2, etc.
2. Put the day of the week for each date in the cell to the right of the date. So the column heading would be Jan in A2, 01Jan08 in A3 and Tue in B3.

I thought I could make another macro and just insert the rows after the Main Menu was built and then VBA copy/paste TEXT(cell,"ddd") down the ranges, but thought there might be a better idea out there...

Sub Workbook_TOC()
Dim ws As Worksheet
Dim wsNw As Worksheet
Dim z As Integer

View 9 Replies View Related

How To Prevent Duplicate Records From Being Entered On Recording Sheet

Feb 9, 2013

I have a created recording sheet (attached) for the purpose of recording moths. I send this sheet out to those recorders who live in my county and at the end of the year they send the filled in sheet back to me. Over time I have put safeguards onto the sheet to ensure that I get the cleanest data returned to me. However, there is still one area which is giving me problems - users entering duplicate records.

Some formula which will stop recorders from entering those duplicate records. There are many columns on the sheet which recorders have to fill in with information, but as far as the duplicate records are concerned it is where the same information is entered into columns C, F and J that I'm having a problem with. It would be great if, when the same information was entered in these columns that the affected cells were highlighted in some way and if an error message appeared saying 'Duplicate record, either delete or alter grid reference'

I have entered two test duplicate record on the 'macro moths' sheet of the attached file : Test Recording sheet.xls‎

View 14 Replies View Related

Prevent Sheet Change Macro When Solver Runs

May 26, 2009

Is there a way to prevent the Workbook_SheetChange event macro from triggering when I run Excel's Solver?

The solver goes through 100 or so iterations changing multiple cells each of which seemed to trigger the Workbook_SheetChange event. If there was a Solver Run event, I could turn off application events, but as far as I know such an event does not exist.

View 9 Replies View Related

Update Function In Cells Following Addition Row Insertion

Jan 14, 2014

I have a command button that runs a macro to insert two new rows between row 15 and 16 no real problem (see code & sheet below), but I would like the Total Hours and Total Cost rows to include the newly added rows i.e.

Rows 16 & 17

Cell B18 now contains =B2+B4+B6+B8+B10+B12+B14+B16
and
Cell B19 now contains =B3+B5+B7+B9+B11+B13+B15+B17

If another two rows are added then Total Hours and Total Cost Cells 20 & 21 will now be

Cell B20 now contains =B2+B4+B6+B8+B10+B12+B14+B16+B18
and
Cell B21 now contains =B3+B5+B7+B9+B11+B13+B15+B17+B19

Command button Code to insert row

Code:
Private Sub CommandButton1_Click()
Const fWhat As String = "EXTERNAL"
Dim sR As Range, fR As Range, fAdr As String, nRw As Long
Set sR = Range("A1:C187")

[Code] ..........

Worksheet Below
1
Hours
170

Cost
387.99

[Code] ...........

View 1 Replies View Related

'Background' Insertion File Size/limiting?

May 15, 2006

I'd like to insert a picture background for multiple s/ sheets I've developed, however, even though the images I'm using are only 30kb's in size, it bumps my s/sheet file size from 900kb's to over 4mb's ! Is there a way I can stop this from happening, or, any way to restrict the background image to only a certain part of the worksheet as opposed to the entire thing?

View 3 Replies View Related

Position Insertion Point In Cell While In Edit Mode

Aug 24, 2008

When a user clicks a cell in Column A, VBA will enter the date in the cell (in the form MMDDYY) and will position the insertion pointer at the end of the date. The user can then enter a four digit number following the MMDDYY. In this worksheet, Column A holds a transaction number, which is always in the form MMDDYYXXXX. I am trying to save the user the trouble of entering the MMDDYY, since it will always be today's date.

View 3 Replies View Related

Automate Time & Date Insertion Upon Cell Selection

Jan 21, 2010

I have a Workbook with about 20 Worksheets, for each sheet I have a Selection Change Event that simply performs the Following:

MainSheet1. Range(Z1) = Now

1 Cell on every Sheet then is pointed to this cell via =Main!Z1. Problem is that when this is preformed Each Row from the Other In-Active Sheets and sometimes a near-by Button will Appear on my Active Sheet. I cannot select the parts of the sheets that appear and when I scroll down till they are out of view and then scroll back up they are gone until I select another cell and it happens again. I have tried in the Z1 cell to just put =NOW() and Make Calculations Manual and then with the selection change to call MainSheet1.Calculate or Workbook.Calculate but this has the very same effect and the other sheets bleed onto or ghost onto my Active Sheet. So far the only way Ive been able to get around this is:

If Application. ScreenUpdating Then
Application.ScreenUpdating = False
MainSheet1.Range(Z1) = Now
Application.ScreenUpdating = True
Else
MainSheet1.Range(Z1) = Now
End If...............

View 7 Replies View Related

Insert Date & Time At Mouse Insertion In TextBox

Sep 28, 2007

I need to insert text at the curson position in a text box on a VBA form. I know how to make the button not take the focus; I just need to know how to pass the cursor a timestamp.

View 7 Replies View Related







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