Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pause recorded macro for user selection of menu option
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pause recorded macro for user selection of menu option
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. -- Gary''s Student - gsnu200832 "Steve Gibbs" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pause recorded macro for user selection of menu option
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 -- HTH... Jim Thomlinson "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu200832 "Steve Gibbs" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pause recorded macro for user selection of menu option
I fully agree with you.
Some kind of userform is the most practical approach. -- Gary''s Student - gsnu200832 "Jim Thomlinson" wrote: 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 -- HTH... Jim Thomlinson "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu200832 "Steve Gibbs" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Accessing the PERSONAL.XLS without a macro ever recorded by user. | Excel Programming | |||
Pause a macro for user input | Excel Programming | |||
Pause macro user selection, then resume | Excel Programming | |||
set up a pause in a print macro for user input | Excel Programming | |||
Macro to pause for user input in dialog box | Excel Discussion (Misc queries) |