Posts by mooreydp

    Re: Validation Of Data On Worksheet_change Event

    Hi Warrior,

    First and foremost mate. I'd remove the on error resume next statement so that when you do have an error you can isolate where it occurs.

    Obviously your problem is if the vlookup returns an error you don't want it to debug. therefore try using the vba find function instead: (I'd suggest wrapping it into your own function for later use as this becomes extremely handy)


    Now as for point 1. the userform opens regardless due to your On Error Resume Next statement not controlling the error thrown by your vlookup formula. as mentioned above try the find function the way i have shown and you will get much better results.

    With regards to point 2. You haven't declared textbox1 as Userform1.Textbox1 and again due to your error handling it is just running on through.

    hope this helps.

    My tip is always remove On Error resume Next statements. They cause way too much hassle and i guarantee that you can get by without them provided you learn a little bit more about what you are trying to return from various functions.

    Re: VBcomponent: Remove Before Close

    I had the same issue. It seems to surround the internal disposal method of Excel. Thus it needs to action the disposal event in its own time rather then complete the event immediately when the .Remove method is executed.

    I wouldn't be surprised if this relates to the issues surrounding the .Net disposal methods either. You can trigger them as and when you wish but they don't necessarily remove the objects from memory immediately after you execute the disposal method.

    I'm curious if anyone has this problem whether they have found my solution to work. As I mentioned earlier, I pinpointed the EnableEvents method because this seemed to resolve my problem. I would be very interested if anyone has any feed back as I'm interested to know why it works in some scenarios and not in others.


    Re: VBcomponent: Remove Before Close

    Hi Tom,

    As they say a picture paints a thousand words. I've attached an example of my workbook with the form removed. (I removed the form because the workbook was 100+ KB) I'll attach the userform file separately in the next post.

    I have tested this on a project that I was having problems with and it worked seemlessly. I pinpointed the EnableEvents property because this was the final step i had to include before it would work, but I guess it could also be down to the order of steps.

    Let me know how you get on and whether or not there are any further questions.



    Re: VBcomponent remove before exit

    I realise this thread is quite old, but as i just encountered a similar problem I thought i'd identifiy my resolution.

    I was trying to export and remove all components from an old workbook and replace them with components from an up-to-date workbook. Quite frequently i would remove a component but excel wouldn't release it.

    I determined that it was Excel level events which were holding up the process and DoEvents had little to no effect. My resolution was to set the Application.EnableEvents property to False prior to any Export and Removal code.

    For now this seems to have addressed my problem. The code in my project is quite extensive but if anyone is suffering the same problem with VbComponents.Remove then feel free to Reply and I will try posting it.

    Kind Regards


    Re: Import Data - Source Path

    Hi mate,

    I'm of to bed shortly, but i'd say that there is some data to the right of your query or your query is pulling in more data then there is columns.

    Try deleting some of the data to the right of your query or insert the imported data into a new worksheet

    Hope this helps.


    PS If that doesn't help i'll check it in the morning. Cheers mate

    Re: A Survey Of Spreadsheet Users

    Hi guys,

    I'm a little late coming into this discussion as i've been off the forums for some time but I thought this may interest you on the productivity side.

    For the last 2 years i have been working for one of the major asset management banking programs in the UK. When i first arrived everything was done rather manually and tonnes of the work was extrememly repetitive. I found that some of the core systems were extremely limited in their ability to process "non standard" data and as a result i was brought on to develop flexible and tactical business solutions.

    I have seen time frames reduced from several hours a day to mere minutes, data has been controlled with data interrogation import routines and data interrogation export routines. We have now linked Excel VBA apps into our core systems and have enforced strict control on version control and backup regimes etc. What i have seen in the last 2 years is that Excel USED PROPERLY can out compete more than 90% of the stand alone solutions in the market place, and what's better - nearly every business has it installed and ready to go.

    As for the critics - the next guys that says "Excel isn't robust enough" and points me in the direction of a C# .Net application developed by an IT company with absolutely no business knowledge might want to reconsider what he calls robust. (That coming from a guy who's latest hobby is C# .NET : D )

    Being able to teach users how to control and maintain an application can be risky but it can also be worth its weight in gold.


    PS. Carl i'd be happy to offer examples of costs saved, I think you would be amazed.

    Kind Regards

    Re: Conditional Locking Of Cells Based On Dropdown Value

    Hi Kartick,

    I'm just about to head off but this should get you out of trouble.

    If you insert the following code into the code section of Sheet1 as it appears in your example workbook then it should get you out of trouble.

    Please note that as it protects your sheet to ensure the locked cells are validated then you may wish to unlock all the cells in your validation list so you can change them back and forth as it suits.

    I hope this helps mate


    Re: Import Data - Source Path

    Hi Luke,

    As i don't know what your file is etc I can only hope that it is the error i picked up on in your destination code.

    Note that the address property is required because you are trying to parse a range object to an existing range object when it actually requires a string address.

    Let me know if this isn't the case. But i tried an example of my own and that is where it fell over.

    Hope this helps


    Re: Range In An If Then Statement

    Hi Syntinal,

    I'm not sure if this is exactly what you want but if you wish to check the value of each cell in the range then you probably need to do something like this:

    I hope this helps


    Re: Run Time Error 9: Subscribt out of range

    gday helmekki,

    check that the workbook you are searching in is the active workbook. And also ensure that the sheet name is actually "sheet3".

    Subscript out of range usually means that you can't gain focus to the object you are searching. Hence it is out of range (you can't get hold of it)

    Most people would prefer to use the code name of the sheet they are searching so that it isn't potentially changed by the user so in this case you might want to check the code name and refer to it like Sheet3.Range("A3:A3000").Find etc. NB. The code name can be changed by selecting the properties of each sheet in the VBE.

    Hope this helps

    Re: Code to import textfiles

    Hey Rob,

    If you replace the following code segment

    sFileName = Application.GetOpenFilename("Text Files (*.txt),*.txt", , "Please select the file to import...")


    sFileName = "result" & iLoop + 44 & "kenmerk10.txt"

    Then you will get the following file names in your consecutive loops.

    result45kenmerk10.txt; result46kenmerk10.txt; result47kenmerk10.txt
    result48kenmerk10.txt; result49kenmerk10.txt

    Additionally you might need to enter a file path in front of the text file name e.g.

    "C:\Temp\result" & iLoop + 44 & "kenmerk10.txt"

    Hope this is more helpful

    Re: Code to import textfiles

    Hey mate,

    This isn't perfect because i don't know your file names and i'm having one hell of a monday, but hopefully this can help you get unstuck.

    Let me know if it makes sense, and if u want u can send me a private message and we'll sort it out.

    Hope this helps

    Re: Code to import textfiles

    Hi Rob,

    Can you answer a few extra details such as:

    1. Will the five different filenames remain the same?
    2. Do you want the details appended on top of each other?
    3. Is there predefined structures for the text files e.g. 5 columns each with text etc?
    4. Do you have to use the external data method or would you be just as happy to have a text stream reader to read directly from the text files and write it dynamically to the sheet?

    The positive is that there are a lot of different ways you can accomplish this task, we just need a few extra details.

    I'll try my best to explain all steps.



    Re: vba macro (should be easy)

    It's a first for me with my three finger pecking habits : D

    But if there is one thing better than good advice it has to be the same good advice from two different sources

    Re: vba macro (should be easy)

    hi sartorius,

    you could probably use something like this:

    Please note that you can't undo macros, so it may not be the best solution to overwrite values unless you absolutely have to. an alternative would be to use an If function to determine whether the cell concerned is 0 or not.


    Re: hiding an error with if(iserr(...))

    Hey mate,

    You're not actually capturing the error in its first instance. You need to capture the initial instance of C10/B10 with another IsErr statement.

    The error you are getting is a result of C10/B10>=1 not Iserr(C10/B10)

    therefore you should write


    Hope this helps