I'm writing a macro that allows the user to change the colors of the
Conditional formattting. I can't figure out how to open the Conditional
Formtting dialog box and then end the macro. My intent is to use an event
to run another macro after they've set the colors. Ideally, the event would
be the close of the Conditional Formatting dialog box, but I'm not sure
that is an XL event. If not, I'll just have the user selecting a cell as
the event.

1. Can anyone tell me how to open the Conditional Formatting dialog box
from VBA and leave it open for the user to edit?

2. Is the closing of the Conditional Formatting dialog box an event? If so,
what is it called?

Thanks in advance.

Opening the dialog should be as easy as:

Application.Dialogs(xlDialogConditionalFormatting) .Show

I could not figure out how to trap its events, however. Good luck with that
part!

HTH,

Eric

You might try this also:

myvar = Application.Dialogs(xlDialogConditionalFormatting) .Show

It will return True/False depending on whether the user pressed Okay or
Cancel. It should also make your VBA subroutine pause until the user is done.

Eric

On Wed, 21 Jan 2009 14:23:09 -0700, salgud wrote:

I'm writing a macro that allows the user to change the colors of the
Conditional formattting. I can't figure out how to open the Conditional
Formtting dialog box and then end the macro. My intent is to use an event
to run another macro after they've set the colors. Ideally, the event would
be the close of the Conditional Formatting dialog box, but I'm not sure
that is an XL event. If not, I'll just have the user selecting a cell as
the event.

1. Can anyone tell me how to open the Conditional Formatting dialog box
from VBA and leave it open for the user to edit?

2. Is the closing of the Conditional Formatting dialog box an event? If so,
what is it called?

Thanks in advance.

I'm gathering from the lack of replies, this (opening the Conditional
Formatting dialog box and stopping the macro) is not doable. Can someone
confirm so I can go ahead and finish this project?

To be a little more specific:

Option Explicit
Option Base 1

Sub Do_Conditional_Formatting()
Dim User_Finished As Boolean
'
' Put up the conditional formatting dialog box.
'
User_Finished = Application.Dialogs(xlDialogConditionalFormatting) .Show
'
' The dialog box halts the VBA code until the user presses Okay
' or Cancel. When that happens, control is returned to this
' routine. Then you can call whatever other routine you want.
'
If (User_Finished) Then Call My_Other_Subroutine ' Won't run if user
pressed Cancel
'
End Sub
'
Sub My_Other_Subroutine()
MsgBox "This won't happen until the user closes the conditional" & _
"formatting dialog box!"
'
' Do other stuff...
'
End Sub

Is this not what you are looking for? Your macro that puts up the
conditional formatting box will halt until the box is closed. When the user
is done, control is returned to the macro that was running. Then you can run
whatever other code you want.


I'm gathering from the lack of replies, this (opening the Conditional
Formatting dialog box and stopping the macro) is not doable. Can someone
confirm so I can go ahead and finish this project?