Posts by jproffer

  • Macro to merge cells in blocks

    Re: Macro to merge cells in blocks


    This will create blocks of 4 merged cells:


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

  • macro button links

    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)

  • macro button links

    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.

  • macro button links

    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.

  • extract numbers with decimals from alphanumerics

    Re: extract numbers with decimals from alphanumerics


    Try this as a UDF:


    Code
    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)

  • Worksheet change event running on row below target

    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)


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

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


    It should break.


    Code
    testname = Range("C3").Value


    sets a variable to the value of C3


    Code
    [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)


    Code
    Sheets(testname).Activate


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


    Code
    [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 name....so the msgbox...


    Code
    MsgBox "A sheet with that name already exists."


    pops up


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


    and the code stops.
    End If


    Code
    [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.

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

    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 though...you really should qualify the range("C3")...as in, what sheet is it on?


    Sheets("name").Range("C3").Value

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

    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. :)

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

    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 :)


  • Make it VBA Short

    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.


  • lock cell

    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

  • Identify font style in an Excel sheet and generate a string

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


    You can get the name of the style with this:


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


    or


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


    Code
    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 :)

  • Unhide All Individually Password-protected Sheets At Once

    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, lol........is 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)