Free Microsoft Excel 2013 Quick Reference

Autofilter arrow wont go away...

Has anyone experienced this???

I have a list of data about 1300 rows and 44 columns. I apply the autofilter to the first row (headers). - no problems. functions correctly.

The problem is that when I REMOVE the autofilter both manually or programmatically, occassionally some of the cell arrows remain in the cells.

This is a random occurance and the arrows never go away. I have tried 'clearall', reapplying the autofilter...the 'dead arrow' remains. the cell can be edited, in fact all properties of the cell are configurable but the arrow remains! ?


Post your answer or comment

comments powered by Disqus
I have unchecked 'auto filter' but the arrows still remain and will not go
away. They do nothing, no drop downs.

Can anyone help?

I would like to know a way to hide the autofiltering arrows AFTER the autofiltering is done. To hide ALL the arrows on a sheet without taking away the autofilter.

Is there a way to do this without doing this when the filtering for each column is done? or without removing the criteria

I was using speech recognition to enter data into cells. It misunderstood me in several cells and placed an @ symbol at the end of the data. Now if you click up on that cell it tries to send an email. How do I make this link go away? everytime I type data into the cells, they are blue and underlined and create this email link. Thanks for the help guys, and I apologize if this should have been in the Excel/Email section

Hi there. I'm hoping someone might know if the colour of the active autofilter arrow can be changed from Blue to another brighter colour like red?

A number of the people who use my spreadsheets have trouble spotting active filters, especially if someone else has saved a sheet with one already applied.

I realise that I am probably missing something fairly basic here but I am largely self taught in Excel and don't know all of the setup options and features.

Many thanks in advance...

gtvkeith

Hello All,

I found this code for hiding an autofilter arrow for column 2. I would also like to hide the arrows for columns 35 through 50. Does anybody know how to do that? Thanks.

Private Sub Workbook_Open()
'hides all arrows except column 2
Dim c As Range
Dim i As Integer
i = Cells(1, 1).End(xlToRight).Column
Application.ScreenUpdating = False
For Each c In Range(Cells(1, 1), Cells(1, i))
If c.Column 25 Then
c.AutoFilter Field:=c.Column, _
Visibledropdown:=False
End If
Next
Application.ScreenUpdating = True

End Sub

Hi,

I am getting tuples from MySQL via ODBC and excel shows it as a table.
I told Excel to show sums at the bottom of each column. It works. I have the sums of the columns.

However, whenever I click refresh (to refresh the table data) the totals go away and I have to click each total again and say "sum"... "sum"... "sum"...

Any idea how I could tell excel to leave the totals alone and only refresh the data inside the table ?

Thank you

Hello all,

Using Excel 2003.

I entered this "Copy and paste prevention" macro in my workbook following the direction below and I got rid of it but it won't go away. Even when I closed Excel....I can't even copy and paste in any spreadsheet now.

Can someone tell me how to get rid of this!? I went into the modules and deleted??

Thanks,
Eric

Sub ToggleCutCopyAndPaste(Allow As Boolean)
'Activate/deactivate cut, copy, paste and pastespecial menu items
Call EnableMenuItem(21, Allow) ' cut
Call EnableMenuItem(19, Allow) ' copy
Call EnableMenuItem(22, Allow) ' paste
Call EnableMenuItem(755, Allow) ' pastespecial

'Activate/deactivate drag and drop ability
Application.CellDragAndDrop = Allow

'Activate/deactivate cut, copy, paste and pastespecial shortcut keys
With Application
Select Case Allow
Case Is = False
.OnKey "^c", "CutCopyPasteDisabled"
.OnKey "^v", "CutCopyPasteDisabled"
.OnKey "^x", "CutCopyPasteDisabled"
.OnKey "+{DEL}", "CutCopyPasteDisabled"
.OnKey "^{INSERT}", "CutCopyPasteDisabled"
Case Is = True
.OnKey "^c"
.OnKey "^v"
.OnKey "^x"
.OnKey "+{DEL}"
.OnKey "^{INSERT}"
End Select
End With
End Sub

Sub EnableMenuItem(ctlId As Integer, Enabled As Boolean)
'Activate/Deactivate specific menu item
Dim cBar As CommandBar
Dim cBarCtrl As CommandBarControl
For Each cBar In Application.CommandBars
If cBar.Name "Clipboard" Then
Set cBarCtrl = cBar.FindControl(ID:=ctlId, recursive:=True)
If Not cBarCtrl Is Nothing Then cBarCtrl.Enabled = Enabled
End If
Next
End Sub

