Build a Macro that uses keystrokes
 

I need to write some macros that are based on keystrokes. I've done it in
other spreadsheets, but can not figure out how to do it in Excel 2003. As an
example, I import delimited files and sometimes they must be edited to format
correctly. If the field has <blank<blank12:30:30, it is viewed as a text
field. To correct this I need to remove the leading blanks. The keystrokes
would be <F2<Home<Del<Del<Enter. This should take me to the next field
and able to repeat the macro. When I record the macro it give me a hard
value of 12:30:30.

Build a Macro that uses keystrokes
 

You do not need to use keystrokes to do what you need. You can use
Trim(ActiveCell.Value), or somethign similar, to get rid of all
unnecessary spaces. ActiveCell wouldn't be my first choice though.
You could use a For Next loop to cycle through the column containing
the screwed up cells. HTH
King Ki wrote:
I need to write some macros that are based on keystrokes. I've done it in
other spreadsheets, but can not figure out how to do it in Excel 2003. As an
example, I import delimited files and sometimes they must be edited to format
correctly. If the field has <blank<blank12:30:30, it is viewed as a text
field. To correct this I need to remove the leading blanks. The keystrokes
would be <F2<Home<Del<Del<Enter. This should take me to the next field
and able to repeat the macro. When I record the macro it give me a hard
value of 12:30:30.

Build a Macro that uses keystrokes
 

In this case, you are correct. However I still have other issues that I
would like to build macros based on keystrokes

Thanks

"JW" wrote:

You do not need to use keystrokes to do what you need. You can use
Trim(ActiveCell.Value), or somethign similar, to get rid of all
unnecessary spaces. ActiveCell wouldn't be my first choice though.
You could use a For Next loop to cycle through the column containing
the screwed up cells. HTH
King Ki wrote:
I need to write some macros that are based on keystrokes. I've done it in
other spreadsheets, but can not figure out how to do it in Excel 2003. As an
example, I import delimited files and sometimes they must be edited to format
correctly. If the field has <blank<blank12:30:30, it is viewed as a text
field. To correct this I need to remove the leading blanks. The keystrokes
would be <F2<Home<Del<Del<Enter. This should take me to the next field
and able to repeat the macro. When I record the macro it give me a hard
value of 12:30:30.

Build a Macro that uses keystrokes
 

In that case, have a look at the SendKeys method in VBA. This would
do what you want, but you have to be in teh Excel window when you run
it, not the VBA window. If you are in the VBA window, it will apply
the changes to whatever line you are on in the VBE.
Application.SendKeys ("{F2}{HOME}{DEL}{DEL}~")
King Ki wrote:
In this case, you are correct. However I still have other issues that I
would like to build macros based on keystrokes

Thanks

"JW" wrote:

You do not need to use keystrokes to do what you need. You can use
Trim(ActiveCell.Value), or somethign similar, to get rid of all
unnecessary spaces. ActiveCell wouldn't be my first choice though.
You could use a For Next loop to cycle through the column containing
the screwed up cells. HTH
King Ki wrote:
I need to write some macros that are based on keystrokes. I've done it in
other spreadsheets, but can not figure out how to do it in Excel 2003. As an
example, I import delimited files and sometimes they must be edited to format
correctly. If the field has <blank<blank12:30:30, it is viewed as a text
field. To correct this I need to remove the leading blanks. The keystrokes
would be <F2<Home<Del<Del<Enter. This should take me to the next field
and able to repeat the macro. When I record the macro it give me a hard
value of 12:30:30.

Build a Macro that uses keystrokes
 

the macro recorder doesn't recognize keystrokes once you enter edit mode in a
cell. As soon as you press F2 the only thing the recorder will see is the
value or formula in the cell when you leave edit mode.

So to answer your question. If your keystrokes always involve doing things
in edit mode inside cells, then no, you can't record them.

This being said, there are other ways to go about doing things. JW gave you
the work around for the example you gave. There is generally a way to do
something, even if it takes some inventive code.
--
JNW


"King Ki" wrote:

In this case, you are correct. However I still have other issues that I
would like to build macros based on keystrokes

Thanks

"JW" wrote:

You do not need to use keystrokes to do what you need. You can use
Trim(ActiveCell.Value), or somethign similar, to get rid of all
unnecessary spaces. ActiveCell wouldn't be my first choice though.
You could use a For Next loop to cycle through the column containing
the screwed up cells. HTH
King Ki wrote:
I need to write some macros that are based on keystrokes. I've done it in
other spreadsheets, but can not figure out how to do it in Excel 2003. As an
example, I import delimited files and sometimes they must be edited to format
correctly. If the field has <blank<blank12:30:30, it is viewed as a text
field. To correct this I need to remove the leading blanks. The keystrokes
would be <F2<Home<Del<Del<Enter. This should take me to the next field
and able to repeat the macro. When I record the macro it give me a hard
value of 12:30:30.

Build a Macro that uses keystrokes
 

If I can't record them can I go into VB and Edit them?

"JNW" wrote:

the macro recorder doesn't recognize keystrokes once you enter edit mode in a
cell. As soon as you press F2 the only thing the recorder will see is the
value or formula in the cell when you leave edit mode.

So to answer your question. If your keystrokes always involve doing things
in edit mode inside cells, then no, you can't record them.

This being said, there are other ways to go about doing things. JW gave you
the work around for the example you gave. There is generally a way to do
something, even if it takes some inventive code.
--
JNW


"King Ki" wrote:

In this case, you are correct. However I still have other issues that I
would like to build macros based on keystrokes

Thanks

"JW" wrote:

