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
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
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.
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)?
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
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?
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.
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)
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.
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.
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.
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?
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.
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.
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?
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.
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")
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.
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.
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.
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.
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.
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
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.
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?
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?
