Posts by AndrewJ

    Re: Vba, R.i.p?


    "Personally, I've been using Office 12 since beta 1 and I just love it to bits. Given the work that I do is mostly interfacing with SQL databases, the interation with back-end database servers is absulutely superb."


    Awesome! I too, deal with sql databases quite frequently... what specific new stuff have you found thats helped or changed?

    Re: Save And Restore Toolbars On Window Switch Or Close


    I'd love to see someone implement this with custom toolbars and a registery check on startup to make sure they really do get restored (if computer or excel crashes), read Dennis' comment in Aaron's thread I linked to above for what I mean.

    Here's a little improvement on Aaron Blood's save and restore toolbars - http://www.ozgrid.com/forum/showthread.php?t=22076


    This (as it is) will make 'visible = false' every toolbar, the status bar, formula bar, headings, scrollbars, the worksheet menu bar, and workbook tabs (basically everything you can remove) and then restores the default settings either when A) you close the workbook, or B) you switch to another workbook. It will remove all options again when you switch back.


    If you want, add two buttons on a worksheet and link them to the macros turn_on and turn_off and it will remember which one was last clicked and return those settings when you switch back from another excel window. (So if the user makes the toolbars visible, switches to another window and then switches back, they wont be hidden again)


    In a module:





    In 'Thisworkbook' module:


    Re: Winrar of Winzip Attachment Addition


    True, true, the compatibility is an added bonus. Winrar is free though.. at least, it used to be? My copy is anyway, and I got it off their site a while back


    But yeah, space isnt as important as it was even 3 years ago, I just brought this up since Dave has a 45 kb limit on attachments

    Re: Christmas Drawing Names


    Yup, wigi's right. For something as small as what you have it won't really matter but I've noticed some of my macros run up to 5x as fast when I remove the .selection statements.... that and avoiding sheet switching whenever possible

    Re: File Size Reduction


    weird... sounds like you have a lot of formatting or images in there somewhere.. if its just text and data you need A LOT of it to fill 22 megs... try copy pasting your data to a new workbook? Maybe first run it thru notepad or something to get rid of anything invisibly attached? I'm at a loss to be honest... you could always try reinstalling excel too...

    Re: Reading From An External File


    Use the import data function -


    turn on the macro recorder, go to data -> import external data -> import data, and then import your file and it shouldnt go into just one cell, then you can pick each cell individually

    Re: Join Text To Cells


    right, but if you copy and then paste special to the same column it *shouldnt* mess up the references.. and then you can avoid vba, which is good to do when excel has a function for it


    not entirely sure about that tho

    Re: Join Text To Cells


    you could also:


    =CONCATENATE("ABC",A1)


    and fill down


    then, right click on the letter of the column your working in ('A', 'B', etc) that appears on the ruler or gridlines, whatever its called, and it should pop up a menu and select the entire column at once. Select copy, then right click again on the same column (the very top, not the cell) and go to paste special, and choose values

    Re: Exit Cell Event


    bout to leave work, but here's how I was thinking it might look:


    edit: yep just tested it and itll do what you want



    by the way, you can take out the parts where it puts the value of varCelladd into cell(2,10) and the msgbox, thats just to show you how it works

    Re: Exit Cell Event


    Oh, I missed that you wanted it when you exited the cell as well.


    try this: in the selection change sub, have a public variable that records whatever cell was selected (varCellname = activecell.name or it might be .address) then test to see what the variable is set to either on before click or you could probably check it at the beginning of the selection change sub, do your calcs, and then reset the variable to the one selected... itll keep the last selected cell basically