You do not need to use keystrokes to do what you need. You can use
Trim(ActiveCell.Value), or somethign similar, to get rid of all
unnecessary spaces. ActiveCell wouldn't be my first choice though.
You could use a For Next loop to cycle through the column containing
the screwed up cells. HTH
King Ki wrote:
I need to write some macros that are based on keystrokes. I've done it in
other spreadsheets, but can not figure out how to do it in Excel 2003. As an
example, I import delimited files and sometimes they must be edited to format
correctly. If the field has <blank<blank12:30:30, it is viewed as a text
field. To correct this I need to remove the leading blanks. The keystrokes
would be <F2<Home<Del<Del<Enter. This should take me to the next field
and able to repeat the macro. When I record the macro it give me a hard
value of 12:30:30.

Build a Macro that uses keystrokes
 

Yes.

You don't have to ever record a macro - it is just useful at times.

In the VBE, Insert=Module

type in the code you want.

--
Regards,
Tom Ogilvy


"King Ki" wrote:

If I can't record them can I go into VB and Edit them?

"JNW" wrote:

the macro recorder doesn't recognize keystrokes once you enter edit mode in a
cell. As soon as you press F2 the only thing the recorder will see is the
value or formula in the cell when you leave edit mode.

So to answer your question. If your keystrokes always involve doing things
in edit mode inside cells, then no, you can't record them.

This being said, there are other ways to go about doing things. JW gave you
the work around for the example you gave. There is generally a way to do
something, even if it takes some inventive code.
--
JNW


"King Ki" wrote:

In this case, you are correct. However I still have other issues that I
would like to build macros based on keystrokes

Thanks

"JW" wrote:

You do not need to use keystrokes to do what you need. You can use
Trim(ActiveCell.Value), or somethign similar, to get rid of all
unnecessary spaces. ActiveCell wouldn't be my first choice though.
You could use a For Next loop to cycle through the column containing
the screwed up cells. HTH
King Ki wrote:
I need to write some macros that are based on keystrokes. I've done it in
other spreadsheets, but can not figure out how to do it in Excel 2003. As an
example, I import delimited files and sometimes they must be edited to format
correctly. If the field has <blank<blank12:30:30, it is viewed as a text
field. To correct this I need to remove the leading blanks. The keystrokes
would be <F2<Home<Del<Del<Enter. This should take me to the next field
and able to repeat the macro. When I record the macro it give me a hard
value of 12:30:30.

Build a Macro that uses keystrokes
 

Edit them? Not really sure what you mean. You can use SendKeys to do
practically anything, but I don't really condone it. There are
generally other ways to do things. But, if you have to use send keys,
it can most likely be done.
Here is an example of entering a cell and typing "fred".
Application.SendKeys ("{F2}fred")
King Ki wrote:
If I can't record them can I go into VB and Edit them?

"JNW" wrote:

the macro recorder doesn't recognize keystrokes once you enter edit mode in a
cell. As soon as you press F2 the only thing the recorder will see is the
value or formula in the cell when you leave edit mode.

So to answer your question. If your keystrokes always involve doing things
in edit mode inside cells, then no, you can't record them.

This being said, there are other ways to go about doing things. JW gave you
the work around for the example you gave. There is generally a way to do
something, even if it takes some inventive code.
--
JNW


"King Ki" wrote:

In this case, you are correct. However I still have other issues that I
would like to build macros based on keystrokes

Thanks

"JW" wrote:

You do not need to use keystrokes to do what you need. You can use
Trim(ActiveCell.Value), or somethign similar, to get rid of all
unnecessary spaces. ActiveCell wouldn't be my first choice though.
You could use a For Next loop to cycle through the column containing
the screwed up cells. HTH
King Ki wrote:
I need to write some macros that are based on keystrokes. I've done it in
other spreadsheets, but can not figure out how to do it in Excel 2003. As an
example, I import delimited files and sometimes they must be edited to format
correctly. If the field has <blank<blank12:30:30, it is viewed as a text
field. To correct this I need to remove the leading blanks. The keystrokes
would be <F2<Home<Del<Del<Enter. This should take me to the next field
and able to repeat the macro. When I record the macro it give me a hard
value of 12:30:30.

Build a Macro that uses keystrokes
 

You can edit any macro in the VB editor, even recorded ones. But if all it
is recording is the value when you exit there won't be much to edit! :-)
--
JNW


"King Ki" wrote:

If I can't record them can I go into VB and Edit them?

"JNW" wrote:

the macro recorder doesn't recognize keystrokes once you enter edit mode in a
cell. As soon as you press F2 the only thing the recorder will see is the
value or formula in the cell when you leave edit mode.

So to answer your question. If your keystrokes always involve doing things
in edit mode inside cells, then no, you can't record them.

This being said, there are other ways to go about doing things. JW gave you
the work around for the example you gave. There is generally a way to do
something, even if it takes some inventive code.
--
JNW


"King Ki" wrote:

In this case, you are correct. However I still have other issues that I
would like to build macros based on keystrokes

Thanks

"JW" wrote:

You do not need to use keystrokes to do what you need. You can use
Trim(ActiveCell.Value), or somethign similar, to get rid of all
unnecessary spaces. ActiveCell wouldn't be my first choice though.
You could use a For Next loop to cycle through the column containing
the screwed up cells. HTH
King Ki wrote:
I need to write some macros that are based on keystrokes. I've done it in
other spreadsheets, but can not figure out how to do it in Excel 2003. As an
example, I import delimited files and sometimes they must be edited to format
correctly. If the field has <blank<blank12:30:30, it is viewed as a text
field. To correct this I need to remove the leading blanks. The keystrokes
would be <F2<Home<Del<Del<Enter. This should take me to the next field
and able to repeat the macro. When I record the macro it give me a hard
value of 12:30:30.