Free Microsoft Excel 2013 Quick Reference

Arrow keys not working Excel 2007 all spreadsheets

Arrow keys do not move from one cell to the next. They advance the page or panel view. No spreadsheet works correctly, (new or existing) and scroll lock is not set. Help appreciated!


Suddenly, inexplicably, my keyboard arrow keys stopped working recently. Now, rather than navigate to/from highlighted cells, the arrow keys move the entire screen left right up & down. In addition, SHIFT+PG UP and SHIFT+PG DN are not working. And HOME now navigates to the top left of the visible screen, as opposed to Column A of the Row that I am working in.

Did I inadvertently switch something on or off? Does anyone know how to return my arrow keys to their normal functioning?

DPKologie

Hi guys,

I have just come across something I have not quite seen before.

I am working with an Excel workbook, and the Ctrl + arrow key function is not working as I would expect. When used it goes the furthest populated cell rather than the next populated cell. So when I am in cell B1 and expect to go to cell B15 when I use Ctrl + down, it ends up in cell B15000, which is the last cell with a value in it.

Now I know that the data is pasted into the Excel sheet from another -non Microsoft- programme. I have tried copying all the data and then using paste-special-values, which worked with similar problems, but that doesn't do anything.

It's not like the data is just one big block that looks like it takes up the whole thing, because it will still sort according to columns. The Find function also works, which suggests it does recognise values in cells. And yet the Ctrl + arrow keys don't work.

Does anybody have any idea what might be going on here? Any suggestions would be most welcome.

When I copy cells in Excel 2007, then try to paste it within the same
worksheet or a different worksheet, the Paste function is not highlighted.
This function has worked just fine in the past, but all of a sudden I can't
select Paste.

On one of my Excel 2007 installations, the keyboard arrows not only move to the next cell, but also shift the whole spreadsheet. Does anyone know what is going on and how to fix it. Thanks!

Hi again

Have a file that I have been coding and find when I open to a worksheet with limited access cells, the Up and Down arrows will not move vertically though Lrft and Right arrows OK though not raping arround.

This problem may have been on for sometime and I haven't been back to it.
Other Excel files all OK.

Well another pretty please help

Pricy

I have built a sheet that a user will enter data. To move from cell to cell
the user would use the arrow keys. I locked all non entry cells and then
protected the sheet. Now the up down arrow keys don't work nor does the tab
key wrap back to column A when it reaches the end of the sheet.
I have two other sheets that do this correctly and have tried to review all
opptions in case I had set something incorrectly but can't find the error of
my ways...
Thanks for reading my tale,
John

I have run across something I cannot figure out on a sheet I am working with. I have four columns I have locked so that the formulas cannot be messed with, and I also have a filter running on about 10 columns. When I protect the sheet, only my right and left arrow keys will work. I cannot get the up and down arrow keys to work. I am able to click on any unlocked cell with the mouse and it will work normal, but I cannot figure out what is causing the up and down arrow keys to "lock". I have gone thru all the options that you get when you protect the sheet and none of them seemed to make a difference. Any help would be greatly appreciated. Thanks!

Hi,

How do you resize a line or arrow shape horizontally in Excel 2007? I tried
pressing shift and dragging which worked in previous versions of Excel but
in 2007 the line extends indefinitely.

Thanks,

David

I used to be able to use the arrow keys to move around cells but now they
scroll the page. This is happening in files that don't have freeze frames and
ones that do.
I have to place the cursor with my mouse.
Is there a setting that inadvertantly got set?
Thanks

is there a setting that isn't allowing my arrow keys to work in Excel?
Additionally, my cursor will not select a cell - it only highlites cells. I
can't use the menu bar or even quit out of excel with Ctrl+Alt+Del. Arrow
keys and cursor work in all other applications

is there a setting that isn't allowing my arrow keys to work in Excel?
Additionally, my cursor will not select a cell - it only highlites cells. I
can't use the menu bar or even quit out of excel with Ctrl+Alt+Del. Arrow
keys and cursor work in all other applications

Hi,

I used the following code in Excel 2003 to update some sql queries, then the pivot tables that run from them.
The probelm i am having is that we have switched to Excel 2007 and now the Queries do not automatically refresh using this code.
What changes do i need to make for this to work in 2007?


	VB:
	
 RefreshWireForecastTables() 
     '******  procedure to refresh all Query Tables then all Pivot Tables on a spreadsheet **********
     'to be used via a refresh button
    Dim sh As Worksheet 
    Dim qt As QueryTable 
    Dim pt As PivotTable 
     
     'update QueryTables
    For Each sh In ActiveWorkbook.Sheets 
        For Each qt In sh.QueryTables 
             
            qt.EnableRefresh = True 
            qt.BackgroundQuery = False 
            qt.Refresh False 
            Application.StatusBar = False 
            DoEvents 
        Next 
    Next 
     'update PivotTables
    For Each sh In ActiveWorkbook.Sheets 
        For Each pt In sh.PivotTables 
             
            pt.RefreshTable 
            DoEvents 
            Application.StatusBar = False 
        Next 
    Next 
     'Update time last refreshed message and then save
    Dim lastrefresh As String 
    lastrefresh = Now() 
    Range("C10").Select 
    ActiveCell.Value = lastrefresh 
     
    ActiveWorkbook.Save 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


When I am in excel (2007) the up & down arrow keys are not functioning. Typically when the up/down keys are used it will navigate me around the different cells. Today however it is not, instead it simply moves the entire excel sheet either up or down.
Anyone ever experienced this? Definitely could use any sort of advice as this is very frustrating.

Why would My F2 key not be working in Excel 2007?
Each time I hit the F2 key it just does nothing. I'm so used to typing
F2 and then the text to append to the cell, but it just overwrites
because F2 won't open the cell for editing.
I've tried with both "Allow Editing in Cells" option turned on and off
and it makes no difference.
I'm sure it's just a simple setting but I'm not sure what it is...
does anyone know?

Thanks

JodieM

Hi!

This "feature" is driving me nuts! When I use the arrow keys in the
excel worksheet, I'm not moving the marker between cells, but instead
I'm scrolling the work sheet, e.i. the entire sheet is moving, but the
marker remains on the same cell. This goes for all documents I open in
Excel.

Please help me restore my dear Excel!

regards,
Andreas

I have an Excel 2003-format spreadsheet that I have opened in Excel 2007.
The spreadsheet has links to another (external) 2003-format spreadsheet which
I do not have.

In Excel 2003, when I open the file I am given a dialog box to choose
whether or not to update the links. If I choose to NOT update, then the
values as of the last time the link was updated are displayed.

In Excel 2007, opening the same spreadsheet does not give a dialog box
asking if I want to update the links. Instead, the spreadsheet opens with
all the linked cells (and those depending on the linked cells) displaying
#VALUE!, NA, or #NAME?

How can I open the spreadsheet in 2007 and have it display the last values
of the links?

Hi!

This "feature" is driving me nuts! When I use the arrow keys in the
excel worksheet, I'm not moving the marker between cells, but instead
I'm scrolling the work sheet, e.i. the entire sheet is moving, but the
marker remains on the same cell. This goes for all documents I open in
Excel.

Please help me restore my dear Excel!

regards,
Andreas

Hi all,

The following code snippet works fine in excel 2003,


	VB:
	
ButtonID = ActiveSheet.Shapes(Application.Caller).AlternativeText 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
But in 2007 it returns the active shape name instead of the shapes AlternativeText, any ideas what the 2007 code would be?

Regards
Padster

hey,

the CommonDialog function works fine in excel 2003 for my function, but not in excel 2007, is there some way to turn CommonDialog on in excel 2007? here is the code is that is of any help:

Option Explicit

Dim l As String

' General Variables
Dim l_num_loop                  As Long              ' Variable for Arrays
Dim l_num_row                   As Long              ' Row Number of Active Cell
Dim l_num_col                   As Long              ' Column Number of Active Cell
Dim l_num_row_val               As Long              ' Row being Validated
Dim l_num_col_val               As Long              ' Column being Validated
Dim l_dte_curdate               As Date
' Excel Sheet Variables
Dim l_chr_pds_class_id                As String
Dim l_chr_Mod_Number                    As String
Dim l_chr_associated_models As String
Dim l_chr_tech_descr    As String
Dim l_chr_mod_descr As String
Dim l_chr_item_type_code As String
Dim l_chr_source_of_mod As String
Dim l_chr_country   As String
Dim l_chr_bu As String
Dim l_chr_currency As String
Dim l_num_cost  As Single
Dim l_dte_cost_from As Date
Dim l_dte_cost_to   As Date
Dim l_chr_attribute_term As String
Dim l_num_value As Integer
Dim l_dte_eol_date As Date
Dim l_chr_filename As String
' Variable for the Dialog box for the Saving the Excel Sheet
Dim obj_comm As New CommonDialog



' Procedure for generating the Flat File
' when the user presses Generate Button
Sub Generate_Click()

    ' Check if supplier is valid
    check_supplier

    ' Intialize the Column and Rows
    l_num_col = 1
    l_num_row = 1
    l_num_loop = 0
    'l_chr_filename = "CST-" & Format(Now, "DD-MM-YYYY")
    l_chr_filename = "cst_noncepam_costs"
     
    '================================================================
    ' Call to Procedure to Create Directory for CPL Spare Part Upload
    '===============================================================
        
        Create_Dir
        
          ' To Save the Excel Sheet using Common Dialog Box
        
        On Error GoTo On_Cancel
                  
          ' To suggest user to save the Save the File with the given Name
          obj_comm.Filename = "emf3intranet03servicecost" & l_chr_filename
          
          'To Save the File with Xls Extension
          obj_comm.Filter = "Microsoft Excel Workbook | *.xls"
          
          ' To remove the Read Only Check Box from the COmmon Dialog Box
          obj_comm.Flags = cdlOFNHideReadOnly
          
          'To code for the Cancel Button on the COmmon Dialog Box
          obj_comm.CancelError = True
          
          'To show the Common Dialog Box
          obj_comm.ShowSave
         
          'To Save the Excel sheet in the Path and Name Selected
          Sheet5.SaveAs obj_comm.Filename
          MsgBox "Excel Sheet Saved under path " & obj_comm.Filename, vbInformation, "Service costs
interface"
          

' When user presses  cancel button the the Cursor comes here
On_Cancel:
          
      On Error GoTo Gen_err

        ' Open The File to write the Flat File
         Open "emf3intranet03servicecost" & l_chr_filename & ".dat" For Append As #1
          
    ' Loop for all the Reocords in the File
      Do While (Trim(Sheet5.Cells(l_num_row + 1, l_num_col)) <> "" Or Trim(Sheet5.Cells(l_num_row + 1,
l_num_col + 1)) <> "" Or Trim(Sheet5.Cells(l_num_row + 1, l_num_col + 2)) <> "" Or
Trim(Sheet5.Cells(l_num_row + 1, l_num_col + 3)) <> "" Or Trim(Sheet5.Cells(l_num_row + 1, l_num_col + 4))
<> "" Or Trim(Sheet5.Cells(l_num_row + 1, l_num_col + 5)) <> "" Or
Trim(Sheet5.Cells(l_num_row + 1, l_num_col + 6)) <> "" Or Trim(Sheet5.Cells(l_num_row + 1, l_num_col + 7))
<> "" Or Trim(Sheet5.Cells(l_num_row + 1, l_num_col + 8)) <> "" Or
Trim(Sheet5.Cells(l_num_row + 1, l_num_col + 9)) <> "" Or Trim(Sheet5.Cells(l_num_row + 1, l_num_col + 10))
<> "" Or Trim(Sheet5.Cells(l_num_row + 1, l_num_col + 11)) <> "" Or
Trim(Sheet5.Cells(l_num_row + 1, l_num_col + 12)) <> "" Or Trim(Sheet5.Cells(l_num_row + 1, l_num_col + 13))
<> "" Or Trim(Sheet5.Cells(l_num_row + 1, l_num_col + 14)) <> "" Or
Trim(Sheet5.Cells(l_num_row + 1, l_num_col + 15)) <> "")
          
        'Set the row Variable to the Next Row after the label
        l_num_row = l_num_row + 1
                
        
        ' To capture the number of rows in excel Sheet
        l_num_loop = l_num_loop + 1
         
            
        l_chr_pds_class_id = Trim(Sheet5.Cells(l_num_row, l_num_col))
        l_chr_Mod_Number = Trim(Sheet5.Cells(l_num_row, l_num_col + 1))
        l_chr_associated_models = Trim(Sheet5.Cells(l_num_row, l_num_col + 2))
        l_chr_tech_descr = Trim(Sheet5.Cells(l_num_row, l_num_col + 3))
        l_chr_mod_descr = Trim(Sheet5.Cells(l_num_row, l_num_col + 4))
        l_chr_item_type_code = Trim(Sheet5.Cells(l_num_row, l_num_col + 5))
        l_chr_source_of_mod = Trim(Sheet5.Cells(l_num_row, l_num_col + 6))
        l_chr_country = Trim(Sheet5.Cells(l_num_row, l_num_col + 7))
        l_chr_bu = Trim(Sheet5.Cells(l_num_row, l_num_col + 8))
        l_chr_currency = Trim(Sheet5.Cells(l_num_row, l_num_col + 9))
        l_num_cost = Sheet5.Cells(l_num_row, l_num_col + 10)
        l_dte_cost_from = Format(Trim(Sheet5.Cells(l_num_row, l_num_col + 11)), "DD/MM/YYYY")
        l_dte_cost_to = Format(Trim(Sheet5.Cells(l_num_row, l_num_col + 12)), "DD/MM/YYYY")
        l_chr_attribute_term = Trim(Sheet5.Cells(l_num_row, l_num_col + 13))
        l_num_value = Sheet5.Cells(l_num_row, l_num_col + 14)
        l_dte_eol_date = Format(Trim(Sheet5.Cells(l_num_row, l_num_col + 15)), "DD/MM/YYYY")
        
        ' Print The Line to the Flat File
        Print #1, Trim(l_chr_Mod_Number); "|"; Trim(l_chr_country); "|";