Sub CutCopyPasteDisabled()
'Inform user that the functions have been disabled
MsgBox "Sorry! Cutting, copying and pasting have been disabled in this workbook!"
End Sub

'*** In the ThisWorkbook Module ***
Option Explicit

Private Sub Workbook_Activate()
Call ToggleCutCopyAndPaste(False)
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call ToggleCutCopyAndPaste(True)
End Sub

Private Sub Workbook_Deactivate()
Call ToggleCutCopyAndPaste(True)
End Sub

Private Sub Workbook_Open()
Call ToggleCutCopyAndPaste(False)
End Sub

How to use:

Copy above code for the standard module.
In Excel press Alt + F11 to enter the VBE.
Press Ctrl + R to show the Project Explorer.
Right-click desired file on left (in bold).
Choose Insert -> Module.
Paste code into the right pane.
Copy the code for the ThisWorkbook module.
In the project explorer, locate the ThisWorkbook object.
Double click the ThisWorkbook object.
Paste code into the right pane.
Press Alt + Q to close the VBE.
Save workbook before any other changes.
Close and reopen the workbook.

Hi, all:

One of my users has a blue line appearing in a worksheet that will not go away! It is not an object, nor a border. If we try to delete the cells (i.e. whole row/column) the line remains!

Copy ing and pasting the contents of the sheet in a new worksheet -- and avoiding the line -- is possible, but if any part of the line is in the copy area, the line also appears in the new paste.

Problem is, she's used the same worksheet as the template for a multi sheet workbook -- hence all sheets now have the blue line -- although the line length can vary by sheet!

Any ideas?

Thank you!

I have a user using Excel 2007, who copies a sheet to a new workbook. The user clicks Office Button, Prepare, Edit Links to Files menu to reach the link dialog box.
Clicks Break Link button, then clicks the second Break Links button.
Closes the Edit Links dialog box to complete the steps.
Then clicks back on Office button etc. to bring up the Edit Links dialog box and the link is still showing up.
Edit Links to Files is still on the Prepare menu, and the file is still in the dialog box.
For everyone else the menu is gone when these steps are followed.

Any one have any idea why the menu selection is not going away.
We check the cell in the spreadsheet and there is no longer a link, just the value.

Thanks, Jesseb

How do you make the little autofill box in Excel go away? This box appears
next to cell when I use the drag-copy feature of the pointer.

Hi all

As it's Friday I have a big hole in my brain and stuff is leaking out and I can't think of how I need to proceed

I have several worksheets where the top half has information that I do not need filtered, and the bottom portion has information that I do quite a lot of filtering on.

To complete a project I'm working on I need to know what row my autofilter arrows are on. Does anyone have a piece of code that could help me out here?

Thanks for any suggestions

DJB

Hi
I am a newbie at vba. I have a table with about a dozen columns. I only want to show Autofilter arrows on certain columns. Following is the procedure I wrote to do this - but it is slow - Can anyone suggest a more efficient way of doing this?
Thanks
Richard

Sub setFilterColumns()
' Triggered by worksheet button - toggle Autofilter on and off
' If autofilter is on turns it off - if turned on - hides autofilter arrows don't want to reveal

Dim FilterRange As Range

Set FilterRange = Range("DataEntryToToList")

' If Autofilter already on turn it off
If ToDo.AutoFilterMode Then
   Todo.AutoFilterMode = False
    Exit Sub
End If

Application.ScreenUpdating = False

' hide the Autofilter arrows for the following columns
With FilterRange
    .AutoFilter Field:=2, Visibledropdown:=False
    .AutoFilter Field:=4, Visibledropdown:=False
    .AutoFilter Field:=6, Visibledropdown:=False
    .AutoFilter Field:=7, Visibledropdown:=False
    .AutoFilter Field:=10, Visibledropdown:=False
    .AutoFilter Field:=11, Visibledropdown:=False
    .AutoFilter Field:=12, Visibledropdown:=False

End With

Application.ScreenUpdating = True

End Sub


This is not exactly programming, but there was no formatting nor general
category.

I have many cells with lines and boxes around them.
When I try to elim some vertical lines, via the lines drop down or the
formatting dialog/borders, the lines do not go away and even the borders
dialog does not show them as being there.

I have highlighted an entire column and I have highlighted two cells, which
had a vertical line between them. The border formatting does not show them.
And, the vertical lines don't go down thru the entire sheet, at the end of
contents, a line may proceed down some 3-5 rows, and they print also.

