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.

Sub mje()
    Dim intColsInSheet As Integer
    Dim intCol As Integer
    Dim strCol As String
    
Get_Column:
    strCol = InputBox("Enter the column that contains loan number:", _
        "Input Column (A, B, C, ...)", "A")
    
    If strCol = "" Then
        MsgBox "Cancelled by User", , str_MsgBox_Title
        ActiveWorkbook.Close
        GoTo Bye
    End If
      
    intCol = Columns(strCol).Column     ' convert column entered to integer
    intColsInSheet = Cells(1, 256).End(xlToLeft).Column  ' Get columns in the sheet
    If intCol > intColsInSheet Then
        MsgBox "Invalid column entered.  Try again"
        GoTo Get_Column
    End If
    
Bye:
End Sub

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

Re: VBA: Get last column in spreadsheet

This works great. Thanks Will!

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

Re: Auto Open Macro?

I searched all hard drives and I have no .xlt files.

Any other suggestions?

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

chitosunday,

Actually, renamed the macro. I also made a copy of personal.xls

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".

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

Ah, now I get it. Thanks for your help Derk!

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