Results 1 to 14 of 14
  1. #1
    Star Lounger
    Join Date
    Jan 2005
    Location
    Linthicum, Maryland, USA
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    macro problem (2003, SP2)

    Our group of 4 will be working on docs with lots of tables. Our table border & heading row shading need RGB values entered, so I've recorded a macro to do this. Trouble is some tables have more heading rows than others. How do you stop the macro till the user selects the rows that need shading, then continue the macro after they've made their selection? I could do two separate macros, one for the borders, then another for the shading, but I'd like to wrap it all into one if I can.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: macro problem (2003, SP2)

    A macro can't pause to let the user select something.

    You could let the user select the heading rows before running the macro, then format first the heading rows and next the rest of the table.
    Or you could prompt the user to enter the number of heading rows (using the InputBox function), and use that in your macro.

    Another option would be to create two macros, and at the end of the first macro display a message box that tells the user that he/she has 20 seconds to select the heading rows, then run the second macro with a 20 second delay:

    Application.OnTime Now + TimeSerial(0, 0, 20), "OtherMacro"

    But I don't think that is a good strategy - the time might be too short for some users and too long for others, and if the user starts doing something else in the interval, results would be unpredictable.

  3. #3
    Star Lounger
    Join Date
    Jan 2005
    Location
    Linthicum, Maryland, USA
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro problem (2003, SP2)

    Thanks. I may try hooking the two together just to see how it works.

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: macro problem (2003, SP2)

    Similar to Hans' suggestion of using an InputBox, you might select one row and then display a small userform with up and down buttons to let the user extend the selection without having to type. It sometimes takes a lot of code to check for bizarre values in an InputBox. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  5. #5
    Star Lounger
    Join Date
    Jan 2005
    Location
    Linthicum, Maryland, USA
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro problem (2003, SP2)

    sounds like a good idea, and I'll have to find someone that can show me how to do that -- I've never used a message box either so that would be a learning experience too.

  6. #6
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: macro problem (2003, SP2)

    Here's a sample. The spin buttons and text box are rigged to work "upside down" so that clicking the down button enlarges the selected region while clicking the up button shrinks it. What do you think?
    Attached Files Attached Files

  7. #7
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: macro problem (2003, SP2)

    I'm somewhat of a duffer and inexperienced at providing Attachments, so my apologies if this doesn't work (this round, anyway), but attached (or so I hope) is a Word document with an alternative, more generic approach that can allow for (effectively) the kind of "pauses" in macros that WordPerfect macro writers are used to.

    When you run my TestReddenRows macro, it calls up a modeless UserForm (the start of the "pause") that prompts the user. Because the UserForm is modeless, the user can do things with the document while the UserForm remains onscreen (and the AppActivate line initially shifts the focus away from the UserForm and onto the document).

    To terminate the pause, the user clicks the button on the UserForm, and you put any post-"pause" processing in the button's click event (in the code behind the UserForm). (In my example, the post-pause processing just colors the selection red.)
    Attached Files Attached Files

  8. #8
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: macro problem (2003, SP2)

    By the clear light of morning I've done some tweaking and clean-up of my previous attachment, and the updated version is attached to this post (with any luck).

    While I was at it, I made my code more "row"-oriented by borrowing shamelessly from Jefferson's sample.
    Attached Files Attached Files

  9. #9
    Star Lounger
    Join Date
    Jan 2005
    Location
    Linthicum, Maryland, USA
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro problem (2003, SP2)

    Both suggestions here are great ideas, but I'm such a novice at code that I can't figure out how to get either into my macro & have it work; I've tried lots of different ways and nothing works. I am taking a one-day overview on VBA for Word next month; maybe I'll "get it" after I do that. I'm keeping a copy of both attachments so I'll be able to try it again later.

    my attachment has the macros for table borders & shading that I recorded and a 4-second break between the two. I know I've got loads of nerve for asking, but if anyone has the time, can you show me how to hook it all up? Thanks.
    Attached Files Attached Files

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: macro problem (2003, SP2)

    In the attached version of your document, I have added a userform along st3333ve's ideas.
    When you run your macro VB_borders, it opens the form at the end. You can then select the header rows, and click OK when you're ready to continue. The OK button simply calls your second macro and closes the form.
    Attached Files Attached Files

  11. #11
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: macro problem (2003, SP2)

    Mine was set up as a separate sub you can call to have the user extend the selection. This would run in "real time" and you would not use the timer method.

    To get all the pieces into your template: Open my document and press Alt+F11 to launch the VBE. In the Project Explorer, right-click and and export both the modTHRS code module and the frmTHRS form module to a convenient folder. Then switch to any code pane in your template and import those two modules into your template.

    In your code, you can call my procedure, passing in the table, in the same manner as the TEST_TableHeaderRowSelect() procedure calls it. Does that make sense?

  12. #12
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: macro problem (2003, SP2)

    If you'd prefer to have the box pop up in the upper right corner (more out of the way) with the focus back on the table, you could make the following 2 tweaks to the HansV version:

    1. In the properties for frmSelect (which you adjust in the Properties Window in the VBEditor: View --> Properties Window), you'd change StartUpPosition to Manual.

    2. At the end of VX_borders, you'd substitute the following for the frmSelect.Show line:

    Dim frm As frmSelect
    Set frm = New frmSelect
    With frm
    .Left = ActiveWindow.Width - 200
    .Top = 60
    .Show
    End With
    AppActivate ActiveDocument.Name
    Set frm = Nothing

  13. #13
    Star Lounger
    Join Date
    Jan 2005
    Location
    Linthicum, Maryland, USA
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro problem (2003, SP2)

    Thanks to everyone for helping me out. You all certainly have a lot of patience, and I really appreciate it -- especially since we now have a much better macro than we did.

  14. #14
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: macro problem (2003, SP2)

    He's ba-a-a-a-a-a-ck.

    "Is this your FINAL version, st3333ve?"

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> "Um, I think so."

    In the attached version, I've separated out the pause-and-prompt process as a separate, generic PausePrompt procedure that you can use with any pair of macros (without creating a separate form) where you want to run the first macro, then pause (with a user prompt), then run the 2nd macro.

    As shown in the attachment, you call the PausePrompt procedure at the end of the first macro, and PausePrompt takes 4 arguments:

    1. The prompt.
    2. The name of the 2nd macro.
    3. (optional) The title shown at the top of the UserForm. (The default is "Macro is paused ...")
    4. (optional) The button caption. (The default is "Continue".)

    The attached document includes your macro pair (run VX_borders) and a 2nd demo pair (run TestPausePrompt).

    For anyone with a similar interest in this kind of multi-use form, I note that it takes advantage of the ability of UserForm labels to automatically size themselves to fit the caption (assuming you set the AutoSize property to True). (Adjusting the other form elements to accommodate the expanded label takes a little more effort, alas.)
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •