SendKeys question

[ExcelMonkey]

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]

It hits the tab key 3 times.

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

 

[Gary''s Student]

It tabs over three times and then moves down twice.

If you start at C8, the code will move you to F10

 

[Andy Pope]

Hi,

The help file says,

To specify repeating keys, use the form {key number}. You must put a space
between key and number. For example, {LEFT 42} means press the LEFT ARROW
key 42 times; {h 10} means press H 10 times.

Cheers
Andy

 

[Dave Peterson]

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.

 

[ExcelMonkey]

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

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]

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?

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

 

[ExcelMonkey]

I am looking to see what custom formats exist and what are used. The code
comes from John Walkenbachs website and was written in 1999 by By Leo Heuser:

http://spreadsheetpage.com/index.php/eee/issue_no7_june_15_1999/

I wasn't sure if anyone came up with another way of doing this without usin
the SendKey method.

Thanks

EM

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?

 

[Dave Peterson]

I've never seen a solution for this (except Leo's).

I am looking to see what custom formats exist and what are used. The code
comes from John Walkenbachs website and was written in 1999 by By Leo Heuser:

http://spreadsheetpage.com/index.php/eee/issue_no7_june_15_1999/

I wasn't sure if anyone came up with another way of doing this without usin
the SendKey method.

Thanks

EM