Double Click Formula Cells

When you double click on a cell and there is a formula in the cell then the
precendents are selected. Is there a way to turn this off or automatically
go back to the cell you were on without any change to the window and its
positioning. What is the VBA for this?

Thanks in advance


0
JJ
9/16/2004 12:34:07 PM
excel 39879 articles. 2 followers. Follow

6 Replies
508 Views

Similar Articles

[PageSpeed] 26

JJ

Tools>Options>Edit  Check "edit directly in cell" and you won't go to
precedents.

 Application.EditDirectlyInCell = True

Gord Dibben Excel MVP

On Thu, 16 Sep 2004 13:34:07 +0100, "JJ" <f@o> wrote:

>When you double click on a cell and there is a formula in the cell then the
>precendents are selected. Is there a way to turn this off or automatically
>go back to the cell you were on without any change to the window and its
>positioning. What is the VBA for this?
>
>Thanks in advance
>

0
Gord
9/16/2004 2:39:14 PM
What if I do not want to edit directly in the cell either?

"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:f99jk0de21iqr8qupv978uu45npmi6jc1b@4ax.com...
> JJ
>
> Tools>Options>Edit  Check "edit directly in cell" and you won't go to
> precedents.
>
>  Application.EditDirectlyInCell = True
>
> Gord Dibben Excel MVP
>
> On Thu, 16 Sep 2004 13:34:07 +0100, "JJ" <f@o> wrote:
>
> >When you double click on a cell and there is a formula in the cell then
the
> >precendents are selected. Is there a way to turn this off or
automatically
> >go back to the cell you were on without any change to the window and its
> >positioning. What is the VBA for this?
> >
> >Thanks in advance
> >
>


0
JJ
9/16/2004 3:03:30 PM
JJ

I have no suggestions.

Gord

On Thu, 16 Sep 2004 16:03:30 +0100, "JJ" <f@o> wrote:

>What if I do not want to edit directly in the cell either?
>
>"Gord Dibben" <gorddibbATshawDOTca> wrote in message
>news:f99jk0de21iqr8qupv978uu45npmi6jc1b@4ax.com...
>> JJ
>>
>> Tools>Options>Edit  Check "edit directly in cell" and you won't go to
>> precedents.
>>
>>  Application.EditDirectlyInCell = True
>>
>> Gord Dibben Excel MVP
>>
>> On Thu, 16 Sep 2004 13:34:07 +0100, "JJ" <f@o> wrote:
>>
>> >When you double click on a cell and there is a formula in the cell then
>the
>> >precendents are selected. Is there a way to turn this off or
>automatically
>> >go back to the cell you were on without any change to the window and its
>> >positioning. What is the VBA for this?
>> >
>> >Thanks in advance
>> >
>>
>

0
Gord
9/16/2004 4:04:00 PM
JJ,

When you double-click a formula cell, it goes into Edit Mode -- you see the
formula right there in the cell.  Those colored "selections" are there as an
editing tool.  You can drag them about to change the formula references.
They're really not selections, and you can ignore them. Once you leave Edit
mode (press Enter), they disappear.

I don't know what you mean by "go back to the cell you were on."  You're
still editing the cell you double-clicked.
-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"JJ" <f@o> wrote in message news:elAWNR$mEHA.2764@TK2MSFTNGP11.phx.gbl...
> When you double click on a cell and there is a formula in the cell then
the
> precendents are selected. Is there a way to turn this off or automatically
> go back to the cell you were on without any change to the window and its
> positioning. What is the VBA for this?
>
> Thanks in advance
>
>


0
nothanks4548 (968)
9/16/2004 4:31:03 PM
JJ,

Copy the code below, right-click on the sheet tab, select "view Code" and
paste the code in the window that appears. This will stop that behavior for
the current sheet.  You could also use the workbook level
Workbook_SheetBeforeDoubleClick event, and if you create application level
events then you could probably do it there as well.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_BeforeDoubleClick( _
    ByVal Target As Range, Cancel As Boolean)
Cancel = True
End Sub


"JJ" <f@o> wrote in message news:eMQHw1$mEHA.4004@TK2MSFTNGP10.phx.gbl...
> What if I do not want to edit directly in the cell either?
>
> "Gord Dibben" <gorddibbATshawDOTca> wrote in message
> news:f99jk0de21iqr8qupv978uu45npmi6jc1b@4ax.com...
> > JJ
> >
> > Tools>Options>Edit  Check "edit directly in cell" and you won't go to
> > precedents.
> >
> >  Application.EditDirectlyInCell = True
> >
> > Gord Dibben Excel MVP
> >
> > On Thu, 16 Sep 2004 13:34:07 +0100, "JJ" <f@o> wrote:
> >
> > >When you double click on a cell and there is a formula in the cell then
> the
> > >precendents are selected. Is there a way to turn this off or
> automatically
> > >go back to the cell you were on without any change to the window and
its
> > >positioning. What is the VBA for this?
> > >
> > >Thanks in advance
> > >
> >
>
>


0
Bernie
9/16/2004 4:48:45 PM
Thanks

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:uz3tI0AnEHA.3324@TK2MSFTNGP15.phx.gbl...
> JJ,
>
> Copy the code below, right-click on the sheet tab, select "view Code" and
> paste the code in the window that appears. This will stop that behavior
for
> the current sheet.  You could also use the workbook level
> Workbook_SheetBeforeDoubleClick event, and if you create application level
> events then you could probably do it there as well.
>
> HTH,
> Bernie
> MS Excel MVP
>
> Private Sub Worksheet_BeforeDoubleClick( _
>     ByVal Target As Range, Cancel As Boolean)
> Cancel = True
> End Sub
>
>
> "JJ" <f@o> wrote in message news:eMQHw1$mEHA.4004@TK2MSFTNGP10.phx.gbl...
> > What if I do not want to edit directly in the cell either?
> >
> > "Gord Dibben" <gorddibbATshawDOTca> wrote in message
> > news:f99jk0de21iqr8qupv978uu45npmi6jc1b@4ax.com...
> > > JJ
> > >
> > > Tools>Options>Edit  Check "edit directly in cell" and you won't go to
> > > precedents.
> > >
> > >  Application.EditDirectlyInCell = True
> > >
> > > Gord Dibben Excel MVP
> > >
> > > On Thu, 16 Sep 2004 13:34:07 +0100, "JJ" <f@o> wrote:
> > >
> > > >When you double click on a cell and there is a formula in the cell
then
> > the
> > > >precendents are selected. Is there a way to turn this off or
> > automatically
> > > >go back to the cell you were on without any change to the window and
> its
> > > >positioning. What is the VBA for this?
> > > >
> > > >Thanks in advance
> > > >
> > >
> >
> >
>
>


0
JJ
9/17/2004 8:04:56 AM
Reply:

Similar Artilces:

click before closing
I have this undermention code that i am using is work ok but i will like to add a next code to it. i will like to have a code that will promp the user to click send data to sheet before closing the form Option Explicit Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("main") 'find first empty row in database iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row 'check for a part number If Trim(Me.TxtDate.Value) = "" Then Me.TxtDate.SetFocus MsgBox "Please enter a part number" Exit Sub End If 'co...

how do i record changing cell data (cell is dde linked)
I have a cell that is constantly changing value, and need to record these changes so I can plot them on a chart. How are they changing? Is a user changing them by typing them in? You can do it using an event macro. Rightclick on the worksheet tab that should have this behavior. Select view code. Paste this into the code window. Then back to excel to test it out: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim DestCell As Range With Target If .Cells.Count > 1 Then Exit Sub If Intersect(.Cells, Me.Range("a1")) Is Nothin...

Excel VB
I have two cells in a workbook that are dates. The cell Start_Date has a valid date in it. The second cell Test_Date is either blank or has a valid date in it. The IsDate test on both cells works fine, however, on a blank cell the Month function extracts "12". Anyone know why? I had to put the month function within a test for IsDate to make it work. Seems like Month function on a blank cell should return something other than a 12. Dave Test code: If IsDate(Range("Start_Date")) Then 'Cell has 11/01/09 in it. MsgBox ("start date a d...

Not showing blank cells in user form list box
The following bit of code populates a UserForm. I would like that none of the blank rows in C5:D14 be shown in the uf. (The cells in column D will always be blank if the corresponding row in column C is blank.) Q1. Can anyone suggest how I might achieve this? Q2. Is it possible to also have another range, namely ("C21:D25") added to the list box so that the list box is populated by ("C21:D25") and ("C5:D14") without any blank rows showing? I have generally used PivotTables to sort the ranges and eliminate blanks, but I wonder if there is a better way. If Activ...

Referring to cells by range names #5
thanks to everyone. here is the formula i was looking for: =indirect(b1) indirect(a3) or =indirect("a") indirect("John") quotes needed with range names, and two spaces in between the indirect -- rllane4 ----------------------------------------------------------------------- rllane47's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1647 View this thread: http://www.excelforum.com/showthread.php?threadid=27830 That Indirect containing the cell addresses needing 2 spaces is a new one on me. ... TWO SPACES ? ! ? ! ? As far as using the r...

Access 2000 Dings with every click!
I just installed Access 2000 on Vista machine. Every time I click on an item in the database window it Dings! Turning off "Provide feedback with sound" doesn't stop it. I don't find Access listed in the Sounds list on the Control Panel. I do want sound for some things, like deleting an object, but these constant dings every time I click are driving me crazy! Where are the event-driven sounds listed and/or how can I get it to stop? I've also got Access 2007 on the machine, and have no problem with it dinging there. Access 2000 is properly referencing the version 9.0...

Make Excel 2003 auto upate cell caluclations
I have the same problem as described below. The answer was given to Excel 2007 but I can't find the same functioin for Excel 2003. Please help. ========== My Excel 2007 has stopped auto updating formula cells when one of the input cells are changed. It will only update the value if i mark the cell and press enter, or save the file. How do I make it auto update again? A: Formulas tab, Calculation Options, check Automatic Tools>Options>Calculation tab>Check Automatic -- Kind regards, Niek Otten Microsoft MVP - Excel "cpliu" <spamfreeliu@yah...

Detect click of combo-box down arrow?
I have a form with lots of co-dependent combo boxes. Certain combinations of selections are valid and some invalid. Some of the combo boxes also have very large rowsources so I postpone loading until the focus lands on the control. The thing is I really don't want to trigger these things in the 'OnFocus' event as it triggers when the user just tabs to the control they want. I really need it to trigger on the click of the down arrow. Is there a way to achieve this other than by overlaying a separate button over the down arrow, Could you use the Click event instead? Regards Je...

grade sheet formula
im a teenager trying to do a project for school on excel. i need to re-do my grade history sheet on excel. this is fine, i've done it all, and i'm fine on that part. where i'm lost at is the points part. the thing is for every class i have a grade. for every grade there are points (A=4, B=3, C=2, D=1, F=0). that and also, if one of my classes is honors gifted, a bit higher in level, you get an extra point (A=5, B=4, C=3, D=2, F=1). and if you're in an AP class, advanced placement, it's 2 more points (A=6, etc.). what i need to know is a formula so that whatever grade...

Formula Changes when data entered in referenced range
I have 5 formulas in a worksheet that refer to a range. When data is entered in the range, the formulas adjust to exclude the cells with data. Range B5:B211 When data is entered in B5, range in the formulas becomes B6:B212. The workbook has nearly 200 worksheets with the same formulas. I'm working in Excel 2003. Does anybody have any idea what's going on????? Might help if you include the formulae that you are using. Regards Trevor "mac849" <mac849@discussions.microsoft.com> wrote in message news:33BDD2CD-C071-4DAE-A821-4967E94C16A3@microsoft.com... >...

formula to subtract time, 12:10 am (00:10)
Hi One way With 00:10 in A1 and 23:50 in B1 =MOD(A1-B1,1) -- Regards Roger Govier "teezee" <teezee@discussions.microsoft.com> wrote in message news:BAAF0132-424F-4491-B9E8-0ABFB9B4D0CB@microsoft.com... > ...

viewing the cell reference as the top row
within a macro I want to have the cell I call be the top row for viewing,= is this possible? yes, it is possible. Use the ActiveWindow.ScrollRow command. For example, setting ActiveWindow.ScrollRow = 10 moves row 10 to the top of the screen. You just need to replace "10" with the row of the cell that you call, it can be a variable, and it should work. Katie "Bob Clark" wrote: > within a macro I want to have the cell I call be the top row for viewing, is > this possible? > God Bless and Thank You!! I've tried several methods BUT how can I now move t...

Line graphs where data comes from formulas.
I am preparing a spreadsheet for use by a colleague and am trying to cut down on the work they will have to do. I have a spreadsheet with 52 tabs (representing each week of the year). Information is entered weekly regarding customer sales and formulas calculate costs of production and sales vs budget. Another tab is called ‘graph data’. Relevant information from the 52 tabs is collected on this sheet, some extra calculations are performed and then a series of line graphs are compiled. One row of excel, lets say row 6 contains information for the horizontal axis. So in cell B6 enter...

Roundup for multiple cells
I have quite a few columns I want to roundup the values. I know how to do =roundup(3.11111,0), but how do I copy that to a bunch of cells? -- STK Instead of 3.11111, put the cell location, such as B2, which should be the first cell that needs the formula at the top of the column. Then copy the formula down using the Fill Handle. http://www.officearticles.com/excel/best_tips_for_using_microsoft_excel.htm ************ Anne Troy www.OfficeArticles.com "STK" <STK@discussions.microsoft.com> wrote in message news:8434BCB1-17D1-4511-9B63-053220AA3731@microsoft.com... >I hav...

Increment Cell Reference
I have a worksheet that starts with daily data, a date, then a dollar value, then there is weekly data that needs to reference the daily data and increment 7 rows each time. Every way I ahve come up with wants to increment by 1, I used OFFSET to move down 7 rows, but then the next weekly cell OFFSET increments the original reference by 1. The daily data references the previous day and then adds any changes. So for the weekly data, I need to just move my reference down 7 rows each week. Thanks, Perry Hi! Is this a macro procedure you're describing or do you need help with a form...

"General" cell format nixes numbers
This is a really simple problem, but I can't seem to figure it out. I have cells formatted as the "general" type. If I type in something like 19, then the cell displays ".19". If I type something in like 123456798, then the cell displays "12345678.8". Sometimes with really long numbers it will display something like "123456798+11". It's happening in all of excel, no matter what workbook is open. It also happens on all computers I log into, so it's probably an excel setting somewhere in my profile. How I do I display standard numbers ag...

copying formulas with fill handle
I have two spreadsheets and want to link info from one sheet to another. I have totals at the bottom of columns and I want to be able to put those totals into another spreadsheet. I want to do this with the fill handle instead of entering one by one. I have totals in lets say B66,C66,D66 and so on. I want to be able to put those numbers in the next spreadsheet in cells B11,B12,B13 and so on. B11 should equal B66 , B12 should equal C66 and so on. Any help would be appreciated. EXCEL 2007 1. Highlight cells B 66 to D 66 in your first spreadsheet. 2. Ctrl-C to copy....

How do I imbed a Word document into an Excel cell?
I am trying to create a database of contact information. It is mostly textual information. I would like to insert reference Word documents into each record. This can be done easily in a Word table. However, Excel places the imbeded object on the sheet, not in a cell. As such, I can not sort, filter, etc. The best I have been able to figure out is to insert a hyperlink, which is unacceptable for my purpose. Thanks in Advance. Baxter, You can link stuff in a Word document to Excel. Select the Word text, Copy. In Excel, in the cell you want the link in, Paste Special - Text - Paste Lin...

