Posts by Russco

    Hi all, have a combobox called (combobox1) on a worksheet. (sheet2) Linked cell is D4
    My issue is that I have code in lost focus event which is bugging out because when user makes selection from combobox using dropdown arrow then using mouse tries to move to another sheet. My codes assumes they would tab down to next cell on same sheet. What I want is a way for selection to be made in combobox and automatically move focus away from combobox to cell D5, then if user tries to move to another sheet the code in lostfocus event has triggered, which it does nicely when they click into cell D5 now.


    Hope this is clear, any help greatly appreciated.


    Russ

    Hi again, have an active word document which I can populate from an excel userform. What I can't fix is my bookmark called "CS1" is inserted above a fixed bullet point formatted paragraph. I want the bookmark text to also look bullet pointed see example end result below: Bulletpoint1 is the bookmarktext. Bullet point 2 is fixed in my template. Any help would be greatly appreciated.
    [h=2]Your Current Situation[/h]
    You are currently a member of the Grosvenor KiwiSaver Scheme.
    · You are in full-time employment, earning a gross salary of $55,500 and are committed to contribute of your gross salary, with your employer contributing an additional .
    · While your main aim is to save for your retirement, you may wish to use some of your KiwiSaver funds to help with a house deposit after having been enrolled in KiwiSaver for at least three years.


    Regards


    Russ

    Hi all, have looked at several sites and nearly have what I need. Hope for additional help as follows:
    I have an excel workbook with a data entry userform called frmpersonaldata that my users then click submit button and my code should and does find an existing word template and open a new "document1". However this is where my code falls down. While this new version of the template has opened I can't then seem to write code that will transfer userform fields to existing bookmarks in document1. So simple example field called me.textbox4.value should be placed into existing bookmark called "ADD1"
    What I can't seem to make work is this code which bugs out with object not set....


    Hope someone can help show me how to write to the new activedocument ???


    Regards
    Russ

    Re: Userform dynamically add combo box list items


    Hi Roy, when userform initialises I use addi item method as this userform is linked to a word template file for populating new document. So I don't want to use external source. I was hoping there was a way to update initialise sub? Is there a way to create an array? And store this upon closing the form?

    Hi all, would like to know if it is possible to allow user on a userform to add item to combobox list which when form is closed and re-opened will have these new listitems showing. I have a commandbutton which has the following code to add items to combobox.

    Code
    Private Sub cmdAddAdv_Click()Me.cboAdvList.AddItem Me.cboAdvList.Text
    End Sub


    Currently when I close this userform and re-open the userform_initialise sub adds existing list but new items are not shown, is it possible to do this please.


    thanks russ

    Re: clean csv file fix date formating


    Quote from jindon;724346

    Then I need to see your csv file.


    Upload as .txt file, the forum doesn't accept .csv file.

    I will do that tomorrow as my file is at work. Much appreciated jindon.

    Re: clean csv file fix date formating


    Hi jindon, I'm sorry but I don't understand your reply. This code cleans and saves a csv file just fine. But when I then open it using notepad the dates are displayed in mm/dd/yyyy even though the original file has correct date format dd/mm/yyyy. I know how to manually clean but really want the file cleaned using your code to maintain date format. Could you clarify what I need to do please.

    Hi all, have previously "borrowed" this code which cleans an existing csv file prior to import to a payroll system. My remaining issue is that the code seems to change the dates from dd/MMM/YYYY format to american format mm/dd/yyyy. This is a major problem. 11/8/2014 (11 August) becomes 8th November in payroll system.
    Can anyone help me with a way to modify this code (see below) so that date format is preserved.


    Any help with this would be very much appreciated.

    Re: Clear hidden commas in CSV File for import


    Gentlemen, your help very much appreciated. Jindon, I used your code and it does what I requested thankyou. Without imposing too much more, could this code be adapted slightly to automatically find the specified filename (.csv) in specified directory and run this cleansing sub. I know how to create the initial unclean csv, so ideally would like my whole sub to then find this unclean file and cleanse it using Jindon's code.
    Appreciate any further guidance on this please.


    Regards


    Russ

    Hi all please see attached file which I could not upload as a csv file type?. This file is created from a larger workbook. When I try importing this file(as a csv type file) into a payroll application it rejects because of unwanted hidden commas appearing after the first few lines with just four columns:


    #50,BMac,,149.33,,21.67,,,,



    The commas after 21.67 are caused because of the longer lines below starting with #60 etc.(7 lines)


    When I open the csv file with notepad it is possible to see these hidden commas. While I know how to cleanse manually, I'd like to automate this process. Can anyone help me with vba code to automate the clearing of these unwanted commas so the new csv file can be uploaded to payroll system. If the code could firstly clear commas and then also create new clean csv file, that would be my ultimate result.


    Any help appreciated.

    Re: FIND MATCH then Provide Result of Adjacent Cell in MsgBox


    Hi Chris, our time zone difference, so sorry for delay. NZST now 12 noon Saturday 22.
    I attach variation of my last file, because the original formula does not cope with the blank cells issue in column H on your TO SHEET.
    What I have done is remove data validation drop down from J3 as you indicate this will be populated in another way.
    The 2 macros in module1 must both be copied into a module on your file. The advanced filter criteria section on TO sheet range currently J2:M4 must be included on your file but can be placed anywhere say column z1 BUT you must ensure that you change the references in the macro called AdvFilter so Criteria Range and CopytoRange match your chosen location. I have put comments in macros indicating where you need to change. The result will now appear in cell M3 on BOM Worksheet sheet. I have kept msgbox functionality but added comments if no part code found at all.


    I assume on the TO sheet your database still starts B7 Parts Header & H7 Code Header
    Hope this works for you.

    Re: FIND MATCH then Provide Result of Adjacent Cell in MsgBox


    I see the gif image. You say that an existing toolbar button populates J3 with required PartNo. If you attach my MatchPart sub to one of your custom toolbar buttons and remove data validation from J3 it should do what you want I think ? What version of excel are you using ?

    Re: FIND MATCH then Provide Result of Adjacent Cell in Msg Box <<<


    Hi Chris, when you say you pasted my formula into J3 ? which formula, my index match formula is in cell M3. and I called this cell "Answer"
    Chris this is how your formula should look, note absolute range references and apostrophes before and after BOM Worksheet
    =INDEX(TO!$B$8:$H$500,MATCH('BOM Worksheet'!$J$3,TO!$B$8:$B$500,0),7)
    Cell J3 should have a data validation list (creating the drop-down) to simplify user selection of part no. based on named range called PartsList so check Formulas>Name Manager on my sample file and replicate these then see how it works.


    Any questions ask.


    Russ