Posts by twowheeltoad

    Re: Text File Import With Text To Columns Splitting Same Text Differently

    Quote from Dave Hawley

    That is an euphemism for "I'm not sure"

    What I meant is that I know that the bulk of the data is identical, I'm just wondering whether the jobs in their mainframe accounts still pull an extra column of data at the end. I'm not sure if that would affect the Text to Columns split of the first 10 columns though. Maybe it would. Only one way to find out...

    Won't be able to do that until tomorrow though (well today now!)...

    Re: Field Info Parameters In Vba Different For Each User

    Mavyak - Thanks for your response. Could you explain your solution a little further for me, please?

    Hi Dave. To the best of my knowledge the raw data is the same, however, you asking that question has prompted me to check one thing when I get to the office tomorrow. You may have inspired me! Watch this space. Thanks.

    OOPS! Just realised that I missed the last sentence of your post, Dave. Users... hmmm... don't you just hate 'em?!

    Hi everyone.

    I have a macro which imports data from a mainframe dump text file and performs 'Text to Columns' on the imported data so that formulae in the spreadsheet can act on the data.

    The code works perfectly well when I use it, but if a different user logs on and performs exactly the same mainframe dump and import macro the Text to Columns action splits the raw data in a different way and the result is that the split renders the formulae useless.

    I've experimented a little and for some reason it appears that the 'Field Info' parameters which are produced when the Text to Columns function is recorded in a macro differ between users even though the raw data is exactly the same.

    FieldInfo:= _
            Array(Array(0, 1), Array(18, 1), Array(35, 1), Array(56, 1), Array(70, 1), Array(88, 1), _
            Array(102, 1))

    Can anyone tell me why this might be, or offer any advice to get around the problem?

    Many thanks,


    Re: Keyboard Shortcut for VBA Added Menu?

    Hey, Jack, Mate... you know in my original post, when I said that the solution would probably be a one-liner... well it was a one character solution... the ampersand! DAMN!!! Unbelievable! Sooo simple!

    Thanks for showing me the way. Thanks also to you Shades.

    Jack - I really wasn't expecting to see the second example that you posted. Not had chance to look at it yet, but I WILL play with it later.

    Thanks again guys.


    Re: Keyboard Shortcut for VBA Added Menu?

    Thanks for the quick response Shades.

    Attached is the code I'm trying to add the shortcut to. Unfortunately I don't seem to be able to get it to work.

    The above code is modified from some that I found on the forum (thanks to whoever it was) but I'm not sure if I can slot any of the code that you just suggested into this procedure or not.

    I thought that (& bear in mind that my VBA knowledge is still VERY limited) I could slot the

    .ShortcutText = "Alt+R"

    into my procedure below the

    cbcCustomMenu.Caption = "Reports"

    but it doesn't like me to do that. HELP says that 'Object doesn't support this property or method (Error 438)'

    I know I'm probably being a little dense but could you please advise where I would have to insert the code to make it work with mine?

    Many thanks,


    Hi All.

    I've done several searches for the answer to this but haven't found a solution. I'm sure there must be one though... I think...

    I've created a menu driven navigation system in a quite a large workbook and am trying to create a keyboard shortcut Alt + R to the VBA added menu.

    I bet the solution's a one-liner...

    Thanks in advance,


    Re: VBA - Removing 'Blank' Custom Menus


    Prior to creating the drop-down menu system I was using a command bar for navigation. Now, when I had this command bar floating it had a title but when I fix it to the bottom the title disappears. Is there a way to have the command bar title visible on a fixed command bar?

    I just wondered if this was possible?

    Unfortunately I have to sleep now as I have to be up again in a few hours. I'll pick up any further responses in the morning.

    Thanks again.


    Re: VBA - Removing 'Blank' Custom Menus

    Well I'll be damned!!! I thought that as they'd been created (inadvertantly) by VBA that they would have to be removed by VBA too. As usual Dave, you make everything sooo easy. Thank you.

    Jack - I was just sorting out some code to post, so you could see how they'd been created, when I read Dave's reply and sorted the problem. Thanks for your offer of help though - it's appreciated.

    I'd like to keep this thread active for a while with another question if I may...

    Prior to creating the drop-down menu system I was using a command bar for navigation. Now, when I had this command bar floating it had a title but when I fix it to the bottom the title disappears. Is there a way to have the command bar title visible on a fixed command bar?



    Hello Everyone.

    I'm continuing my VBA learning experience but it's slow going.

    I've created a navigation system in a large workbook but in doing so I've managed to create 2 'blank' custom menus next to the Help menu. Can anyone help me to remove these? I have some code to remove menus but as these are 'blank' I do not know what to refer to them as.




    Please see the attached link to a file I've been working with;

    The question I have, & I'll try to be brief, is...

    When a place is selected from the drop-down list in the yellow cell the list is populated with information.

    This information, for the purpose of this experiment, has been altered to protect the innocent.

    When the list is populated the cells beneath the Days contain colours in text. VBA conditional formatting should then change the colour of the cell to match the colour in the text. This works beautifully in the Excel application itself but for some reason this version, uploaded to the Internet, will not automatically change the fill colour of the cells after populating the list. You have to manually calculate (F9) the file for it to happen. Try it and see...

    Now, I don't have much experience of Excel on the Web so could someone tell me why this happens, or, more to the point, doesn't happen?

    Many thanks,


    P.S. Not quite as brief as I expected. :roll:

    Re: 97 to 2000 Corporate Migration.

    You do make me chuckle sometimes Jack!

    I wish I worked at a place like yours - it's no wonder you know so much dodgy stuff!!! :lol:

    Thanks for all the advice.

    Actually, there was something else I wanted to talk to you about, but I can't remember what at the moment. Well I have been up since 04:30! :yawn: It was something to do with some old posts that I'd been reading. I'll find it again tomorrow and get back to you.

    Re: 97 to 2000 Corporate Migration.

    I don't actually know anything about the simultaneous migration to different versions. What I meant was that it's inkeeping with the way the IT people seem to work where I am - serious lack of logic! Come to think of it most other areas of the company are run that way too. Must be some cunning business strategy that I haven't yet got to grips with!

    Re the H drive: I understand what it is, but I wondered why you said it will be adversly affected by the transition?

    Re: Sumproduct formula

    Damn you Derk!!! I was just in the middle of typing a response and came back to refer to the original post and saw that you had beaten me to it.

    I was struggling to word what I wanted to say anyway, whereas your response was pretty concise.

    I must learn to be quicker...I must learn to be quicker...

    Re: 97 to 2000 Corporate Migration.

    Norie - thanks for your reply.

    Jack - Don't even get me started on the logic of moving OS to XP and Office to 2000!!!

    What's this about email problems? The company uses Lotus Notes but you mentioned Outlook. Will Lotus Notes be affected?

    & more importantly...what's that about the H drive? Tell me more, please?



    Hello folks.

    My company are currently going through a migration from Windows 98 to Windows XP and during this process they are upgrading from Office 97 to Office 2000.

    Unfortunately I'm out of the office for the next few weeks when the migration is due to take place in my area.

    Someone came to me today and said that the migration from Office 97 to Office 2000 would cause no end of problems with my existing Excel files, which were created in 97. They were effectively warning of total meltdown!

    Now, I'm quite experienced with Excel (not an expert though), but I don't see why the migration from one Office package to a new version should create problems with existing Excel files. I switch between 97 and XP all the time at home without any trouble.

    Am I missing something?

    Should I be worried?

    Has anyone else been through a similar migration?



    Re: hide VBA code

    I have a file containing code that I do not want users messing with.

    Now, we all know that Excel's security isn't very tight but, in this particular instance, sheet and workbook protection with passwords would be enough However, Excel files with workbook / worksheet passwords will not pass through the company email filters.

    The question is...

    Will VBA passwords pass through email filters any easier than workbook / worksheet passwords? I'm pretty sure that the answer will be no, but I thought I'd just try to make sure.