I want to delete a cell row in a table when I delete a corresponding worksheet with the same name as the cell.
For example, on the attached, if I delete the worksheet "Shawn" then I want row with "Shawn" deleted from the table on the "Example" tab. If I delete "Becky" worksheet then the row with "Becky" should be deleted from the table on the "Example" tab.
Delete cell when worksheet deleted.xlsx
When I use a reference to populate a cell in anothe worksheet and I use the delete key to remove the entry, a zero shows up in the cell of the worksheet wit the cell reference. For example if I use =AM!A3 and use the delete key to delete the entry in cell A3 of worksheet AM, the worksheet with the reference =AM!A3 shows a zero. Is there some way to prevent the zero from appearing in the cell with the reference?
View 3 Replies View RelatedI created comments to be shown when drop down list items are selected using the helper cell and workbook change solution from Leith Ross at the thread below.
[URL]....
This works perfectly to show the comments. What I'd now like to do is delete the comment if the content of the cell is deleted.
I recently downloaded a macro which came with it's own button on it's own toolbar. (I can't remember the developer, but it's called Multihide and simplifies the hiding and unhiding of worksheets).
Now, I've copied the button onto my own toolbar, and tried deleting the toolbar (by the usual method - Cursor over toolbar, R-Click, Customise, highlight toolbar name, delete) which came with the macro, and although it at first looks as though it's gone, when I next restart Excel, it's there again. I find I can't delete the button from the toolbar either, nor delete the whole toolbar.
I have 2 sheets Sheet1 & Sheet2
Sheet1 - contains rows of data
Sheet2 - summarizes those rows
I want it so that WHENEVER a row in Sheet1 is removed that same row is removed in Sheet2 and VICA VERSA whenever a row in Sheet2 is removed that identical row is removed in Sheet1.
- this is so that data integrity doesn't get lost if someone removes a row of data in the future.
I have a macro i would like to run whenever a user deletes a worksheet...
I would like the user to be able click as normal to delete,(ie. right clicking the tab and selecting delete worksheet, or selecting delete worksheet from the menu) but would like to run my macro when they select delete.
I am using excel 07 if that helps, or i could use another version...
My macro will save a copy of the deleted worksheet as xlveryhidden as a backup..
I am working on a workbook in which you can click a button on an index page to create a new sheet in the workbook with the name "meeting" and the number meeting that it is. The new sheet is then added to the index page. I am looking for a way to detect when a sheet is deleted so I can update my index page accordingly. I need to be able to know when a meeting is deleted so I can rename all meetings after that meeting and then update my index.
View 7 Replies View RelatedI'm currently working with 3 dropdown lists depending on each other using data validation and =indirect(). I arranged them like in a top-down approach where you have a mother-list and child-lists (categories-->subCategories-->subCategoryCriteria). The user should easily chose first a category then a subCategory etc... This part works perfectly
My question is: when a user chose in the dropdown list e.g. a category, a subCategory and a subCategoryCriteria and he now maually deletes the value in the highest level, which is the Category (mother-list), all sub level values like subCategory and subCategoryCriteria will stay. It shouldn't be that way, because it's not logical. If you turn a tree upside-down (top-down approach) and cut one of the higher positioned trunks, all lower positioned trunks will fall off as well, right? So, my question is: can I use a conditional formatting or a formula to delete or fade out the value in the lower lists when the higher list value is being deleted?
I tried to use If-formula in combination with indirect in data validation, like =IF(A1="";("");INDIRECT(B1))...just and example.
The value doesnt need to be deleted, it could also be automatically face out like white on white or something. It's just confusing for the user if he deletes the highest choice and the lower choices still stay.
I have a spreadsheet in excel with scheduling information, each group of data (job) is made up over 2 columns and 6 rows and is in a block (this is so it is easy to view and manouvre)
I have a link in another worksheet that extracts information from the cell if a criteria in the group is met!
At the end of each day I need to delete the columns (B and C, which make up that day) in the schedule.
When I do this all the references in the other worksheet are messed up, even though the next day is moved to columns B and C.
I would like the link to the other worksheet to automaticaly recognise the new values in column B and C as the reference.
I was customizing my rightclick cell menu, for cells in certain columns, to add some links to files and then I would delete them when right clicked in some other column.
By mistake I deleted EVERYTHING from my rightclick menu. If found where the problem was so it wouldn't happen again.
So now I am trying to put back those basic commands in the rightclick menu , but I cannot get those 'icons' that shows up on left of each command. how to get that?
I've a worksheet with 36 columns to keep track of a football confidence pool,I've a CF in which a W is entered in every other column from B1:AG1 and it highlites the whole column yellow,in column A is all players names,from column B2:AG2 I enter the 32 NFL teams,in row B3:AG3 I enter points from 1-16,in column AI it adds all numbers in yellow per row which gives me a weekly score,in column AJ I've the players name and there year to date points and finally in column AL it ranks the players by highest point total.
I will use this on a weekly bases for 16 weeks,what I need is for column AJ to keep adding the players points once I delete there respective numbers from rows B3:AG3 and add new ones for the following week.
Formula in cell AI3 =sumif($B$1:$AG$1,"W",$B3:AG3) copied down
Formula in cell AJ3 =A3 & " " & AI3 copied down
Formula in cell AL3 =rank(AI3,$AI$3:$AI$8,0) copied down
I also have the following macro an my sheet which allows me to use the formula in column AI3.
Function ConditionalColor(rg As Range, FormatType As String) As Long
'Returns the color index (either font or interior) of the first cell in range rg. If no _
conditional format conditions apply, Then returns the regular color of the cell. _
FormatType Is either "Font" Or "Interior"
Dim cel As Range
Dim tmp As Variant
Dim boo As Boolean
Dim frmla As String, frmlaR1C1 As String, frmlaA1 As String
Dim i As Long
I need to cycle and extract row data from the A, B and D columns and copy it into another worksheet while maintaining the copied data structure. Right now I am manually selecting the data....
View 9 Replies View RelatedI have working data on list sheet and added data on compiled sheet.
I need to match duplicates and delete these rows from compiled sheet.
I've tried coping to the list sheet and use "Remove Duplicates", but it rids me of the data that has been processed and has fills applied.
I'm looking for a macor that can do this work for me without losing data on list sheet.
This data reaches into the thousands and I'm looking to run this off the CASE column.
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?
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 need a formula or VBA macro to do the following:
I have 2 worksheets, namely sheet 1 and sheet 2.
Sheet 1 contains data in columns A to H.
Sheet 2 contains data only in column A.
I want Excel to check the data found in column A of Sheet 2 with those in Column A of Sheet 1. Hence, when an exact match is found in Column A of Sheet 1, Excel will delete that entire row.
I have a cell in my spreadsheet (J3) that I need to never be changed or else other macros in my workbook will not work properly. I decided to try and use data validation since it is easy to change the rule every month and I can stay away from messing with passwords.
My cell value is a date, so I set data validation to have that cell equal only that specific date. If a user tries to overwrite the cell value, the error messages is prompted correctly. The one BIG problem is that I can just press the delete key and delete the contents of the cell. Is there any way of preventing this from happening without having to lock the cell and password protect the sheet?
I used the code below for Conditional Formatting. This works fine but the VBA-code crashes when I delete more than one selected cell. Is there a simple modification possible to prevent this from happening?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("A1:C250")) Is Nothing Then
Select Case Target
Case 1
icolor = 6
Case 2
icolor = 12
Case 3
icolor = 7
Case 4
icolor = 53
Case 5
icolor = 15
Case 6
icolor = 42
Case Else
'Whatever
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub
I am maintaining an issues list in excel. File contains two worksheets/tabs: 1) Open Issues & 2)Closed Issues
One of the columns is Status. When an issues is in 'Complete' status, it is removed from Open Issues and added onto the 'Closed Issues' worksheet.
There are many issues closed daily and its quite a manual task to individually delete rows in one sheet and paste them at the end of the last row in the other sheet. Want to have a button/macro that would find any rows that have the cell for the status column w/ value 'Complete', delete it from 'Open Issues' sheet, and append it to the 'Closed Issues' shseet.
ive done basic c++ programing years ago, but know nothing about vba or excel macros.
I have a workbook with 3 worksheets. I am trying to delete 2 of the three but "Delete Sheet" is grayed out in both the "Edit" menu and RtMouseClick menu. The sheets are not protected and the workbook is shared. Do I have to un-share the workbook in order to delete sheete? If so.
View 9 Replies View RelatedI want to catch if the user has deleted a row. There is a need to know when a row is deleted and what the data was in that row. So, what I'm trying to do if a row is deleted is to undo the action and then change the font of all data in that row to have the strikethrough effect. This will enable others looking at the workbook to know that the information in the row existed but is no longer relevent.
So, the code is something like this:If row deleted Then
Application.Undo
Target.Font.Strikethrough = True
End If
Need to delete the line on this worksheet and post how they did it.
View 2 Replies View RelatedI'm trying to delete the buttons on the top of this worksheet, and for some reason am not able to do so.
View 3 Replies View RelatedI was trying to print a workbook which was about perhaps 20-30 sheets of roughly one page of printed info each. However, I saw the print job and it said it was printing over 7,000 pages!!!
It turns out there were about 4-5 worksheets which were blank (and also didn't have gridlines even though gridlines were checked on in options).
When I switched the sheets to page break view, lo and behold each sheet contained a few thousands "pages". I simply can't seem to reduce it down to a relatively normal size.
I tried going right past the edges of page 1 and deleting all columns/rows to the right and bottom of it to no avail. I selected the whole worksheet and removed all borders and any cell content with no luck.
I saw other "solutions" on the internet about copy/pasting the other unaffected sheets to a new workbook and deleting, but this seems cumbersome.
I am tring to make a macro that will find all charts in a worksheet and delete them.
the macro is essentilally a monthly refresh that cleans out a template of charts that were pput in the previous month. As the chart names and the number of charts might vary from month to month I just want simple code to select all charts in the worksheet and delete them.
I am absolutely new to visual basic thing but have a good idea of what I am doing in excel. I have seen some code to delete pics but can't figure out what it is that I am suppose to do. What I am doing is coping and pasting something from the internet. I do not need any of the pics that are pasted into the worksheet and am tired of deleting them individualy.
View 5 Replies View RelatedAn old post provided the following macros that purportedly prevent users from deleting a worksheet. Unfortunately, it also makes it so you can't delete ANY worksheet in ANY file, which is of course not what was intended. Need figure a way to remove this nuisance? All attempts to delete a sheet keep looking back to the offending workbook, even though the macro has since been deleted from it.
Try pasting the following two event procedures into the Help sheet module:
'==========================>>
Private Sub Worksheet_Activate()
Dim CB As CommandBar
Dim Ctrl As CommandBarControl
For Each CB In Application.CommandBars
Set Ctrl = CB.FindControl(ID:=847, recursive:=True)
If Not Ctrl Is Nothing Then
Ctrl.OnAction = "RefuseToDelete"
Ctrl.State = msoButtonUp
End If
Next
End Sub................
Can someone help me out with this script?.
I keep getting "Subscript Out of Range Error"
I'm trying to delete all the code on sheets(1) of the active workbook.
I have recorded a macro to copy a worksheet, perform some operations on the copy then delete the copied worksheet. My problem is that when running the macro I get a dialog box asking me if I am sure that I want to delete the sheet and I need to press OK. How can I get the macro to say it is OK to delete the sheet?
The last line of my macro is ActiveWindow.SelectedSheets.Delete. I have tried things like:- ActiveWindow.SelectedSheets.Delete = TRUE
It's simple enough to delete all macros. I have a form that starts as a landing area for a bunch of data then it gets renamed. I want to delete both of my command buttons from the sheet and remove all of my macros. How am I going to pull this off, how can i save it to the sheet the first time?
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'cut command buttons
ActiveSheet.Shapes("CommandButton1").Select
Selection.Cut
ActiveSheet.Shapes("CommandButton2").Select
Selection.Cut
[Code] .........