macro problem (2003, SP2)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

He’s ba-a-a-a-a-a-ck.

“Is this your FINAL version, st3333ve?”

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote