simple key stroke macros in excel
simple key stroke macros in excel
(OP)
I used to use macros all the time in lotus 123. you could record and edit strokes in a cell, or with a little practice you could type in the key strokes.
I recently tried to record a key stroke macro in excel and found it did not work. in 123, my macro would look something like this {edit}{home}{delete}{enter} and this could be activated by a control+letter combination that I set in the spreadsheet.
I have a spread sheet which I have to insert a column of numbers every month to track that months data. In order to make things simple I created a dummy column with the formulas I need. Each cell has a an apostraphe ( ' ) in front of the formula so that excel treats the cell as a text value. to activate the formula I strip out the apostraphe. currently I press F2 to edit the contents of the cell; press home to put the cursor in the same place as the apostraphe and press delete then enter. Presto chango I have a formula instead of a text label. there are about 20 cells in the column, in lotus, 20 repetitions of control something, no thought involved, in excel cumbersome keystrokes. My employer only allows excel.
The macro recorded by excel blew up, exel had no clue what I wanted. The recording was in VB (?).
Any thoughts.
I recently tried to record a key stroke macro in excel and found it did not work. in 123, my macro would look something like this {edit}{home}{delete}{enter} and this could be activated by a control+letter combination that I set in the spreadsheet.
I have a spread sheet which I have to insert a column of numbers every month to track that months data. In order to make things simple I created a dummy column with the formulas I need. Each cell has a an apostraphe ( ' ) in front of the formula so that excel treats the cell as a text value. to activate the formula I strip out the apostraphe. currently I press F2 to edit the contents of the cell; press home to put the cursor in the same place as the apostraphe and press delete then enter. Presto chango I have a formula instead of a text label. there are about 20 cells in the column, in lotus, 20 repetitions of control something, no thought involved, in excel cumbersome keystrokes. My employer only allows excel.
The macro recorded by excel blew up, exel had no clue what I wanted. The recording was in VB (?).
Any thoughts.





RE: simple key stroke macros in excel
As such, unfortunately, you need to be thinking like a programmer, as it is a full blown programming language and there is no longer the constructs that you are familiar with.
The following, while looking odd, takes advantage of the fact that VBA ignores leading quotes:
Sub Macro1()
ActiveCell.Formula = ActiveCell.Formula
End Sub
TTFN
RE: simple key stroke macros in excel
The VBA code required to complete your tast is very simple (thanks to IRstuff's post
Sub ChangeToFormula()
For i = 1 To 20
ActiveCell.Formula = ActiveCell.Formula
ActiveCell.Offset(rowoffset:=1, columnOffset:=0).Activate
Next i
End Sub
If you want to assign a keystroke to the macro (Excel 2000), go to 'Tools' -> 'Macro' -> 'Macros...' then click on the macro name (in this case 'ChangeToFormula') and click on the 'Options...' button. This will let you assign a keystroke to your macro (Crtl + any letter). Beware that any letter you enter will take precedence over any of excel's standard keystrokes (example... if you enter 'b', you can no longer use Ctrl+b to bold the selected text / cell).
Good Luck!
jproj
RE: simple key stroke macros in excel
Select the area concerned, then use replace (CTRL + H), with the apostraphe ( ' ) in the find variable, and leave the replace variable blank, then when selecting replace all, all the apostraphes will be removed.
Hope this is heaps easier than learning how to use VB
RE: simple key stroke macros in excel
As for programming the macro, thanks, I will see if I can make them work. It just seems like a lot of trouble for something that used to be so easy.
I am sure that lotus 123's look and feel lawsuit against microsoft and Borland quatro pro seemed like a good idea at the time, but I would have been much happier if excel had been able to retain the keystroke menu access that 123 developed as an alternative to windows pull down menus.
Thanks for your help. I wish ctrlH worked for me, maybe I can fix that.
RE: simple key stroke macros in excel
TTFN
RE: simple key stroke macros in excel