Posts by mje

    Re: VBA: Input box - invalid data entered - type mismatch error


    Will,


    Thanks for the help. I got it to work using your function call. I've never used user created funcntions. Can you please explain to me how this function works? I think I figured out most of it, but I'm not sure how "Like "#" works and "err.clear".


    Another question - is there any way that I can enable macros from within a macro? I'm guessing not, but just thought I'd ask. :)


    Thanks again for your help.


    MJ

    Re: VBA: Input box - invalid data entered - type mismatch error


    Here it is.


    This was my previous post:
    - I'm using an input box to prompt users to enter a column. How can I determine if what they entered is a valid column?


    Will did a great job answering my previous post, now I have another question.


    I can check (thanks to Will!) that the user enters a column valid for the spreadsheet; however, I'm stumped on how to trap if the user enters a totally invalid value for the column prompt. e.g. what if they enter xxx? I currently get "Run-time error '13': Type mismatch".


    I've read a little about On Error, but I'm not sure how to code it.


    TIA...MJ

    I'm using an input box to prompt users to enter a column. How can I determine if what they entered is a valid column?


    In other words, if the spreadsheet contains cols a thru d, I don't want the users to enter e. The number of columns can vary from user to user.


    TIA...MJ

    Re: Reduce size of XL file


    Thanks for all of the quick replys.


    I narrowed it down to 5 rows. I have the 5 rows in a spreadsheet and it is 17.5 mb. If I copy the rows to a new sheet one by one the size goes down to 14kb. If I copy all rows together to a new sheet it's 17.5 mb.


    I got my XL file down to 64k but don't have a clue what caused it to be so large.


    Again...thanks for the help. You guys are great!! Have a good weekend.


    MJ

    I have an XL file that only uses a1:M215. No macros, only one sheet. The file is 17mb. I can't for the life of me see what could be making the file so large.


    Any ideas??


    TIA...MJ

    Re: Auto Open Macro?


    I finally found a fix. I deleted my Excel11.xlb file and created a new one. I'm not sure why this worked, but it did. My assumption is that I must have accidentally made a change to the File/New menu item. Or, it "mysteriously" was corrupted.


    Thanks for you help!


    MJ

    How can I tell if I have an auto open macro? Every time I click File/New in XL I get "The macro 'PERSONAL.XLS!Unprotect_All_Sheets' cannot be found." This is a macro that used to exist but has been deleted.


    TIA...MJ

    Re: Macro executing on XL startup


    RoyUK,


    Sorry, I didn't understand your question. To my knowledge, it is not an Auto_Open module, it is an ordinary module. How do I confirm this??

    Re: Macro executing on XL startup


    The macro is store in my personal.xls: D:\Documents and Settings\my_name\Application Data\Microsoft\Excel\XLSTART\PERSONAL.XLS.


    As I said in my post, it work just fine until a few weeks ago. Nothing has changed on my PC.


    MJ

    When I click on File/New nothing happens. I've narrowed it down to the following macro in personal.xls. I got this macro from this forum in January and all worked great until a few weeks ago. The macro still works fine when I run it from tools; however, suddenly, it appears to run everytime I start XL.


    If I close all opened sheets and then click on File/New I get "Run-time error '1004': Method 'Worksheets' of object'_Global'failed." If I remove the macro from personal.xls and then start XL I get the same error right off the bat.


    In both instances when I debug, the line being executed is "For Each ws in Worksheets".



    Code
    Sub Unprotect_All_Sheets()
        'WillR
        'from OzGrid Forum 1/27/05.  mje
        Dim ws As Worksheet
        For Each ws In Worksheets
            ws.Unprotect
        Next ws
    End Sub


    TIA...MJ

    Re: defined name to multiple sheets?


    That did it. It will be a pain getting cols back in for days of the month, but less time than I spent yesterday trying to figure out the problem!! Thinking back on this, the wb was created from the first monthly sheet and then copied.


    Thanks for your help...greatly appreciated!!


    MJ

    Re: defined name to multiple sheets?


    I'm having the same problem. I've spent half the day trying to figure this out, with no luck. I've got a WB with 13 sheets, 2 thru 13 are named Jan thru Dec. I want $AA$281 to be named "tmo" sheets 2 thru 13. In sheet1, named Monthly Avg, I should be able to sum all sheets with Jan!tmo+Feb!tmo, etc.


    This worked great in Dec when I set it up. There are about a dozen named cells, but I inserted new rows and need a new name and it won't work. The other named cells still work fine.


    TIA...MJ

    This works, but I would like to understand it.


    1) Why is the purpose of using r? set r = ActiveCell, .ScrollRow = r, and, r.Select? I've tried it without and it appears to work ok.
    2) Why use Range("A2").Select? I've tried Range("A1").Select and it doesn't work.


    TIA...MJ

    I have a macro that's been working for a year. Now the file I received is saved with row 33000 at the top of the sheet. My freezepane doesn't work right anymore. It freezes with 33000 as the first row. Here's my code:


    Range("A1").Select
    ActiveWindow.FreezePanes = True


    I mistakenly thought that the range command would make it freeze at row 1...guess I thought wrong!! :)


    How can I make this freeze row 1 instead of freezing the first row viewed?


    TIA...MJ

    I have the following forumula in a cell: "=CONCATENATE(B154," - ",VLOOKUP(B154,LabCat04,2,FALSE), " Actual")". I would like to put the word "Acutal" in bold, but can't figure out how to do it. When I select the word, the "bold" icon is greyed out.


    Is it possible to do this?


    TIA...MJ