Auto Filter Won't Sort
For some reason I have a worksheet that won't sort stuff by Autofilter any more. I built it as a customer database but for some reason today I just can't get the names to ascend or descend. there is something simple I can check for, I can post an empety sheet if needed but right now I can't really post a list of all my customers info.
View Complete Thread with Replies
Related Forum Messages:
Both 'Private Sub Workbook_Open' And 'Auto Open' Run Sequentially
I have built a complex vba & multisheet spreadsheet that I am looking to secure against all the common attacks. So I have:A Workbook Open pw;
VBA password (29 symbols/numbers/Caps/lower case)
Very Hidden worksheets
Restricted scroll areas
Code that auto protects all sheets upon opening
Registry referencing in Workbook Open with timed closure if not matched
Now I am on the last leg of implementing protection against Application.EnableEvents = False; force enabling of Macros and hiding of toolbars, scrollbars etc... Phew.
Soooo, in my research, I have learnt that if EnableEvents = False, Workbook_Open is essentially skipped and the security VBA routines are disabled. To get around, I have copied the entire contents of the Workbook_Open routine to a module under Auto_Open. The first line of both these scripts is: Application.EnableEvents = True.
Works! So far so good.
However, I have a Msgbox prompt in the scripts that displays twice. So in essence, Excel is running Workbook Open first, then Auto Open second.
If I open another instance of Excel and run Application.EnableEvents = False first before opening my spreadsheet, I only get one message. So only the Auto Open script runs.
Filter Results Of Primary Filter Sort
I am in the process of making a database more efficient and am running into a problem with sorting data. I currently am trying to use AutoFilter to sort the data. When I want to then narrow the results further using the same column as the critical and there is nothing that matches the critical, I get everything from the entire database that matches that critical rather than what I want to see, which, in this case, would be nothing.
I have attached a sample file. In it, when All AF 1000 is run and then Selected MAC 2000 Wash is run, I want to see no results instead of seeing all MAC 2000 Washes from the original data set. I can do it using IF/THEN but I am looking for a faster way to do it.
For/Next Loop Won't Start
I'm sure I just need a change in the line of code in red, but can anyone see why when the code reaches the For/Next loop in red it just jumps over it and goes to the End With line?
FYI - The code is supposed to check (select) the boxes in ListBox1 if any item in the list it's creating matches the value found in Sheets("Zone Associations").Cells(Rng, sZone)
Excel Won't Quit
I'm having a bit of a issue at getting Excel to fully shut down. it is still running in the Task Manager. There is a bit of infomation on the net about but i can;t seem to make it go away. I'm using Excel 2007 and running VBA code out of MSaccess 2007
Heres my code.
Won't Let Me Save My File ...
Excel won't let me save my file! When i try to save it it says "Your changes could not be saved to "*.xls", but were saved to a temporary document named "F65aa000". Close the existing document, then open the temporary document and save it under a new name".
Every time I try to save it under an existing file name, it won't do it and the existing file is then renames to some alphanumeric file and the same message appears.
This file contains macros that took a long time to do!
Function Won't Return Value
I'm trying to get a "simple" function to return a number and not the #NAME?error!
I'm writing the following vba code. It's located in the sheet1 vba code area.
On the spreadsheet itself, on sheet1, column C, there is data from row 1 down to row 55. I have tried formatting the Column as General, text,etc.
Function Asset() As String
Asset = Range("C1").End(xlDown).Row
When I type into the spreadsheet =Asset() , I get the #NAME? instead of the number 55.
When I step through in debug mode, Asset shows Asset="55" , but my function doesn't return it!
Sheet Won't Recalculate
My sheet has a very simple formula in a lot of the cells that simply say this cell in sheet A should equal a cell on sheet B in the same workbook.
I deleted the contents of the cells in sheet B, however the cells in sheet A did not update. It still shows the contents of sheet B that were there before I deleted them. The sheet is set to calculate automatically and I even commanded it to force calculation.
Any clues as to why my sheet will not update?
If I cut a formula from a cell in Sheet A and paste right back, it updates correctly to reflect the cell that was deleted in Sheet B.
Picture Won't Delete
I am working with a spreadsheet created by a predecessor and there is a picture at the top of one of the worksheets. When I select the picture and hit Delete, it seems to work, but the picture is still there. When I move it around the worksheet and resize it, a copy with the same name ("Picture 47") remains in the spot I moved it from. When I delete the resized/moved picture, it deletes, but the picture still remains in the original spot.
When I record a macro and select the picture and hit delete - or even when I move the picture and resize it - the recorded macro is blank.
All of the pictures seem to be named "Picture 47"
Formulas Won't Refresh
The below is my problem. I have a excel wokbook that contains 2 reports from an outside source and on top of the reports i have some excel calculations.One of report has drill down capability. Everytime i open my excel workbook and refresh it the excel forumals also get refreshed along with my 2 reports and i get my results. But when ever i drill down something on the report the excel formulas are not getting refreshed automatically.
I end up refreshing the whole workbook to get the correct results which is a very time consuming process considering the amount of data i am trying to pull. And drilldown is something the users do very often and they don't like refreshing the whole workbook.
Is there any way to update the excel functions automatically whenever i drilldown?
A UDF That Won't Update By Itself..
I have a UDF that looks up data in another worksheet (called "Capital Costs") to create data for the worksheet in which I enter it. This is the first UDF I have ever written, so I must be doing something wrong. It works just fine--once.
But if I go into the "Capital Costs" worksheet and change the data there, it does not automatically update the cell in the other worksheet with my UDF. I have to erase my UDF and reenter it for it to grab the new data in "Capital Costs". (I have tried hitting F9, and that doesn't update the UDF.)
My UDF is "=Capex(year)". So let's say my year 1 Capex was $50,000. The UDF calculates that and populates the cell correctly. But if I go into the "Capital Costs" worksheet and change the year 1 Capex to $60,000, the UDF (which is on a different worksheet) doesn't automatically update. I would like it to automatically update, just the same way it would if it were a built-in function like AVERAGE or SUMPRODUCT or STDEV.
Why Won't Files Close
I am using Excel 2003 in a Windows XP environment. No other workbooks are open.
I have a macro that runs in Workbook(1). It will determine a list of Excel files that exist in a specified directory. For each file found it will;
1. Open the file ( Workbook(2) )
2. Perform a bunch of stuff
3. Saves the file under a new directory/name.
4. Closes the file opened in step 1.
5. Deletes the file referenced in step 1.
Steps 1 through 3 are working fine. What I am seeing is the files opened in the various step 1ís are not closing and eventually Excel runs out of resources. When I look under the Window menu, I see them all active in the list. I think when I saved the files in step 3, it created a new Workbook in the collection and the indexing at step 4 is hosed, but do not know for sure.
I should be using for effective actions on steps 4 and 5?
On Error GoTo lblRHXLProblem
'Open the file without updating
Workbooks.Open Filename:=vRHXLFileName, UpdateLinks:=0
'Determine the file based information
Text Won't Wrap
I have a worksheet where i need three columns at fixed widths and in one of these columns i need the text to wrap. I have a macro and have included a wrap text function in it but it doesn't seem to wrap text!! No cells are merged and the row height has not been altered from the default height. I would very much appreciate if one of you could look at the macro(pasted below) and tell me why the text won't wrap. I have looked through forums and found answers to this question, but none of the solutions I have come across seem to work
Macros Won't Work With Decimals
I've been trying to create an excel macro to calculate shortest route path using floyd algorithm..
Problem is, i can't figure out why this macro won't work with decimal numbers..
I'm pretty sure there's a VERY simple explanation for this..
Probably has something to do with excel's formating
See the attachment and press Run button, it won't produce the correct result, but if if you multiply all the numbers in "input" sheet by 100.
Cell Formatting Won't Update
My problem is the data and getting the formula's to read it. I copied and pasted the data from a pivot table and the columns are set to accounting two digits. But thats not what
shows in the cell. Each cell shows many digits. The formulas are working
now if I put the curser in the cell and select return. The values then
update to the accounting format it is supposed to be already and everything
Three Little Words Won't Change To All CAPS
I was handed an Excel Spreadsheet containing a list of names. Within the sheet there are three names (Elizabeth, Amy, and Daniel) that contain upper and lower case letters. The desire is that all names are in CAPS. However, these three names refuse to remain as CAPS when overwritten in the cell. As a note, other names can be typed in those cells and will successfully remain as typed, either U/L case or all CAPS. Another note, if the Replace feature is used, the names remain as Replaced.
Macro Won't Update Pivots
I have a macro that is supposed to update 3 pivot tables with the change to just the first one but it doesn't work. Can anyone help?
Here is my code but nothing happens when I change the first pt (C & S). It doesn't update the other 3 tables.
Private Sub Worksheet_Calculate()
Dim PF1 As PivotField
Dim PF2 As PivotField
Dim PF3 As PivotField
Dim PF4 As PivotField
Dim x As String
Application.EnableEvents = False
Application.ScreenUpdating = False
Set PF1 = ActiveSheet.PivotTables("PivotTable4").PageFields("State")
Set PF2 = ActiveSheet.PivotTables("PivotTable3").PageFields("State")
Set PF3 = ActiveSheet.PivotTables("PivotTable2").PageFields("State")
Set PF4 = ActiveSheet.PivotTables("PivotTable1").PageFields("State")
x = PF1.CurrentPage
PF2.CurrentPage = x
PF3.CurrentPage = x
PF4.CurrentPage = x
Application.ScreenUpdating = True
Application.EnableEvents = True
Pivot Table 4 = C & S this is one I want to make the change to
Pivot Table 3 = Sales Pivot
Pivot Table 2 = 2005 Pivot
Pivot Table 1 = SMG Pivot
Transpose Won't Work, Perhaps A Macro Will
I am sent an email that contains multiple blocks of information.
They're broken down like this:
There are about 100 of these blocks of text in the email, and some of them are duplicates. What I'd like to do is copy them into Excel, and run a macro which separates them into the appropriate column and removes the duplicates. The problem that I'm running into is some of the blocks have a value (highlighted in red above) and others don't have anything at all. I'm looking for a solution that will be able to evaluate the text and if the cell after Status isn't value, I'd like it to insert a blank cell, a cell that says ignore, or something that will keep the format correct.
Number Field Won't Accept Zero
I have a cell tha't set up as a number format with the validation only accepting 3 whole number characters, however if the number is 098 then it converts it to 98. I need for that cell to be a number type format and to accept 0 as the first character. I thought I tried everything but I'm sure I'm missing something.
My Macro Won't Run For Other People
It runs fine for me, but when others (who are using Excel 2003) try to run it, it won't fire. The second macro entitled "Delete Foothill" runs fine though. "Run Foothill won't fire.
Sheets("Foothill Plaza (Weekly Call)").Unprotect Password:="Foothill"
' Run_Foothill Macro
Sheets("Foothill Plaza (Weekly Call)").Select
Application.CutCopyMode = False
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
Looking Up Data That A Lookup Function Won't!
I have store numbers and store volume groups (one letter indicating their sales volume). This is sorted in somewhat of a table - the volume groups are in row 4 going horizontal, the store numbers are under their respective volume groups going vertical.
I have all store numbers listed vertically on another sheet, starting in A2. I want to search for the store number in A2 within the volume group data and return the appropriate group header when it's found.
Why Won't This Write My Text File
I have verified that there is data in the worksheet...but while the text file is created and properly closed, nothing is written to it.
Sub ScoresToDeko() 'writes a text file
Dim Scores As String
Dim CRLF As String
Dim count As Integer
Scores = ""
CRLF = Chr$(13) & Chr$(10)
Columns Won't Stay Formatted
I have Excel 2008 for Mac (vers 12.0). On a basic spreadsheet, the first column is for date which I have formatted so I can type in 15/8/9 and it appears as 15-Aug-09.
However, each time I open Excel after having Quit it, I have to reformat the cells in this column again as they revert to converting my date input with something like 39,123.
My other columns are formatted to currency and are always okay.
Personal.xls Won't Open
i can't get personal.xls to open when excel opens, i have then file both in xlstart map in the path's
C:Documents and SettingsjohoApplication DataMicrosoftExcelXLSTART
where "joho" is my account. i think my problem started when i added some addins
Vbmodeless Form Won't Show Sometimes
It's a basic data entry kinda thing, some data is entered, about 10 popup boxes pop up, in sequence of choosing different buttons that assign data to cells.... but I have a 'limbobutton', to keep the vba running, since it runs off of workbook open... and the limbobutton works fine, except after i go through the 'add data' procedure I spoke of above.... after I enter data, and get back to the main menu, then choose the 'limbobutton' for the floating button, so a user can manually edit/review the data-- and the 'limbobutton' just flashes up for a brief second, and the vba just stops, and you're at the excel sheet, like it was meant to end...
I'm wondering if there's any sort of conflict that might be occurring here... I know it sounds like I have an extra 'unload me', but I've checked, and still have yet to find something.... If anyone might have an idea how to fix this, or is there perhaps a better way then vbmodeless... I'm just looking to have a floating button that says 'Return to the main menu', so after manually entering data, or reviewing it, a user can return to the 'main menu'...
Chart Won't Plot Dates
I have some weekly data that I need to produce a graph from (100% Stacked Column). Each time I create the graph using the dates as the x axis i only get a thin line which looks like it represents one day, the other space looks like it is for the other 6 days of the week. I know that you could add an apostrophe to the beginning of the date but this is to be an automated report.
Deleted Toolbar Won't Delete
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.
Emails Won't Get Received Time
This code has worked for me about 6 months now, and I havent done ANY changes at all to it. Can any of you guys think of a reason why it wont work?
The following is just en excerpt of the entire code, but the only thing thats relevant...
Checkbox Won't Connect To Linkedcell
I've created a button on a sheet that triggers the creation of a checkbox on the ActiveCell. I also want the checkbox to link to the activecell in order to use the TRUE or FALSE values in an AutoFilter. I used the following code, but the checkbox doesn't link to the ActiveCell. I thought it might be because the LinkedCell = "TEXT" whereas a Range isn't text?
Dim MyRange As Range
Dim CBLeft As String, CBTop As String, ctlName As String
Dim cbObj As OLEObject
Dim MyDoc As Worksheet
Set MyRange = ActiveCell
CBLeft = CallByName(MyRange, "left", VbGet) + 0.25 * CallByName(MyRange, "Width", VbGet)
CBTop = CallByName(MyRange, "Top", VbGet)...........................
Sumifs Won't Work For Multiple Tabs
Ok here is my dilemma, I am trying to calculate gains and losses by organization. I created a sumifs formula that works on a single page, but it doesn't work on multiple tabs on my summary page.
Here is the data:
Here is the formula on the same page as data:
Why Won't This Selected Range Copy To Another Sheet?
I have a button on the Players worksheet that has this
Why would it not work?
Range("NewData").Range("B5:B" & Cells(Rows.Count, "B").End(xlUp).Row).Copy
I am trying to copy Column B starting at B5 down to the last used cell in column B. into the players sheet starting at cell a3.
I try it and it highlights 4 cells and that is it???
Formatting Won't Apply To My Cell With A Formula
I downloaded the checkbook register from Microsoft.com and am trying to add a
few cells at the end to quickly calculate my outstanding debits and credits
so that I can balance my statements automatically. I figured out how to write
the formula properly (eventually), but the cells will not take on the
currency format no matter what I do. I have tried pasting the format from the
other cells within the register, tried clearing the format and reapplying the
currency format, tried accounting, etc.
Two Formulas Repeating That Won't Fill Properly
Here is my dilemma, I have two formulas that reference another sheet, my example shows it more clearly...
Is there a macro or a formula I can use on the remaining 4000 rows that will keep this pattern while maintaining the formulas above?
Double Condition (where Sumproduct Won't Work)
my table is something like below:
my program sheet looks something like this:
I need a double condition formula to check the date, if matches the date Column (A), and matches product Column (b), then returns Column (C) which is the doc#.
I Could easily do it if it is a sum product formula, but in this case, it is alpha numberic.
date: 14 Jun (dd / mm)
product -- Doc#
apple ---- ac21 (so what formula do I put here?)
Orange --- abc2 (formula?)
date --- product --- Doc#
12 Jun -- apple ----- abc1
12 Jun -- Orange --- ab12
14 JUn -- Apple ----- ab21
14 Jun -- Orange ---- abc2
IF Statement Won't Work As I Have Exceeded The Limits
I have been trying to do an if statement but need to choose between 10 options:
In Column AA I could have any one of the following 10 text entries - In column AB I need to return 8, 4, 2, 1 or 0.5 depending on which is in AA (this column AA cannot be sorted nor are people willing to cut back to 5 options )
"Very High" returns 8
"Very High (>50)" returns 8
"High" returns 4
"High (>20)"returns 4
"Medium" returns 2
"Medium (>10)" returns2
"Low" returns 1
"Low (>5)" returns 1
"Very Low" returns 0.5
"Very Low (<1)" returns 0.5
naturally the following IF statement won't work as I have exceeded the limits
Formula Won't Calculate When Only Some Of The Three Variables Are Input
In column H: =IF(ISERROR(L4-J4-K4),"",IF(OR(K4="0", K4=""), L4-J4, L4-J4-K4))
So, if formula is going to come up with an error, I don't need to know. If it's not an error: L - J - K. Except that sometimes there isn't information in J or K, and the formula won't calcluate L - 0 - 0, unless I physically enter in zeros.
=IF(ISERROR(L4-J4-K4),"",IF(K4=""), L4-J4, L4-J4-K4))
I don't want the people using this to have to enter in a plethora of zeros in order to force the calculation. What am I doing wrong? I've attached workbook with the relevant information in it. You can see in the middle that if there is nothing entered, it won't work right.
Some Cells Won't Acknowledge Special Characters
I am using Excel 2002 SP3. I have a multiple worksheet workbook and am using links between some cells so as to keep the updating down to a minimum. Typically I can enter [code]=' Storage '!E70[code] to transfer the contents of one cell in the ' Storage ' worksheet to the cell I want the data in and I get the value of that worksheet/cell. But sometimes all I get is the data I entered: ie ='Storage '!E70. Is there some for cell format problem that prevents this from happening?
Formula Won't Calculate On Specific Computer
We use a spreadsheet to log all transactions at our front desk. I've made a column that automatically calculates the time when a transaction is input into a row. The formula is as follows:
This morning that formula stopped working on the computer at the front desk, returning a message about a circular reference. I can open that same spreadsheet on my computer as well as others in the office and it calculates fine, but of course on the one computer on which I need it to work, it won't ;P
All computers are using Excel 97. Presumably a setting has changed on the front desk computer that's affecting this calculation.
Macro To Make Pivot Table Won't Work Twice
Our system can create an SLK file showing customer open orders. It is ALWAYS in the exact same format. I created a macro to open this file, manipulate the data (convert text to numbers, convert text date to actual date, and a few other things) and then make a pivot table.
When I try to run it it gets hung up on the pivot table and the debug highlights the BOLD below:
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"SOLIST!C6:C12").CreatePivotTable TableDestination:="", TableName:= _
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
The RowField, ColumnField, and Page Fields are correct.
The QTY is the data that goes in the field.
Sum Won't Update After VBA Enters Values
I've got a user form that enters values from a text box into one of the spread sheet columns and a Sum at the top which is not updating when the value is added into the column. After highlighting the cell and pressing return it will update the sum though
I've checked that auto calculation is on and that all cells involved are the same format, I even made up a basic form to simulate the same situation in another workbook and that actually works. Is there any way and code could be causing this trouble? or maybe just a corrupt workbook for some reason?
Legacy Vba App Won't Run In 2003
My old Excel app which uses the Kodak ImgEdit control runs fine in Off97,2000, & XP/2002, but the VBA code breaks in XL2003. Even some normal VBA code, like direct sheet references such as
kills execution, whereas
Dim sDat As worksheet
Set sDat = Worksheets("Data")
seems to work - a behaviour rather like a bad/missing VBA reference.
Only the ImgEdit ocx is used, and seems to register correctly (needs imgcmn & oieng400 dlls) using Regsvr32. The ocx is on a worksheet, not a form. And a side-effect is the Excel is using 90% cpu time while this workbook is loaded, although all vba is stopped/reset.
The only clue I've found is that starting a new project and trying to drop ImgEdit onto a 2003 sheet returns a "Cannot insert object" error, while trying to drop it onto a form returns a "The subject is not trusted for the specified action" error.
Or there a document detailing the differences between 2002 & 2003?
Workbook Won't Close Correctly And Keeps Re-opening On The Refresh Event
I'm trying to combine 2 sets of code that I have searched other threads for.Both use the OnTime code to trigger events after 5 and 10sec.
My problem is that the workbook won't close correctly and keeps re-opening on the refresh event. I understand both events need to be passed to the dTime variable, but I am obviously doing something wrong. I will include the code as I'm sure its something obvious;
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnTime dTime, "Sort", , False
If Cancel = False Then Application.OnTime dTime, "RefreshIt", , False
On Error Goto 0
Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:10:00"), "Sort"
Public dTime As Date
dTime = Time + TimeValue("00:00:10")
Application.OnTime dTime, "Sort"
Selection.Sort Key1:=Range("I6"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
dTime = Time + TimeValue("00:00:05")
Application.OnTime dTime, "RefreshIt"
Any help would be appreciated!