Posts by jproffer

    Re: Macro to merge cells in blocks

    This will create blocks of 4 merged cells:

    Sub test()
    Dim i As Long
    Dim j As Long
    For i = 1 To 100 Step 4
        For j = 1 To 50 Step 2
            Cells(j, i).Resize(2, 4).Merge
        Next j
    Next i
    End Sub

    Please test on a COPY of your work, in case that's not exactly what you want.

    Re: macro button links

    Another thing you might think about is forcing the SaveAs DialogBox in a Workbook_Open event. Naturally they could just cancel it, unless you code it to keep bringing up the Dialogbox until they saved it AS something locally. (and before you ask :) ......I wouldn't have the slightest idea HOW to do that, but knowing the power of VBA, I would say it's probably possible)

    Re: macro button links

    Not really. I don't email a lot of WBs, and when I do it's just for THAT person, so they generally just save it.

    Another thing you could do is put the WB on Google Docs, then they are forced (I think) to download it before changing it...then they can upload again, and send the next person a quick "your turn" email without any attachments.

    Of course that would require that they don't "jump the gun" so to speak. If they did, they would be changing what would, in a few minutes, be an outdated version. It would be risky, to say the least....depends on the people.

    Re: macro button links

    Let them not get paid a few times because of lost/corrupted data and they will see the light :saychees:

    But you could put a pop-up message box each time the file is opened, telling (warning) them of possible lost data if they didn't first SAVE the file to their local drive.

    Re: extract numbers with decimals from alphanumerics

    Try this as a UDF:

    Function CatchLastNumbers(MyRng As String)
    Dim MyRev As String
    Dim Start As Long
        MyRev = StrReverse(MyRng)
            Start = Len(MyRev) - WorksheetFunction.Find(" ", MyRev) + 2
            CatchLastNumbers = Mid(MyRng, Start, 9999)
    End Function

    Formula syntax: =CatchLastNumbers(A1)

    Re: Worksheet change event running on row below target

    Looks like you were offsetting before you copied...not sure, but anyhow try this as your macro (normal...not sheet code)

    Re: Macro wont stop running once if-then-else statment is true

    It should break.

    testname = Range("C3").Value

    sets a variable to the value of C3

    [COLOR=#0000ff]On Error Resume Next[/COLOR]

    Just as it sounds, it tells it to go on and skip the error (if there is no sheet with that name)


    Attempts to activate a sheet called (whatever is in C3)

    [COLOR=#0000ff]If[/COLOR] Err = 0 [COLOR=#0000ff]Then[/COLOR]

    "Err" is the count of errors. So if Err = 0 then it DID activate the sheet with that the msgbox...

    MsgBox "A sheet with that name already exists."

    pops up

    Exit [COLOR=#0000ff]Sub[/COLOR]

    and the code stops.
    End If

    [COLOR=#0000ff]On Error Goto[/COLOR] 0

    kills the error handler from above, and resumes normal runtime...will break on error.

    Is that not what you wanted? If you don't exit after the message box, it will add a sheet and try to name it to C3 and will error out because there is already a sheet with that name.

    Re: Macro wont stop running once if-then-else statment is true

    Try this one. It will try to activate a sheet named (whatever is in C3), if it does (and there is NO error) then Err=0 and it will trigger the messagebox and exit. After that, it kills the error handler and resumes as normal.

    One tip really should qualify the range("C3") in, what sheet is it on?


    Re: Macro wont stop running once if-then-else statment is true

    Certainly not useless...and not horribly sloppy either. :)

    Just can be better is all. That's as good a way to learn as any. The recorder does have limits though... making loops is the first that comes to mind...there are many other things it can't do. :)

    Re: Macro wont stop running once if-then-else statment is true

    Try this. I just cleaned up a bit and changed the IF statement to exit after the message box. All the selecting is unnecessary. If you record something, and see "select" at the end of one line, and "Selection" at the beginning of the next, you can combine them.

    Also the "FormulaR1C1" isn't necessary IMO (some disagree), but even if you really are putting a formula in a cell, .Value will do that.

    At any rate, here ya go :)

    Re: Make it VBA Short

    I'm not sure this will work...because I'm not SURE you can use a loop variable in a workbook name, but you might try this out on a COPY of those 3 workbooks.

    Re: lock cell


    That message sounds like your workbook has some sheet code or workbook level code playing tricks with you.

    Press Alt+F11

    Press CTRL+R

    Find the workbook you're talking about and expand it (with the + symbol to the left of the name).

    Double click each worksheet and see if there is any code in any module.

    Also double click on "ThisWorkbook" and check for code.

    Code, if any, would be in the white area to the right after you double click the sheet/workbook

    Re: Identify font style in an Excel sheet and generate a string

    You can get the name of the style with this:

    FntStyl = Range("A1").Font.FontStyle


    Select Case Range("A1").Font.FontStyle
            Case "Italic"
                Affil = "I"
            Case "Underline"
                Affil = "U"
            Case "Bold"
                Affil = "B"
        End Select

    That would set your variable, but you would have to put in a "Case Else" for other styles or combinations of styles. Modify to whatever Letters vs. Styles you want in your variable.

    I would suggest using the top code I listed to make sure you get the names right for various combinations. For instance, Bold/Underlined/Italic...Bold/Italic (but not underlined).....also there's the double underline.

    So something like this would get you all those names as strings.

    Sub GetFontStyle
    Dim FntStyl as String
        FntStyl = Range("A1").Font.FontStyle
            MsgBox FntStyl
    End Sub

    EDIT: My mistake..."Underline" isn't a FontStyle, so there are only a few combinations. Anyhow, that should lead you in the right direction. Post back with any further questions. HTH :)

    Re: Unhide All Individually Password-protected Sheets At Once

    I was hesitant on the code tags because sometimes they end up squeeed up on the left 1-2 inches of the screen. 8-)
    ...........soooooo, speaking of the ads, there a way to minimize/move the ads to a more convenient location?? (above the post would be fine, so long as the entire post wasn't restricted to the far left of the post window)