SendKeys question
 

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

SendKeys question
 

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

SendKeys question
 

It tabs over three times and then moves down twice.

If you start at C8, the code will move you to F10
--
Gary''s Student - gsnu200824


"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

SendKeys question
 

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

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"ExcelMonkey" wrote in message
...
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

SendKeys question
 

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

SendKeys question
 

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

SendKeys question
 

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

SendKeys question
 

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..._june_15_1999/

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

Thanks

EM


"Dave Peterson" wrote:

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

SendKeys question
 

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

ExcelMonkey wrote:

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..._june_15_1999/

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

Thanks

EM

"Dave Peterson" wrote:

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


--

Dave Peterson