Format Cells Dialog

[Guest]

I was wondering though if there is a way to show the complete Format Cells
dialog with all tabs using an xlBuiltInDialog? I would like to override the
menu event for the Format Cells menu button, so that I can run code to
refresh some controls after a user changes their cell format, but so far have
only found a way to show the dialog with a single tab on it.

Thanks
Patrick Hampton

 

[Jim Cone]

Patrick,
Unless it is hiding under an alias, I think you are going to have to use
something like this, which activates the dialog by using the menu...

Excel.Application.CommandBars(1).FindControl(, 30006, , , True).Controls("Cells...").Execute
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Patrick Hampton"
<Patrick (e-mail address removed)>
wrote in message
I was wondering though if there is a way to show the complete Format Cells
dialog with all tabs using an xlBuiltInDialog? I would like to override the
menu event for the Format Cells menu button, so that I can run code to
refresh some controls after a user changes their cell format, but so far have
only found a way to show the dialog with a single tab on it.

Thanks
Patrick Hampton

 

[Guest]

Jim

Thanks for the reply, but I don't think this is going to work very well for
me. I am need to override the menu event hanlder and show the dialog from
within the handler. So I assume that calling execute on the menu item will
simply call my handler again rather than showing the dialog, is this correct?
Approaching the problem from a different direction, is there an event that
fires in Excel when the cell format has been changed? If so I could go that
route instead.

Patrick Hampton

 

[Jim Cone]

Patrick,

Each worksheet has a change event and a selection change event.
You can test those to see if your cell format change activates either event
..
Also, the often frowned on Send Keys method could work...
'Ctrl key + 1
Application.SendKeys "^1", True

Note: you cannot test SendKeys from the vba module,
test it from the worksheet.
Regards,
Jim Cone
http://www.officeletter.com/blink/specialsort.html


"Patrick Hampton"
wrote in message
Jim
Thanks for the reply, but I don't think this is going to work very well for
me. I am need to override the menu event hanlder and show the dialog from
within the handler. So I assume that calling execute on the menu item will
simply call my handler again rather than showing the dialog, is this correct?
Approaching the problem from a different direction, is there an event that
fires in Excel when the cell format has been changed? If so I could go that
route instead.
Patrick Hampton