Formula Bar in Excel 2003

[klb]

Does anyone know how to change the button on the Formula
bar so that when you click on it, if there is already a
value in the cell, an equal sign is entered at the
beginning of the existing entry? With Excel 2003, it
erases the existing entry and provides you with a dialog
box to insert a new formula.

For a variety of reasons, I want to add to an existing
entry instead of replacing it with something else.

Thanks

 

[Peo Sjoblom]

I am afraid that was last seen in excel 2000 to many people's chagrin

 

[klb]

Just upgraded from Excel97.

And no one has been able to write some VBA code that will
duplicate it?

 

[Ken Wright]

Put =0 in a cell, copy the cell, select the range concerned and do Edit / Paste
Special / Formulas & Add
Now just do Edit / Replace / +(0) with blank and all numbers will be prefixed
with an =

 

[klb]

lol - I wanted a simple - click one button - way to do it
instead of the

F2 Home = end

that I am using now.

 

[Dave Peterson]

You could put it in a macro and add a custom button to your favorite toolbar.

Assign it this macro:

Option Explicit
Sub addEquals()
SendKeys "{F2}{Home}={End}"
End Sub

 

[klb]

Here is the code that I came up with. It only adds the =
if the entry in the current cell is a number. If the
entry is a formula, date or text, it will not add the
equal sign but will go into edit mode.

'assign to a custom button on any toolbar
'Allows editing of an existing number, places = sign at
start of entry
'Activates cell for editing

Sub editval()

Dim testval As Variant
testval = ActiveCell.Value

ActiveCell.Select
'Cell must not be a formula
If ActiveCell.HasFormula = False Then
'Cell must be a number not text or a date
If IsNumeric(testval) Then
'places the = sign to the left of the existing number
ActiveCell.Value2 = "=" & ActiveCell.Value
'Edit cell using F2

End If
End If

Application.SendKeys ("{f2}")

End Sub

 

[Ken Wright]

I must be missing something here. After doing what I suggested to your data, a
simple press of F2 on any of the numbers in the range you pasted to, took you to
the end of the data in the cell, and the number was already prefixed with an =
sign. I can't see how that doesn't give you what you needed.

You press F2 and bingo, you are where you needed to be and exactly where your
macro will take you.

 

[Dave Peterson]

I think the OP wanted to be able to click the Fx button to the left of the
formula bar and keep the existing contents.

In xl2002, if I have 1234 in a cell and hit Fx, I get = and the insert function
dialog--the original contents are lost.

 

[klb]

Dave,

You are correct. What I want is to preserve the existing
contents of the cell and add the = sign (if there is only
a number in the cell) for editing. The fx button erases
the original contents and pressing F2 does not insert the
= sign - at least in Excel 2003.

The solution I posted does work in Excel 2003 and Excel 97
(home machine). Not needed in Excel 97 but it does work
there anyway.

 

[Ken Wright]

Hi Dave - Agreed, if the need is for a button to do it, but that just seems an
unncessary step to me as opposed to just hitting F2 after having run my scenario
against all the data on the sheet.

'assign to a custom button on any toolbar
'Allows editing of an existing number, places = sign at start of entry
'Activates cell for editing

If there is new data being added to the sheet into blank cells, then my solution
is mandraulic and I'd go with the macro, but if the data is already there, and
it is just to edit that data, then the button would seem mandraulic to me. I'll
take a keyboard shortcut anyday.

 

[klb]

To clarify, my original question was:
--
Does anyone know how to change the button on the Formula
bar so that when you click on it, if there is already a
value in the cell, an equal sign is entered at the
beginning of the existing entry? With Excel 2003, it
erases the existing entry and provides you with a dialog
box to insert a new formula.

For a variety of reasons, I want to add to an existing
entry instead of replacing it with something else.
--
It is reasonably clear that I was not asking about new
data in a blank cell.

An = is not automatically added when you type in a
number. If you know of a way to do that (other than the
code I wrote), I would like to know how to set that option
in Excel.

Pressing F2 does not add the = for an existing number.

The code I posted can be assigned to a keystroke shortcut
if you don't like buttons.

-----Original Message-----
Hi Dave - Agreed, if the need is for a button to do it, but that just seems an
unncessary step to me as opposed to just hitting F2 after having run my scenario
against all the data on the sheet.


If there is new data being added to the sheet into blank cells, then my solution
is mandraulic and I'd go with the macro, but if the data is already there, and
it is just to edit that data, then the button would seem mandraulic to me. I'll
take a keyboard shortcut anyday.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

---------------------------------------------------------- ------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------

------------------

 

[David McRitchie]

you do not, should not precede a number with an = sign,
unless for instance you want what normally would be a constant
to be a formula. Maybe that is not what you meant but it looks
to me like a Lotus 1-2-3 thing, and if you have Transition options
turned on would suggest turning them off.

In other words I don't see a problem with F2.

--

 

[Ken Wright]

LOL - We're probably talking by each other here, but just to try and clarify
once and for all. Assuming you had a 1000 numbers in cells somewhere, your
problem was that if you hit F2 on any of these numbers then it takes you to the
end of the cell without an = at the front, whereas the previous fx button would
precede the entry with an = and then take you to the end of the cell ready to
put in another number as well.

What I had suggested would have instantly put an = in front of every one of
those numbers without changing the value, and then, at any time later, if you
had hit F2 on any of those numbers, you would have been in the same position
your macro gets you to, as it would take you to the end of the cell, and there
would already be an = in front of the number. This would however have done this
to every number you selected on the assumption that you may want to edit any of
those numbers. It also converts all constants to formulas (As Dave pointed
out), so may not be a good route anyway.

 

[klb]

To Ken:
Yes we are talking past each other. The spreadsheet I am
working with requires entering and then editing numbers
from several printed sources, has subtotals and totals in
the same column (for different purposes) and is outlined.
Some cells in the same column are text and some are
dates. If I had to do over again, it would be designed
differently but it would take more time than what I have
now. Thanks for your comments. Also, thanks for the new
word - mandraulic.

To David McRitchie:
And what would you do if you have 43291 already in entered
the cell and want to add 23455 to that? The current fx
button on the formula bar will replace the entry and ask
what function you want to use. If you hit F2, you need to
go the left of your current entry and type an = then go to
the end of the entry and type in +23455. The result will
be =43291+23455. Even if you entered it as +43291+23455,
Excel will change the first + to = when you hit enter.

Not a Lotus 123 thing although I will admit to having a
macro4.0 sheet in my Personal.xls with a few converted 15
year-old Lotus macros that are easier to understand than
the equivalent VBA code.


The code I posted makes my work easier. No one else needs
to use it but they are free to do so if they want - be it
on a button or a keyboard shortcut.

Curiosity may have killed the cat - but satisfaction
brought it back.

-----Original Message-----
To clarify, my original question was:
--
Does anyone know how to change the button on the Formula
bar so that when you click on it, if there is already a
value in the cell, an equal sign is entered at the
beginning of the existing entry? With Excel 2003, it
erases the existing entry and provides you with a dialog
box to insert a new formula.

For a variety of reasons, I want to add to an existing
entry instead of replacing it with something else.
--
It is reasonably clear that I was not asking about new
data in a blank cell.

An = is not automatically added when you type in a
number. If you know of a way to do that (other than the
code I wrote), I would like to know how to set that option
in Excel.

Pressing F2 does not add the = for an existing number.

The code I posted can be assigned to a keystroke shortcut
if you don't like buttons.

-----Original Message-----
Hi Dave - Agreed, if the need is for a button to do it, but that just seems an
unncessary step to me as opposed to just hitting F2

after
having run my scenario

against all the data on the sheet.


If there is new data being added to the sheet into blank cells, then my solution
is mandraulic and I'd go with the macro, but if the data is already there, and
it is just to edit that data, then the button would seem mandraulic to me. I'll
take a keyboard shortcut anyday.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

---------------------------------------------------------

-

 

[Dave Peterson]

Ken, you're asking questions again.

Stop it and just give the posters what they asked for <gd&r>.

Yours is not to reason why....

 

[Ken Wright]

LOL - OK, me shut up now