Trim(UCase(l_chr_source_of_mod)); "|"; Trim(l_chr_currency); "|"; l_num_cost; "|";
Format(l_dte_cost_from, "DD/MM/YYYY"); "|"; Format(l_dte_cost_to, "DD/MM/YYYY"); "|"
        

    Loop     ' End of Loop for all the lines in the Excel Sheet
       
    ' Close the Flat File
      Close
    MsgBox "Flat file saved under the path " & "emf3intranet03servicecost" & l_chr_filename &
".dat", vbInformation, "Service Costs"
    
    Exit Sub
    
'================
' Exception Block
'================
   
' Exception block to Catch any errors in the SQL Creation for Call to Procedure
sql_err:

    MsgBox "Error in call to Procedure for Creating Flat File", vbCritical, "Cost Update"
         
    Exit Sub
' Exception Block to catch general Errors
Gen_err:

    MsgBox "Error " & Err.Description, vbCritical, "Cost Update"
    Close
    Exit Sub
   
End Sub

' Procedure for Creating a Directory on the Server and if it exists then Remove the Flat file
' existing under it

Public Sub Create_Dir()

Dim l_chr_file      As String            ' Variable to store the File name
Dim l_chr_filename  As String            ' Variable to store the File name

On Error GoTo Dir_err

  ' Makes the Directory Under the NT Server
      MkDir "emf3intranet03servicecost"
  Exit Sub

' Exception Block to Delete the Flat file and file having total number of lines
' from the Directory if the Directory Exists
Dir_err:
              
        l_chr_filename = "cst_noncepam_costs"
        l_chr_file = Dir("emf3intranet03servicecost" & l_chr_filename & ".dat")
        If l_chr_file = l_chr_filename & ".dat" Then
          ' Flat File Deleted
              Kill "emf3intranet03servicecost" & l_chr_filename & ".dat"
        End If
End Sub


Public Sub check_supplier()
' Intialize the Column and Rows
    l_num_col = 7
    l_num_row = 1
    l_num_loop = 0
Do While (Trim(Sheet5.Cells(l_num_row + 1, l_num_col)) <> "" Or Trim(Sheet5.Cells(l_num_row + 1, l_num_col +
1)) <> "" Or Trim(Sheet5.Cells(l_num_row + 1, l_num_col + 2)) <> "" Or
Trim(Sheet5.Cells(l_num_row + 1, l_num_col + 3)) <> "" Or Trim(Sheet5.Cells(l_num_row + 1, l_num_col + 4))
<> "" Or Trim(Sheet5.Cells(l_num_row + 1, l_num_col + 5)) <> "" Or
Trim(Sheet5.Cells(l_num_row + 1, l_num_col + 6)) <> "" Or Trim(Sheet5.Cells(l_num_row + 1, l_num_col + 7))
<> "" Or Trim(Sheet5.Cells(l_num_row + 1, l_num_col + 8)) <> "" Or
Trim(Sheet5.Cells(l_num_row + 1, l_num_col + 9)) <> "" Or Trim(Sheet5.Cells(l_num_row + 1, l_num_col + 10))
<> "" Or Trim(Sheet5.Cells(l_num_row + 1, l_num_col + 11)) <> "" Or
Trim(Sheet5.Cells(l_num_row + 1, l_num_col + 12)) <> "" Or Trim(Sheet5.Cells(l_num_row + 1, l_num_col + 13))
<> "" Or Trim(Sheet5.Cells(l_num_row + 1, l_num_col + 14)) <> "" Or
Trim(Sheet5.Cells(l_num_row + 1, l_num_col + 15)) <> "")
          
        'Set the row Variable to the Next Row after the label
        l_num_row = l_num_row + 1
                
        
        ' To capture the number of rows in excel Sheet
        l_num_loop = l_num_loop + 1
        
        ' Check if supplier is one of Serice, Lpp, Freight or Warranty
        ' Added by Mani to also include Dellware, DW SKU Manager
        If UCase(Trim(Sheet5.Cells(l_num_row, l_num_col))) <> "SERVICE" And
UCase(Trim(Sheet5.Cells(l_num_row, l_num_col))) <> "LPP" And UCase(Trim(Sheet5.Cells(l_num_row, l_num_col)))
<> "FREIGHT" And UCase(Trim(Sheet5.Cells(l_num_row, l_num_col))) <> "WARRANTY" And
UCase(Trim(Sheet5.Cells(l_num_row, l_num_col))) <> "DELLWARE" And UCase(Trim(Sheet5.Cells(l_num_row,
l_num_col))) <> "DW SKU MANAGER" Then
             GoTo invalid_supp
        End If
  Loop
 Exit Sub
invalid_supp:
  MsgBox " Invalid Supplier " & Trim(Sheet5.Cells(l_num_row, l_num_col))
    End
        
        

End Sub


We have 8 linked spreadsheets which all feed into another spreadsheet. On two machines if we open up the 2 main spreadsheets and hit F9 to calculate all the figures update correctly. If on onther machines they open up the same 2 spreadsheets and hit F9 to calculate the formula fields show #Value. If we open all 8 spreadsheets and F9 on each one then they work fine. It takes 45 minutes to open all 8 which means it is not viable to have all 8 open. Why does it work on 2 machines and what do we need to do to get it to work on all other machines.

The right 'Alt' key does not work in Excel 2003. The key plus the <enter> key
provides a paragraph break in a cell of text. When I discovered the problem
at work, I had to have MS Office re-installed (a few times!), but is not my
preferred option at home.

Hello,

I'm a beginner with Excel programming. I inherited a report developed in Excel 2003. The report has a few charts. The axes on the chart change based on a VBA code. The scale changes in 2003 but does not work in 2007. So the graph appears to cut of the values falling out of range. Here is the code. Any help is appreciated.

Private Sub ComboBox1_Change()
On Error GoTo 1
With Worksheets("CPC, &#37; Supply-NPSR").ChartObjects(2).Chart.Axes(xlValue)
.MaximumScale = Range("AF2").Value
.MinimumScale = Range("AF3").Value
End With

With Worksheets("CPC, % Supply-NPSR").ChartObjects(1).Chart.Axes(xlValue)
.MaximumScale = Range("AE2").Value
.MinimumScale = Range("AE3").Value
End With

I have an Excel 2007 workbook with a macro that takes the data from one worksheet and makes a seperate TXT file. It works fine on two on my computers. On the third, the macro runs and gives no indication of an error, but not all of the data is transfered to the TXT file. All the systems have Excel 2007, all updates with the same SP , updates, ect..
Any help is appreciated.

Hi all,

for a project I need to fit a 2nd order polynomial (ax^2+bx+c) on a lot of data. From this fit I need to get the x,y that corresponds to its maximum.

Normally I did this with a graph, get the fit and was able to calculate the X and Y for the maximum. The project is getting bigger and bigger and I want to automate this task.

I found that I can use the LINEST function in excel but somehow it does not work for me.

The spreadsheet has been build so that on every row, there is data from 1 sample.

the formula I’m using and is not working (returning #value) :

=LINEST(AV16:AZ16;AQ16:AU16^{1,2})

the strange thing is that if I rearrange the data so that I can have I have my X and Y in a different column it does work (formula: =LINEST(AS19:AS23;AR19:AR23^{1,2})

now my question is, is there a way to get a 2nd order fit using excel formulas using the row data as an array instead of column data?

It is not possible to rearrange the data to columnar data in the sheet itself. The only solution if the LINEST function cannot work on rows is to use VBA and make it loop through the data put transpose my x, transpose my Y and store in temp in an array. Then do the LINEST function.. I'm not very good with VBA so I rather not start attacking this problem in this way.

Hope some of you have an idea how to tackle this problem!
tx!