why the following code generates an error if I use the backspace key to clear the textbox?
Code:
Dim t As Date
If Len(Controls.Item("txtstart").Value) = 4 Then
On Error Resume Next
t = TimeSerial(Left(Controls.Item("txtstart").Value, 2), Right(Controls.Item("txtstart").Value, 2), 0)
On Error GoTo 0
ElseIf Len(Controls.Item("txtstart").Value) = 5 Then
On Error Resume Next
[code]....
whether I can handle errors in a way that tells me which object caused the error?
For example if a userform has two combobox's and one causes an error, can I return to a message box containing the name of the object (combobox1 or combobox2)?
I have tried returning err.source but I am either not using it correctly or it isn't intended to be used this way.
My formula to add a row to a worksheet worked fine, except for the fact that it was including items with 0%. I need items with >0% but <=50%.
Here's the code that sorta works...
The IF formula works if placed in a cell, but Vba doesn't seem to like the Quotation Marks in Rc11="".
View 2 Replies View RelatedA large Excel file that I have been working with for months is now giving me trouble.
When I tried to delete 2 rows, it displayed a message: "Excel cannot complete this task with avail resources. Choose less data or close other applications."
I opened a previously saved version of the file and tried deleting rows, and the same message came up.
I shut down the computer, rebooted, same problem.
Any way to determine if a file has been corrupted .... or even worse ... is there a possibility that my Excel program itself is corrupted?
why my code would hit a run time error on a shared file? It works fine unshared. This is on a ActivateSheet Worksheet Event, Code is housed in a Module and called when activated.
[Code]......
why this is causing an error upon opening the file. This workbook has a userform with a listbox (single list, single selection), and when I have the ControlSource property set it causes an error upon opening the userform (which opens when the file is opened), whereas if I do not have the ControlSource property set to anything, it seems to work fine.
Interestingly, if I set the file up to not open the userform with the file, and instead have the userform open when a worksheet control such as a command button is clicked, it doesn't seem to matter if the ControlSource property is set or not. The workbook in question is attached.
I am pulling external data from an Access 2007 database query that uses the nz function. While linking to the query, I get an "Undefined Function nz" error. The query works perfect when run in Access.
View 1 Replies View RelatedI am receiving the following error:
-2147417848 automation error the object invoked has disconnected from its clients
Unfortunately, I can't tell where the error is occurring because everytime it happens excel crashes or at least stops responding. I tried waiting it out (about 20 minutes) but no luck. the only thing I can do is force the program to shut down through the task manager.
The program is designed to pull information from finviz.com (a financial site), filter the data. pull the next round of data and run the filters again. there are a total of 8 queries and i think it gets hung up during the 6th or 7th run through.
Stepping through the code does not seem practical since each query might pull as many as 300 rows of data that is evaluated in a for/next loop (thats a lot of F8s).
I have a spreadsheet which has 3 separate external queries running from worksheet 'A'. My user enters a couple of dates in another worksheet 'B' to supply the date parameters for the query and a macro runs to refresh all of the queries and performs a few calculations.This works fine. The trouble is I don't want the user to be able to view all the data on sheet 'A' only the summary on B. My macro ends up on sheet B but whilst the query is refreshing the raw data is displayed to the user on sheet 'A'. When I hide the sheet 'A' i get a run time error '1004' Select method of worksheet class failed.
View 4 Replies View RelatedI have just signed up to the forums as I am having a very specific problem. I have looked on google and used the search feature (on several forums!) and found very limited information that is useful to me. I am having trouble with a very simple few lines of code in a macro. The code looks like this:
Function startmarketIDs()
Dim targeturl, writerow, readrow, textmass, xmlHTTP
targeturl = "http://lite.betfair.com/ Events.do?s=00010913z"
Set xmlHTTP = CreateObject("Microsoft.xmlHTTP")
xmlHTTP.Open "GET", targeturl, False
xmlHTTP.send
MsgBox xmlHTTP.StatusText
textmass = xmlHTTP.responsetext
MsgBox textmass
End Function
My problem occurs at the line "textmass = xmlHTTP.responsetext". The responsetext command is obviously causing problems because if I remove it, the code executes without error and the "xmlHTTP.statustext" says "OK". With the responsetext command left in, the code generates the following error: Run-time error '-1072896658 (c00ce56e)': System Error: -1072896658. If I change the targeturl to www.betfair.com, the code executes fine and I get a message box (as desired) with the source code of the website displayed. Without meaning to sound like Im answering my own question (!!), perhaps there is something about the url or the way in which I am using the xmlHTTP object that is causing the issue, I wondered if someone would mind inspecting my code and perhaps pointing me in the right direction?
I have the following code to add dash mark to TextBox1 value depending on some condtions (mentioned in the code), the code works perfectly, but when we use Backspace Key to clear some charactors it is conflicting with the code running when changing Textbox1 value.
View 11 Replies View RelatedI currently have this code that uses a textbox to search through the populated listbox and removes any entries not matching the value in the textbox. It works great, but if the backspace key is struck it cannot reload the listbox and narrow down the results again.
View 2 Replies View RelatedI have a piece of code in one of my Macro's, if I run the macro and don't let this piece of code run everything works fine, when I let this piece of code execute the excel file keeps running until I go to save, at which point I get a message saying "Microsoft office excel has encountered a problem and needs to close. We are sorry for any inconvenience".
Code is as follows:
If MsgBox("Copy and hardcode this scenario?", vbYesNo) = vbYes Then
Calculate
Application.DisplayAlerts = False
[Code]....
I'm testing some userform code & it seems to cause some sort of lockup where I can't enter anything into the excel cells. It doesn't just affect the spreadsheet the userform is attached to but any spreadsheet that I have open.
View 5 Replies View RelatedI am creating a line graph to display changes in Cost Per Unit (CPU) of a given item over time.
The issue I have is that the CPU data on the line graph is displayed as 0 values where data is yet to be inputted. Instead I want the graph to remain blank where no data has yet been added. How can this be done?
find attached a simplified mock up of the spreadsheet.
Constant (fixed) values are:
- Date
- Hourly Rate
Values inputted on a daily basis are:
- Hours Worked
- Volume Picked
Calculated values are:
- Total Cost
- Cost Per Unit
- Mean [=average(CPU)]
- UWL [=mean+(2*stdev(CPU))]
- UAL [=mean+(3*stdev(CPU))]
I'm open to using VBA if necessary, however as I will not be the only user of this workbook when it is complete I would prefer to keep it a simple as possible.
I want to say thanks for any help you might be able to offer. Attached are 2 sheets out of a workbook I am using which has many sumproduct formulas. On the the Input sheet, is where vacation and training forecast dates are listed. On the Calcs sheet, there are 2 monthly calendars for the year, 1 for vacation, the other for training. Would like to know if there is a way to have both records show up on only one calendar set. I currently have the vacation shown as a '1' and the training shown as a '9'. The workbook is used in Excel 2003 & 2007.
View 5 Replies View RelatedI am trying to sort a database with about 500,000 names. For some reason, about half my data has leading spaces and half do not. I have taken a screenshot at the divide so you can see what I mean. The sort in the picture is in this order:
Column D
Column C
Column A
Is there any way to mass purge these leading spaces in Column D (column b also has the issue but the fix is not nearly as important as for column D)?
I have an action that deletes a row, and decrements rows counter by one. But, if the user manually deletes one row, I don't get that information (decrementation). Workbook pretection is out of the option.
What are my choices?
I was thinking, is it possible to capture a delete event in general, and add some code to it? Or maybe even completely disable it?
Using Excel 2003, I'm summarizing info in one sheet (Review) with data in another worksheet (Work Orders). The following SUMPRODUCT calc worked fine...
View 2 Replies View RelatedI seem to have a bug i can't quite figure out with my code - its causing excel to crash each time it reaches this line (when its commented out - the form runs fine - otherwise "excel has encountered an error")....
Set frameFullX = frameFull.Controls.Add("Forms.frame.1", "frameFull1")
frameFullX is declared as a control and frameFull is a frame already on the userform.
Anyone know what I might be doing wrong (I'm going to try restarting and see if was just an issue with my machine or not?
My nested loop works as intended without If statements, ie it counts non blank cells in a series of 17 ranges (this loop is called the CountRangesOnEachRow loop) AND then drops down a row and repeats (this loop is called the RowNo loop). The problem is that once my first If statement occurs the RowNo loop fails and it stops at the first row.
The first If statement is designed to do the following:
1. Tests to see if A4 has data. If yes, copy cell contents (pupil's name) to Sheet(2).Range("StudentName")
2. If empty skip the countranges loop and move down a row to A5.
3. Cells A4:A35 need to be tested for data in this manner.
I've included a sample workbook & code with the IF statement comment blocked so you can see what it should do AFTER it tests the A column for data. The macro should end after row 6 as A6:A35 are all blank.
I have got several different workbooks that I use that contain macros that format cells. However, just recently I have had countless problems with the macros telling Excel to format certain cells. There seems to be no logic or order to this. Also, if I then try to format the cells by hand, I get the following error message: "Too many cell formats"
View 3 Replies View RelatedI have a set of userforms, two of which are big with 4 pages and many buttons, labels and textboxes that run or are involved in code being run from user input to the userform.
For some reason certain codes are cauing excel to fully crash, executing these codes will make excel crash each time and the only way to prevent it (that I have found) is to open the userform in VB editor and then close VB editor again, the code will then work perfectly for a while and then it will start crashing again (it is normally a few saves and open/closes later that this occurs).
Logically thinking, opening the userform in VB must reset something that gets corrupted or set incorrectly....
Have a code to clear contents on one of my worksheets and it continually causes excel to freeze and forces me to exit the program. When the button is selected it takes approx. 30-45 seconds to clear the data and then excel freezes up. The code is as follows:
Sub()
Sheets("Sheet1").Range("B2:D16000").ClearContents
End Sub
The data in the range B2:D16000 comes from another macro that copies and pastes values to this sheet. I am also working on the mac 2011 version.
I have a workbook with many lookups, sumproducts, dynamic named ranges and cse formulas. How do I determine what is causing my workbook to be slow? Are there more efficient formula types that I can use?
Sample Formulas: ...
I recently added some command buttons to 6 excel files each with about 20 sheets. most of the sheets got 6 new buttons. After I added the buttons I would get memory errors when I treied to open other files. So I upgraded to Office 2003 and Excel 2003 because I read that it had increased memory available. It did not seem to help much, and now on average about evry say 10 minutes Excel hangs for about 1o seconds, sometimes saying "Not Responding" up the top. Then it starts working again. MY PC has Server 2003 and 1GHz and 512MB of Ram. Here is some info:
=INFO("memavail")1048576
=INFO("memused")50769256
=INFO("totmem") 51817832
=INFO("numfile") 173
=INFO("osversion")Windows (32-bit) NT 5.02
=INFO("release") 11.0
I do not think the PC memory or chip is being stressed it seems to be Excel's memory?
I have a spread sheet that contains many user defined functions from a 3rd party provider that downloads financial data from an external database. Often not all of the data is required by the user, and as the UDF's take a long time to calcuate, i'd like to provide the ability to easily switch them on and off. Originally I had been looping through cells and either adding an apostrophe in front of the = or removing it. However running this loop is very slow.
I found this thread
(Optimize VBA Loop For Inserting Formula)
and adapted it to convert formula strings (with ' in front) to formulas in a single pass by setting:
myRange.formula = myRange.value. This works very well. I can't, however, work out how to go back the other way in a single pass. Have tried: myRange.formula = "'" & myRange.formula. This works for individual cells, but not for more than one at a time.
I have the following formula in 220 cells in a workbook:
=( SUMPRODUCT(('General Journal'!$C$1:$C$44995>=$D$3)*('General Journal'!$C$1:$C$44995<=$E$3)*('General Journal'!$H$1:$H$44995=$A5),'General Journal'!$I$1:$I$44995))+(SUMPRODUCT(('General Journal'!$C$1:$C$44995>=$D$3)*('General Journal'!$C$1:$C$44995<=$E$3)*('General Journal'!$J$1:$J$44995=$A5),'General Journal'!$K$1:$K$44995))+(SUMPRODUCT(('General Journal'!$C$1:$C$44995>=$D$3)*('General Journal'!$C$1:$C$44995<=$E$3)*('General Journal'!$L$1:$L$44995=$A5),'General Journal'!$M$1:$M$44995))
Where D3 is a starting date, E3 is a closing date, and A5 is a sorting code. It looks at my general journal and looks for all the entries between the two dates, and then sums up all the entries linked to the sorting code in A5. The sorting code is in column H, and the amount to sum is in column I, and this is repeated 3 times.
You are already probably signering at how long I must have to wait while excel calculates all these formulas (* 220). I have resorted to a macro that turns caluculation to manual when I open this workbook, and back to auto when it closes.
I have a large file, part of which amongst other things calculates life expectancy from a range of q(x) values (proportion of people that die moving from age (x) to (x+1). Life expectancy is calculated using a user-defined function (below).
My problem is that whenever I run a macro that changes the file, even parts of the file that don't affect the cells using the life function, it jumps into the life function. (An example: copying and pasting values on a different sheet). This is a hassle when stepping through other macros using F8, not to mention the time cost.
Some further possibly necessary information: one macro uses the GoalSeek application to set the target cell that contains the life function
By the way, this didn't use to happen in older versions of a similar file. When running the GoalSeek macro to change target life expectancy it did, but not for any other macro.
Here is the function:
Function life(data As Range)
' Aims to calculate Life expectancy from Qx values
' It assumes first value of Q is Qb, then Q0 to Qmax
Dim Nobs As Integer
Dim j As Integer, i As Integer
Dim q() As Double
Dim L() As Double
Dim T As Double, le As Double
Nobs = data.Rows.Count