Showing All Cells On Close (removing Filters)
Apr 8, 2014
I have a spreadsheet where members of my team can run filters to view stats.
On close I have a basic script to remove all filters and show all cells to keep it consistent for the next user
VB:
Private Sub Worksheet_Deactivate()ActiveSheet.ShowAllData
End Sub
However, If i open the spreadsheet and don't run a filter when I go to close it debugs as alldata is already shown.
Any work around to keep the existing code working but to not error if all data is shown?
View 5 Replies
ADVERTISEMENT
Mar 24, 2007
I have the following code that displays a form at a user defined time and if the user does not press "Stop" then the workbook saves and closes. The user can press stop then the workbook remains open.
Here is what I have where:
Admin_Auto_Shutdown = Yes or No
Admin_Auto_Shutdown_Time = 3:34pm or user defined time (This doesn't seem to work??)
'Auto Shutdown CloseandSave
If UCase(wb.Worksheets("Admin"). Range("Admin_Auto_Shutdown").Value) = "YES" Then
Application .OnTime TimeValue("Admin_Auto_Shutdown_Time"), "AutoShutdown"
End If
Sub AutoShutdown()
Application.OnTime TimeValue("Admin_Auto_Shutdown_Time"), "AutoShutdown"
Auto_Shutdown_Form.Show
End Sub
Now, my question is about a timer that I can show on a form. When the form is displayed I would like to give the user 30 seconds to press stop (and keep the workbook open) or to press proceed and save and close or to not do anything and the workbook would close and save when the timer reaches zero.
Code for user form which is missing most everything...
Private Sub Halt_Click()
'If user whats to continue without closing
Auto_Shutdown_Form.Hide
End Sub
Private Sub Proceed_Click()
'If user whats to save and close
Auto_Shutdown_Form.Hide
How do I add a timer to this code where it will run this at the end of the timer?
Auto_Shutdown_Form.Hide
Application.DisplayAlerts = False
With ThisWorkbook
.Saved = True
.Close
End With
View 4 Replies
View Related
Jun 7, 2006
I have a problem with the attached spreadsheet. I have certain letters (A,B,C etc.) that are shipped to various regions. I would like to have a count on top to count the total number of orders, but one that also counts the total number of unique orders. However, this unique count has to be dynamic and must be able to adjust accordingly to the filters (by default, if no other filters are applied, should be 15). For example, if I apply the "Ship To" filter to Canada, the total number should be 19, but the unique count should be 12. If I change the "Ship To" filter to US, the total number should be 9, and the unique count should be 7. I've tried to use the advanced filters but if I apply the unique entries filter, it is only a one time calculation. Also, the advanced filter gets rid of my other filters.
View 5 Replies
View Related
Aug 30, 2009
I am using a number of lists that act as filters for a range of cells. For the filters, I have a few selections for each and I also would like to have an all filter (no filter) but without the formula being too lengthy (or code). Is there an easy way to be able to not use a specific filter if a certain selection is made ("All" for example)?
View 5 Replies
View Related
Jul 30, 2014
Creating a project tracking sheet that is as automted as possible so that people dont have to populate lots. I have managed to get it to create a new sheet and populate according to a filled out combobox. The user then populates a 'milestone' section. When a button is clicked these are transferred to a GANTT chart type sheet and pasted. Before they are pasted the macro checks if the project title exists, if it does not it first pastes the title LEFT INDENTED. Then follows by pasting the milestones CENTRED. If the title exists, it inserts the milestones under the project title by copying and pasting all milestones. Hence, if milestones are regularly added, it will begin to add duplicates of the same milestone. I need to remove these. I would usually be able to do this however there is often the same milestone in numerous projects which I CANNOT remove as it would loose data. I would like a code that says between the cell I was searching for (Rng) and the next cell which is also left indented to remove duplicates. Basically I can't use x1enddown because it needs to stop at the next cell which has the same formatting... i.e. only duplicates to be removed out of the cells which are centred. I would then like it to repear this action for the whole document and move the next left indented cell and do the same.
It feels like a kind of backward conditional formatting is what I am looking for
Here is my code as it stands
Dim FindString As String
Dim Rng As Range
FindString = Range("D2").Value
If Trim(FindString) <> "" Then
[Code]....
View 9 Replies
View Related
Jul 1, 2014
I have 3 pivot tables and with 3 filters each (they are all the same filters). I just want to change 1 of the filters for each of the pivot table (meaning the other 2 stay the same for all of the pivots). Is it possible to have a filter change automatically to match a filter in another pivot?
View 9 Replies
View Related
Nov 7, 2005
I have an extensive spreadsheet with several columns and all showing borders where each cell/column begins and ends. However there are a few cells where the break with the next cell does not show the line.
I have highlighed the cell in question, gone to format/cell/borders and everything looks fine. Black color + format border shows square with all sides of square showing.
View 3 Replies
View Related
May 1, 2009
I have a list box looking at range IB5:IB100 I only have about 25 names listed. However the list box opens up about 3/4 of the way down with the list of names that are not there. The user then needs to scroll up to see the names.
How can I get the list box to show only the names in the list? or at least start at the top of the list (IB5)
I have the ignore blank button checked.
View 4 Replies
View Related
Dec 26, 2013
On the top, you'll see that even though there is an equation in cell "K" and merged cells "O and P" that the cell is reflecting a blank box (how I want it to look). Within the same workbook, a different worksheet that has the same exact cell settings is showing "$0.00" in those cells. I can't figure out how to get it to be blank! Yes, I tried the copy/paste from the cell that I like to the other worksheet but it doesn't work. Again - all of the cell settings are the same.
View 3 Replies
View Related
Sep 28, 2009
if it is possible to prevent a listbox from showing blank cells.
I have selected a big range and many of the cells are blank. The reason is that I want the listbox to update automatically when I add a value to any of the blank cells.
Using the methods suggested by the posters here I was able to solve my problem. I had to modify my source data somewhat but it was well worth it.
View 11 Replies
View Related
Oct 30, 2006
is it possible to enter data on a spredsheet while a form is opened or must the from be closed first. I thought I was smart to have a form remain open so users could change parameters but at the same time allow them to enter data in uprotected cells.
View 2 Replies
View Related
Nov 24, 2009
i am removing cell contents using fallowing code
Private Sub CommandButton1_Click()
Dim row, col
row = ActiveSheet.UsedRange.Rows.Count
col = ActiveSheet.UsedRange.Columns.Count
For i = 1 To row
For j = 1 To col
Cells(i, j).celarContetents
Next
Next
End Sub
but next time the cell count is showing previous rows value?
View 9 Replies
View Related
Mar 22, 2012
I am using Excel 2003.
It’s been working perfectly up until around an hour ago. However, now when I close Excel, I get compile errors.
These compile errors seem to be because Excel is trying to access controls in the workbook after it has closed. Since the workbook is closed, VBA can no longer ‘see’ the controls, and therefore it thows up errors.
I’m also getting a similar error on a Worksheets("DataExplorer").unprotect line, which seems to be because the worksheet isn’t there after closing.
These errors only occur when I close the whole excel application using the big cross in the top right. If I just close the workbook (using the smaller cross just below the big cross in the top right of excel), it doesn’t throw up these errors.
Just to clarify: all of the code runs perfectly when Excel is open. The errors are being thrown up for lines of code which run without problem until Excel is closed.
View 9 Replies
View Related
Jun 29, 2012
I have the following formula which works perfectly unless one of the cells has a value of zero;
=MIN(SUM(AD13),MIN(SUM(AK13)))
When AD13 = £60.00 and AK13 = £94.00 (or vice versa) the formula returns £60.00, which is correct. BUT when AK13 = £0.00 obviously the formula returns £0.00, but I would like the formula to still return £60.00.
How this can be done?
View 3 Replies
View Related
May 22, 2014
I've put together a macro to format an extract from a fuel report but at the end of it I need to remove numbers in front of drivers names.
E.g.
1. John smith
10. Joe bloggs
123. Peter piper
Should end up as
John smith
Joe blogs
Peter piper
All without the number, full stop and the space before the name.
View 5 Replies
View Related
Dec 29, 2011
I have a column of numbers that are in sets of 3. (123 456 789) I need to remove these spaces and just see 123456789.
View 3 Replies
View Related
Jan 4, 2013
I have a excel sheet and inside certin cell are vales of "0". I would like to delete those 0 but keep the other values I have around them. Is there any way to delete only the "0" without going cell by cell by cell?
View 3 Replies
View Related
Jan 26, 2014
I have inherited a spreadsheet that had check boxes in it. I don't want to use them. I was able to delete all but 4. I can't figure out how to get them removed.
View 4 Replies
View Related
Mar 3, 2007
I have a lists of e-mail address (up to 15 rows long) in adjacent columns.
At times one or more addresses need to be removed. After removal I need the list to 'bunch up' so that the space caused by the now blank cell is removed.
At the moment I am doing this with vba as follows:
Sub Clear_Blanks()
With Range("e13:e27")
. AutoFilter
.AutoFilter Field:=1, Criteria1:="<>"
.Copy Range("e35")
.AutoFilter
.ClearContents
End With
Range("e35:e49").Cut Range("e13")
End Sub...
View 4 Replies
View Related
Nov 4, 2006
I'd like to copy a range of cells from an open workbook to a closed workbook, but only once the open workbook is closed by the user.
I have found info on copying from an open workbook to a closed one, but can't seem to find anything similar to an 'onClose()' function for workbooks.
How can I manage this?
I use a UserForm to fill-in rows with data one at a time (the form needs to be reInitialized for each row entry). So, would it be better to just copy all rows within the range each time the Form itself closes (by coding the copy/paste in my "Submit" button)?
I figure this might be a possible work-around if an onClose() function doesn't exist for workbooks. However, I would prefer that function, so as not to clutter my userForm code.
View 8 Replies
View Related
Aug 6, 2007
I have a spreadsheet that has been sent to multiple clients. I have discovered an error in a few of the cells:
1. a link between two cells is broken so the correct number is not showing up in the cell. It is a simple formula ie: =A15, that I either forgot to put in or deleted in a fit of madness. It is in the same cell on the same worksheet in the workbook.
2. A hidden column on a worksheet has a formula that sums a range of cells. I have discovered that this range of cells is wrong. This error is duplicated on a number of worksheets in the workbook. The problem is that the error is in the same column in each worksheet but not in the same row.
Each worksheet is password protected. This is done through VBA code so the passwords won't be a problem.
The clients have already started working on the spreadsheet that I sent them and I want to send them a "fix" so that the correct formula will just update the spreadsheet they already have rather than me sending a completly new one and them having to start from scratch.
I had this wonderful spreadsheet set up with columns that appear and disappear and formulas galore so that the client didn't have to do to much work and then I find these errors after it has been sent. My testers let me down but that's life.
View 9 Replies
View Related
May 28, 2014
How to remove all the " symbol out of a string of data?
The " symbol appears at different places and i need to remove them all completely. the Find & Replace tool doesn't work because when you have say 3/4" and want it to just read 3/4 it automatically converts it to 03-Apr. even when you have changed the cell formatting to text.
View 12 Replies
View Related
May 25, 2013
I have a spreadsheet which reads:
A1 E012345678
A2 126789433
A3 ABCDEFGH
A4 CDEEGFFH
A5 E0456783
A6 98765432
etc.
I need only the the data in Cells A1 A5 etc. which means the 3 cells below (A2, A3, A4 ) should be deleted. A5 I need the data, and then A6, A7, A8 I do not need ... A9 need and so on.
the data should be in in one below the next with no spaces in between.
Data is only in column A.
View 6 Replies
View Related
Oct 18, 2011
I have imported data and it shows "Check 1234". How can I use a formula to remove "check" from this cell?
View 3 Replies
View Related
Jul 7, 2014
I have been sent a spreadsheet that was populated from another system. I need to run some VLOOKUPS but the cell that I need to compare has a ' in front of the text/number eg 'VB123456. I have found a thread on how to remove it for a number but it does not work with letters.
View 6 Replies
View Related
Sep 17, 2007
E122112 David Hall
Robert Townsend
Micheal Keel
Tanya Smith
Elizabeth Charles
E004587 Andrea Tummings
The problem is for those names that come thorugh with the Letter and than the number...I don't need the names like that....I would like a Macro that would look at each cell in column A and remove that from those selected cells. I would like my result to look like this...
David Hall
Robert Townsend
Micheal Keel
Tanya Smith
Elizabeth Charles
Andrea Tummings
View 9 Replies
View Related
Jun 4, 2009
I have a bunch of cells that have City and Zip Code combinations.
Ex: Chicago 606
Fayettville 72701
SACRAMENTO 95691
Some of them are 3 digit zips and others are 5 digits. I just want to weed out anything with a number leaving the city names.
View 9 Replies
View Related
Jun 9, 2006
How do I shift all the cells up labelled data, so that there are no blank rows in between? I tried using the ones found on the forum via search but it is stuck in an infinite loop.
View 5 Replies
View Related
Mar 17, 2007
I have a spreadsheet which will be access by others and they will enter rows of information. I want to ensure that they do not half complete rows. Therefore I would like to know if I can prevent the workbook from closing unless data is entered into each cell in the specifed columns?
View 8 Replies
View Related
Feb 14, 2013
I want to establish a link from my worksheets. Using the conventional link method I was able to link the values from my first worksheet to the second worksheet. My problem is when I delete a particular row. The reference of the second worksheet will have an error #REF! since I deleted those cells. Is there any way that I can link my two worksheets without any error that even if I deleted a particular cell/row the reference is still intact?
View 2 Replies
View Related