Formula Question #12
In the following formula: =IF($Y$2="","",IF(T24=$Y$2,(U24*0.5*$Y$3),IF(V24=$Y$2,(W24*0.5*$Y$3),IF(X24=$Y$2,(Y24*0.5*$Y$3))))-(U24+W24+Y24)) How do I change all of the $Y values to X. Is there a way to do that all at one time? Thanks, Tom On Sun, 26 Nov 2006 12:43:47 -0700, Tom G <invalid@invalid.com> wrote: >In the following formula: > >=IF($Y$2="","",IF(T24=$Y$2,(U24*0.5*$Y$3),IF(V24=$Y$2,(W24*0.5*$Y$3),IF(X24=$Y$2,(Y24*0.5*$Y$3))))-(U24+W24+Y24)) > >How do I change all of the $Y values to X. Is there a way to do that >al...

Outlook 2003: Unable to print email from right click menu unless item is opened
I am a new user to Outlook 2003. In my prior version, after I would read the e-mail message in the inbox, I would right click the message which would bring up the menu. When I click "Print" I get a dialogue box which says "Please open this HTML message to print it." How can I bypass this and just print it without fully opening it as I was able to do in the prior version of Outlook? Thank you! ...

Macro Fill Formula
Hello there im looking for a formula that will fill all blank cell from A1 to AA1020 with "0" without affecting the blank cell properties i.e. filled colors, sizes, etc. thanks You may already have understood this, palups, but no formula will fill OTHER cells for you; it'll only affect the cell it's typed in. That's by design; otherwise we'd all be capable of royally messing ourselves up. If you have a large area that has a lot of formatting in it but no data, then what you want is a way to copy 0 into the DATA of each cell without affecting the FORM...

Formula Problem for Newbie
I have 4 columns : A1 is date; B1 is Advance; C1 is Deposit and D1 is Total... How do I do this formula so that D1 shows the total. The total column will be in a negative because the Advances are more than the Deposits but I think if I just insert the Advance with a - sign, this should work. Have spent an hour trying to figure out this simple thing, so finally decided to ask for help. Thanks! If the Advance are positive values use =C1-B1 Otherwise =C1+B1 best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Terri" <t...

HOW TO CHANGE A FORMULA NAME IN A PAGE TO OTHER PAGE WITHOUT 1 BY.
I need to change the formula in the page in every link to a different page in my work book. How can I do that with out having to fo it one by one in each cell? If I understand correctly, you have something that you need to change i a bunch of different cells to a different formula? If so, this is little quicker but it won't do them all at once (I'm not sure how to d that). But if you change the formula in one cell, then highlight tha cell, use format painter (the little paintbrush on your toolbar) an highlight the new cell you wish to put the formula in. The only thin is that ...

change a color of a cell based on it's contents
Hello, I read many messages about the conditionnal formatting but only three levels are not enough. I explain what I want to do : I've an excell sheet containing some datas taken from the volume occupied by a database. During the day, the database is growing up, and in the morning, a cleaning is performed. We pick up at regular interval during the day. I would like to color the cell based on the % it content from a dark green to a dark red. Something like that : 1-5 : very dark green 6-10 : dark green 11-15 : grenn 16-20 : light green 21-25 : very light green 26-30 : a little bit yellow ...

Transfer specific characters from cell to another cell.
Ok, I have this sheet that has 700 cells which includes name, last name of the person and his/her address in it. note: Name, last name and address are in the same cell. Now I would like to transfer JUST the names and last name to another cell. Is it possible to do that? if so, how? I don't want to erase 700 address of each cell and then copy and paste the name/last name. That will take a lot of time. Could somebody guide me? please, I am willing to learn. Regards. Hi What is the separator between Name Lastname and Address? Is it space, comma or something else? Assuming a Comma, ...