Anyone know how to find these lines so I can unformat them?
--
MichaelM

Excel 2003/XP Pro

Using the Windows Task Manager I seem to have a instance of Excel.exe that
will not go away. My Addin creates a second instance of excel, works some
code and then closes itself, or so I thought. The file closes, the instance
disappears and returns me to the original instance, but I recently noticed
that the windows task manager still shows a process (not using resources 00)
.. Even if i close the original instance the process list just drops from 2
excel.exe on list to one. Although i have tried numerous manual and code
methods for closing I can only clear the Task manager list by rebooting.

if i run ym code it creates a list item in task manager for each run, so
without reboot I can get a long list.

Should I be concerned? Am i missing something in the shutdown?
Have listed the basic code snippits below

Public XLNewSpecial As Object
Public XLUser As Object

Set XLUser = GetObject(, "excel.application")
' so I can call the original instance when needed

Set XLNewSpecial = CreateObject("excel.application")
Application.Visible = True ' along withother settings

'code here to do suff

'explicit closing code

XLNewSpecial .Quit
Set XLNewSpecial = Nothing

The code looks like it works fine with the exception that the Task Manager
will not release my special instance of Excel with out a reboot. to be
specific WTM shhows a process but not the application, after my closing.

Ok, dumb problem. I have a subtotals bar on the left side of my spreadsheet and I can't undo it. How do I get it to go away?

Hello Everyone,

I can't for the life of me remember how I did this in one of my worksheets but I did it somehow. I don't ever want to see the autofilter arrows as I am filtering using VBA.

Justin

I have a system of several workbooks for financial planning. The main ones are Inc-Exp for tracking and analysing income and expenses, and Asset-Liab for tracking assets and liabilities - like the house, 401(k), mortgage loan, etc. There are 3 others that contain expense details, and 6 others that calculate income tax for each year.

Inc-Exp and Asset-Liab have several formulas referring/linked to each other. Both of them also have a number of pivot tables.

Inc-Exp also links to the expense detail workbooks (but they do not link back to Inc-Exp, or anywhere else). Each of the income tax workbooks has links to and from Inc-Exp.

In all the workbooks (and for every sheet), the "Calculate" warning is present in the status bar. However, the workbooks are all set to Automatic calculation. In addition, when I calculate manually (press F9), the word "Calculate" flashes very briefly, and then immediately reappears. In other words, it won't go away.

It seems that some of the values in these workbooks change depending on whether and when I press F9, but it is not clear why.

I think there may be a circular reference problem, but under Error Checking, the Circular Reference item is grayed out, so I think Excel believes there are no circular references.

Any ideas? Many thanks.

I am attaching the VBA code that I use for a spreadsheet that grabs information from other price spreadsheets based on the selection from a drop down. The problem I am getting is that every other time that I make a selection from the drop down, the autofilter arrows show up.

Can someone look at this and tell me how to prevent them from showing up at all?

Thanks

Sub ComboBoxSelectCabinetStyle()

Application.ScreenUpdating = False


lcWorkbookName = ActiveWorkbook.Name
Application.DisplayAlerts = False
ActiveSheet.Unprotect


  'Application.ScreenUpdating = True
  
  
Set myDocument = ActiveSheet
lcmyMasterDocument = ActiveSheet.Name

lcCodeSheet = "Code"
   Sheets(lcCodeSheet).Visible = True                   'Unhide code sheet
    Sheets(lcCodeSheet).Select
  Range("c4").Select                                    'The name of the source file
    lcSheetName = Selection.Value
   Range("a8").Select
    lcFQPath = Selection.Value                          'Variable for where the source files are kept
    ActiveWindow.SelectedSheets.Visible = False         'Hide code sheet
    
      Sheets(lcmyMasterDocument).Select
vbWhyNot = MsgBox("Overwrite Data? with data from " & lcSheetName, vbYesNo)
If vbWhyNot = 6 Then

For Each sh In myDocument.Shapes
    If Left(sh.Name, 4) = "Pict" Then
      sh.Delete
    End If
Next

With ActiveSheet
 AutoFilterMode = False
 Range("A11:H500").AutoFilter

 
 
 
End With

    ChDir lcFQPath
    lcFQPathAndFile = lcFQPath & lcSheetName
       Workbooks.Open Filename:=lcFQPathAndFile, Notify:=False    'Activate the source file
    lcSourceBookName = ActiveWorkbook.Name
    Range("A12:L500").Select
     Application.CutCopyMode = False
     Selection.Copy
          
    Windows(lcWorkbookName).Activate

    Application.ScreenUpdating = False
     
        
    'Selection.EntireRow.Hidden = False
          
 Sheets(lcmyMasterDocument).Select
    Range("a12:L500").Select
     ActiveSheet.Paste
