Pause recorded macro for user selection of menu option

[Steve Gibbs]

I have a macro that creates a series of validation lists. Since I need to
create about 100 forms with 10 to 20 validation list each. I would really
like my macro to do more. I create a series of range names, then I need to
use them in my validation lists. My macro selects "Data", "Validation",
"List", places an "=" in the box, then selects "Insert", "Name", "Paste".
Then I need to select the appropriate name from the list of range names, but
I don't know how to modify the macro to allow for this selection. I have
searched my books and cannot find an answer. I have started to study VBA but
I need this this week if possible and I'm not ready to write the code. I
would really appreciate some help. Thanks

 

[Gary''s Student]

Here is an exaple that you may be able to adapt. Say we have a macro that at
some point needs the users to supply data in A1 and B1 and C1.

We want the macro to tell the user to puts values in A1, B1, and C1 and then
wait until all three cells are filled.

Once all three cells are filled, the macro should proceed.

Sub WaitForEntry()
MsgBox ("Please fill A1, B1, and C1")
Set r = Range("A1:C1")
While Application.WorksheetFunction.Count(r) < 3
DoEvents
Wend
MsgBox ("Thank you")
End Sub

The DoEvents allows the user and the macro to share focus.

 

[Jim Thomlinson]

And what happens if A1, B1 and C1 are never filled (with numbers since you
are using count and not counta). Then the code just runs forever. I
personally would avoid code like this but to each his own.

Steve:
Here is how you create named ranges and validation lists to refer to those
ranges...

Sub test()
ThisWorkbook.Names.Add "MyList", Sheets("Sheet1").Range("A1:A3")
Sheets("Sheet1").Range("B1").Validation.Add Type:=xlValidateList, _
Formula1:="=MyList"
End Sub

 

[Gary''s Student]

I fully agree with you.

Some kind of userform is the most practical approach.