Macro Pause during Dialog Box data entry

[Guest]

How can I make a macro pause for user input into a dialog box. As a simple
example, If I have the DATA>VALIDATION menu open, how can i make it pause
while I set the validation options, and then continue on with the rest of the
macro after the user hits OK?

Thanks

 

[Jim Rech]

Sub a()
MsgBox "Before show"
Application.Dialogs(xlDialogDataValidation).Show
MsgBox "After show"
End Sub

--
Jim
| How can I make a macro pause for user input into a dialog box. As a
simple
| example, If I have the DATA>VALIDATION menu open, how can i make it pause
| while I set the validation options, and then continue on with the rest of
the
| macro after the user hits OK?
|
| Thanks

 

[Guest]

This works really well, thanks. Do you know where I can find a list of all
the dialog box's exact names?

 

[Dave Peterson]

One way is to search VBA's help for:
"Built-In Dialog Box Argument Lists"

 

[neopolitan]

Jim, I have wanted to know how to do this for a long time, but I need a
little more explanation on how to employ your code. Where in the code
that I have below would I insert your code? Obviously, I don't want
the column (A) hard-coded in the macro, thus the need for a pause.


Code:
--------------------
Sub ColDataValidation()
'
' ColDataValidation Macro
' Macro recorded 10/19/2005 by neopolitan'

'
With Selection.Validation
.Delete
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=countif(A:A,$A1)<=1"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

 

[Jim Rech]

You really cannot use the code I posted with what you want to do. All my
code does is open the dialog and then lets the user do whatever he wants.
We have no control over what goes in the dialog. In your case you want to
get one input from the user (I think) but other do everything else.

The only way I know of to do what you want is to ask the user for a range
first and then run your code, sticking the range he selected in your code.

This is an example of asking for a range:

Sub GetRg()
Dim Rg As Range
On Error GoTo BadRg
Set Rg = Application.InputBox("Select a range", , , , , , , 8)
Rg.Value = "You selected this range"
Exit Sub
BadRg:
MsgBox "You cancelled"
End Sub

Obviously you'd have to adapt this to your situation by building the code

Formula1:="=countif(A:A,$A1)<=1"

from Rg.Address in some way.


--
Jim
in message |
| Jim, I have wanted to know how to do this for a long time, but I need a
| little more explanation on how to employ your code. Where in the code
| that I have below would I insert your code? Obviously, I don't want
| the column (A) hard-coded in the macro, thus the need for a pause.
|
|
| Code:
| --------------------
| Sub ColDataValidation()
| '
| ' ColDataValidation Macro
| ' Macro recorded 10/19/2005 by neopolitan'
|
| '
| With Selection.Validation
| .Delete
| .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
| xlBetween, Formula1:="=countif(A:A,$A1)<=1"
| .IgnoreBlank = True
| .InCellDropdown = True
| .InputTitle = ""
| .ErrorTitle = ""
| .InputMessage = ""
| .ErrorMessage = ""
| .ShowInput = True
| .ShowError = True
| End With
| End Sub
| --------------------
|
|
| --
| neopolitan
|
|
| ------------------------------------------------------------------------
| neopolitan's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=611
| View this thread: http://www.excelforum.com/showthread.php?threadid=477565
|

 

[Dave Peterson]

And just to add to Jim's response, working with Conditional formatting formulas
can be confusing (well, to me anyway).

It's one time I think it's easier (but not required) to select the range you
want to apply that CF rule to.

You can also use a work-around from John Walkenbach's site:
http://j-walk.com/ss/excel/odd/odd07.htm