'    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
 '       False, Transpose:=False

    
    ChDir lcFQPath
    lcFQPathAndFile = lcFQPath & lcSheetName
    Workbooks.Open Filename:=lcFQPathAndFile, Notify:=False            'Reactivate the source file
    ActiveWindow.Close
    
 
     
 
    End If
    

Application.ScreenUpdating = True
Application.DisplayAlerts = True
ActiveSheet.Protect
ActiveCell.Offset(-6, 2).Select





End Sub

Private Sub ExpandButton()
ActiveSheet.Unprotect
    Me.AutoFilterMode = False
        ActiveSheet.Protect
End Sub

Private Sub CollapseButton()
ActiveSheet.Unprotect
    Me.Range("rgnA").AutoFilter _
            Field:=1, _
            Criteria1:="<>", _
            Operator:=xlAnd, _
            Criteria2:="<>Qnt.", _
            visibledropdown:=False
            ActiveSheet.Protect
End Sub


I have a graph..based on 65535 cells but it only plots a few of them thanks to a pivot table
is there any way make the pop up that tells me that I need 2 data series if I have more than 32000 data points go away or never show as its not needed in my case and I want it to go away and quit reminding me...

When I input time in the h:mm:ss format it add a am/pm to it. I tried formating it to remove it yet it wont go away. What I am trying to do is add up time in the G Column but all I get is 0:00:00.. I added the [h]:mm:ss format to that cell... Does this have to do with Execl adding am/pm to the time? If it is how do I stop it.

Can anyone advise how to get these formulas working?

They're in an existing spreadsheet, and i'm copying them into another spreadsheet to use, however i'm getting the #name error. I've added in all the possible addins i have, which normally work when i get this error, but it still wont go away!

Any advice greatly appreciated.

Steve

I have written a class that when instanced, represents product information
or a product line. I am working with MS Office 2003 and Windows XP Pro.

Inside the Class_Initialize() .. I have the following code:

***************************************************************

Set ProductList = Workbooks.Open("C:Program FilesMLO Tools" &
FileName)

....
.... other code

ProductList.Close SaveChanges:=False

Set ProductList = Nothing

**************************************************************

So I open a file, strip out the info, then close the file.

Even after the code closes the file, the workbook reference and all its
objects (sheet1, thisworkbook) stay in the VBA project explorer. If I run
this program 5 times, there are 5 references to the workbook;
Each reference displays a dialog "OUT OF MEMORY" if I try to open it. Then
the OUT OF MEMORY issue prevails, evenif I try to type new code.

Does anybody have any clue why this is happening.

Hi, Ok still working with the functions from yesterdays post here

http://www.excelforum.com/excel-prog...rom-a-sub.html

I have the following 2 functions setup.

Function FindColor(Rng As Range)
    For Each cl In Rng 'Range("C4:C49")
         If cl.Interior.colorindex = 6 Then y = y + 1
    Next
 
  FindColor = y
End Function
And
Function FindName(Rng As Range)
  For Each cl In Rng 'Range("C4:C49")
    If cl.Interior.colorindex & cl.Value = "6" Then j = j + 1
  Next
 
  FindName = j
End Function
The First one count the color cells in the range
The Second one looks at the range if the cell is yellow and has no value then it counts it.

If the cell is yellow then it is a full time position if the cell is empty and yellow then we know that we have a full time position open for one of our temp people.

I have these in several cells with different ranges.For example:
=findcolor(C74:C106)
=findname(C74:C106)

Anyway onto the problem: I keep getting a value error in any cell that has this this function call. The error wont go away till I double click the cell.

I tried from yesterdays lesson to add the "Application.Volatile = True"
to the function at the top, however after that I get another error stating
"Expected Function or Variable" on that line.

So what am I missing to get this working?

Thanks for looking, Mike

An Excel VBA program I've spent much time on somehow got corrupted and wont
open up, I have an eternal hourglass when I open the file that wont go away
no matter what I do. Though I regularly back up my work, this corrupted file
represents a whole days worth of coding that I cant get too.
Is there absolutely anyway I can get to my modules code and copy it over to
another workbook maybe thru VBA. Also, I have password protected my modules.
Just grasping at straws here before I start over.

thank you.


No luck finding an answer? You could always try Google.