This is the dialog that you see when you select range and hit ctrl-1
(control-one). Actually, it's the first tab on this dialog (Number tab).

You can see that dialog by:
opening the VBE
hit ctrl-g to see the immediate window
type this and hit enter:
Application.Dialogs(xlDialogFormatNumber).Show

But it's doing a bit more.

This Buffer.Numberformatlocal is passing the number format for whatever range
that buffer is set to.

For me (with my buffer range having a General number format), the 3 tabs took me
to the listbox under the type: textbox.

Then I hit the down arrow. It took me to the number format directly under the
currently selected number format.

Then Enter applied that choice.

Try this skinnied down version:

Option Explicit
Sub testme()
Dim buffer As Range
Set buffer = ActiveCell
Application.Dialogs(xlDialogFormatNumber).Show buffer.NumberFormatLocal
End Sub

======
But I wouldn't use this SendKeys routine on anything. Too many things can go
wrong. If the wrong application is active (it doesn't have to be excel), who
knows what I just did or chose.

Are you trying to change the numberformat or find out what numberformats are
used?


ExcelMonkey wrote:

It is going through a menu to pull up the number format dialog. I just
wasn't sure what part of the code activates the menu itself. This was
written in 1999. I am currently in Excel 2007. It works fine in code but I
am trying to mimic it with actual keystrokes in Excel 2007 and I can't seem
to do it. When I try it manually it moves the cursor around the cells in the
sheet. What part of this code activates the menus?

Do
SaveFormat = Buffer.NumberFormatLocal
Dummy = Buffer.NumberFormatLocal
DoEvents
SendKeys "{tab 3}{down}{enter}"
Application.Dialogs(xlDialogFormatNumber).Show Dummy
nFormat(Counter) = Buffer.NumberFormatLocal
Counter = Counter + 1
Loop Until nFormat(Counter - 1) = SaveFormat

"Dave Peterson" wrote:

I should have said that this kind of code is sometimes used to change options on
a dialog that you can't do any other way.

Dave Peterson wrote:

It hits the tab key 3 times.

You may want to try doing it manually to see what's trying(!) to be selected.

ExcelMonkey wrote:

Can somebody tell me what this is doing:

SendKeys "{tab 3}{down}{enter}"

This is obviously mimicing keystrokes. I am not sure if I understand what
{tab 3} means.

Thanks

EM

--

Dave Peterson

--

Dave Peterson


--

Dave Peterson