Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Recording a Macro that follows Keystrokes | Excel Discussion (Misc queries) | |||
Special keystrokes in a Macro | Excel Discussion (Misc queries) | |||
Relative Macro Help on Keystrokes | Excel Discussion (Misc queries) | |||
How to build build a macro that automatically imports | Excel Programming | |||
Toggle Macro with KeyStrokes | Excel Programming |