×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

simple key stroke macros in excel

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.
Replies continue below

Recommended for you

RE: simple key stroke macros in excel

Sorry you're having so much trouble, but VBA is considerably more powerful than either 123's macro language or even Excel 4's macro language.

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

AndyZ:

The VBA code required to complete your tast is very simple (thanks to IRstuff's post )... Once you have the formulas in the column you want them in, activate the first cell in the column and execute the following procedure:

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

Why use macros when you can utilise existing features 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

(OP)
Thanks for the help, Ctrl+H sounds good.  It works for inserting the apostraphe's but I can't get it to strip them.  The search criteria only seems to look in formulas and values, not in labels. I am using XP, I don't know if it is a version change, or I just haven't used the replace feature correctly.

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

Since the object is to not have an equation, use a double quote instead of the single quote as the first character.  CRTL-h will then work.

TTFN

RE: simple key stroke macros in excel

(OP)
Double Quotes worked like a champ. THANKS.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close