Command Button #4

I'm trying to create a command button to clear filters in my
spreadsheet.  I don't know how to write the commands, but can recored
a macro and run it fine.  I tried pasting that macro into the
button....and I had it working then messed it up.  Now when I click on
the button, nothing happens.  The code currently associated to the
button is below.  I really don't want Criteria1 to = 'Reject', but the
one time the button worked if no filter was set and the button
clicked, an error was returned.  The only way I could make it work was
to force a filter as part of the macro.

How do I make the clear filter work by clicking the button, and can it
be made so that if there isn't a filter set when the button is
clicked, it does nothing rather than return error?

Sub Click()
'
' Macro1 Macro
' Macro recorded 1/7/2009 by Ben DiCarlo
'
    Rows("8:8").Select
    Selection.AutoFilter Field:=2, Criteria1:="Reject"
    Rows("8:8").Select
    ActiveSheet.ShowAllData
    Range("A7").Select

End Sub

Private Sub CommandButton1_Click()

End Sub


0
dickharlo (9)
1/8/2009 3:49:42 PM
excel 39879 articles. 2 followers. Follow

2 Replies
964 Views

Similar Articles

[PageSpeed] 38

Try:

Private Sub CommandButton1_Click()
    If me.FilterMode Then
        me.ShowAllData
    End If
End Sub

==========
In xl2003 menus, I can add a ShowAllButton to my favorite toolbar.

Tools|Customize|Commands tab|Data category
Drag the "Show All" icon to your favorite toolbar.

I like it since I can click on it in any worksheet in any workbook.


dickharlo@gmail.com wrote:
> 
> I'm trying to create a command button to clear filters in my
> spreadsheet.  I don't know how to write the commands, but can recored
> a macro and run it fine.  I tried pasting that macro into the
> button....and I had it working then messed it up.  Now when I click on
> the button, nothing happens.  The code currently associated to the
> button is below.  I really don't want Criteria1 to = 'Reject', but the
> one time the button worked if no filter was set and the button
> clicked, an error was returned.  The only way I could make it work was
> to force a filter as part of the macro.
> 
> How do I make the clear filter work by clicking the button, and can it
> be made so that if there isn't a filter set when the button is
> clicked, it does nothing rather than return error?
> 
> Sub Click()
> '
> ' Macro1 Macro
> ' Macro recorded 1/7/2009 by Ben DiCarlo
> '
>     Rows("8:8").Select
>     Selection.AutoFilter Field:=2, Criteria1:="Reject"
>     Rows("8:8").Select
>     ActiveSheet.ShowAllData
>     Range("A7").Select
> 
> End Sub
> 
> Private Sub CommandButton1_Click()
> 
> End Sub

-- 

Dave Peterson
0
petersod (12004)
1/8/2009 5:34:34 PM
On Jan 8, 9:34=A0am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> Try:
>
> Private Sub CommandButton1_Click()
> =A0 =A0 If me.FilterMode Then
> =A0 =A0 =A0 =A0 me.ShowAllData
> =A0 =A0 End If
> End Sub
>
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> In xl2003 menus, I can add a ShowAllButton to my favorite toolbar.
>
> Tools|Customize|Commands tab|Data category
> Drag the "Show All" icon to your favorite toolbar.
>
> I like it since I can click on it in any worksheet in any workbook.
>
>
>
>
>
> dickha...@gmail.com wrote:
>
> > I'm trying to create a command button to clear filters in my
> > spreadsheet. =A0I don't know how to write the commands, but can recored
> > a macro and run it fine. =A0I tried pasting that macro into the
> > button....and I had it working then messed it up. =A0Now when I click o=
n
> > the button, nothing happens. =A0The code currently associated to the
> > button is below. =A0I really don't want Criteria1 to =3D 'Reject', but =
the
> > one time the button worked if no filter was set and the button
> > clicked, an error was returned. =A0The only way I could make it work wa=
s
> > to force a filter as part of the macro.
>
> > How do I make the clear filter work by clicking the button, and can it
> > be made so that if there isn't a filter set when the button is
> > clicked, it does nothing rather than return error?
>
> > Sub Click()
> > '
> > ' Macro1 Macro
> > ' Macro recorded 1/7/2009 by Ben DiCarlo
> > '
> > =A0 =A0 Rows("8:8").Select
> > =A0 =A0 Selection.AutoFilter Field:=3D2, Criteria1:=3D"Reject"
> > =A0 =A0 Rows("8:8").Select
> > =A0 =A0 ActiveSheet.ShowAllData
> > =A0 =A0 Range("A7").Select
>
> > End Sub
>
> > Private Sub CommandButton1_Click()
>
> > End Sub
>
> --
>
> Dave Peterson- Hide quoted text -
>
> - Show quoted text -

Worked perfect Dave....Thank you!
0
dickharlo (9)
1/8/2009 6:38:30 PM
Reply:

Similar Artilces:

How do I put input boxes and sumbit buttons in?
How do I put input boxes and sumbit buttons in excel vba? I am trying to make a address book manually using Excel VBA. Is there a way I can record multiple information into Excel after submitting info. All i have so far is being able to submit one bit of information into excel but I dont think it will stay if I save it. Like is there a way I can group multiple address information using excel VBA? If so how? What you want to do is not clear. Please post back and give us a step-by-step procedure of what you want to do and what you want Excel to do. HTH Otto "mikstr14" <m...

print preview command in Excel
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) I just upgraded to office 2008 for Mac and can not find the &quot;print preview&quot; command in Excel I had in my previous version and that is available in Word 2008. Was it removed? > On 11/4/09 8:43 AM, in article 59b7feef.-1@webcrossing.caR9absDaxw, <br> > &quot;sprint1005@officeformac.com&quot; wrote: <br> > <br> > > Version: 2008 <br> > > Operating System: Mac OS X 10.6 (Snow Leopard) <br> > > <br> > > I just upgraded to office ...

macros will not assign to buttons
Help! I cannot assign macros to my custom buttons. The dialog box appears and I can select a macro but it does not save to the button. I believe this is related to another problem I had - which was my personal.xls file did not load on start even though it was in the xl start folder. I fixed that - it was disabled by excel which I fixed by going to about excel - disabled files and selecting enable. Now personal.xls loads - but the macros will not assign. What type of button? From Tools>Customize>Commands>Macros? These customizations are stored in a file named Excel(x).xlb wher...

Bar graph #4
How does one insert a "totals" column in a bar graph but not have them graphed? Use a chart labeller add- in. Pick one of these Rob Bovey's Chart Labeller, http://appspro.com (notwithstanding its name, it works with all chart types) John Walkenbach's Chart Tools, http://j-walk.com Tushar's Hover Chart Label utility: http://tushar-mehta.com/excel/software/chart_hover_label/index.html best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "JF" <JF@discussions.microsoft.com> wrote in message news:4C...

ESEutil commands
I need a step by step guide on using eseutil. I need to learn how to use this tool to commit the logs to the database On Tue, 23 Nov 2004 18:09:02 -0800, "Ed" <Ed@discussions.microsoft.com> wrote: >I need a step by step guide on using eseutil. I need to learn how to use this >tool to commit the logs to the database eseutil/? A full online backup of the store will commit the logs. What exactly are you trying to accomplish? I have another site that will not have a backup solution in place until next month so i would to know how to flush the logs using this utility ...

thread question #4
How can I determine in a block of code if the thread it's running in is the same as the main GUI thread? I was trying to use GetCurrentThread to get the thread the function is being called from. Not sure how compare that to GUI thread. On Wed, 21 Dec 2005 13:58:35 -0600, "Eric Margheim" <NOSPAM***eric@prism-grp.com***NOSPAM> wrote: >How can I determine in a block of code if the thread it's running in is the >same as the main GUI thread? > >I was trying to use GetCurrentThread to get the thread the function is being >called from. Not sure how...

Creating custom buttons #2
Hello, I would like to create screens of buttons so that the operators of the register can simply press a button to add an item for a sale. The system will be used at the end of a cafeteria type food line and I need to speed up the check out process. What I am thinking is having 3 buttons on one screen, Hot Food, Drinks, and Candy/Snacks. Then when the cashier presses Hot Food, another screen comes up that displays 1 button for each of the 16 current Hot Food items. When 1 of those items is selected, the Hot Food button screen should remain open until the user presses a Back button. ...

Command Button #4
I'm trying to create a command button to clear filters in my spreadsheet. I don't know how to write the commands, but can recored a macro and run it fine. I tried pasting that macro into the button....and I had it working then messed it up. Now when I click on the button, nothing happens. The code currently associated to the button is below. I really don't want Criteria1 to = 'Reject', but the one time the button worked if no filter was set and the button clicked, an error was returned. The only way I could make it work was to force a filter as part of the macro. How...

command
how do you use command. A lot of computer people use it but how do we use it? Aaron You've reached a newsgroup dedicated to supporting the use of Microsoft Access, a relational database product. I don't see anything in your post that suggests you are asking about MS Access. Please post to a newsgroup that supports the area covered by your post. Regards Jeff Boyce Microsoft Office/Access MVP "aaron" <aaron_t_d@hotmail.com> wrote in message news:78CB1423-55B3-40A1-8BF5-D4615E816057@microsoft.com... > how do you use command. A lot of computer people use i...

Set Increment on Spin Button to 0.1
I'm using a spin button from the control toolbar to increase the value in a cell. the min value i want is 0 the maximum is 2 but i want it to increase by 0.1 in the properties this doesn't seem to be allowed help!!!! Thanx Mark Mark, Here is one way. Link your spinbutton to an unused cell (I use) A15. Set the max to 20. Then in code, set the real cell's value like so Private Sub SpinButton1_SpinUp() Range("A16").Value = Range("A15").Value / 10 End Sub Private Sub SpinButton1_SpinDown() Range("A16").Value = Range("A15").Valu...

command boxes from menu
I was working on word 2003, I had copied a portion of an online ebook to paste onto a new document, when I did right click to paste it on the doc. the command box appears outlined, no word commands, it's like a ghost. I then went to the menu at the top to Edit and find paste to do it from there and the same thing appeared; an outline of the command box, no word commands, it's like a ghost. I tried to pretend the words were there just not visible and moved my cursor to the position in which I thought paste was and clicked but nothing happened. Has anybody ever had this ...

command
still learning of the sql command HOW TO DISABLE XP_CMDSHELL AND DISABLE BOTH MAIL PROCEDURES ...

remove button
How can I remove the Save and Close, or Save and New button on a phone call activity? Hi Troy, You could find out the location of the buttons via Javascript and then hide these buttons. Though this is relatively hard to do and unsupported, it can be done. Another approach, which in my opinion is a lot better, would be to remove the rights for users to create, delete and eventually update the entity phone call. You can do this in the security roles in the settings section. Hope this helps, -- Ronald Lemmen - MSCRM MVP Avanade Netherlands http://ronaldlemmen.blogspot.com/ "Troyz...

Adding a command to a picture
I have inserted a picture onto my form and in the Event / double click set up a macro to close the form however when i double click on my picture nothing is happening - help!! On Thu, 3 Jun 2010 06:02:22 -0700, casi <casi@discussions.microsoft.com> wrote: One suggestion would be to temporarily add a button to your form, and from the button wizard select the option to close the form. Then look behind the scenes what was generated, and implement your image_doubleclick the same way. -Tom. Microsoft Access MVP >I have inserted a picture onto my form and in the Event ...

Command Handler
Hi Guys, I have a context menu and menu handler. I know how to acheive what is needed,but I am wanting to know if there is a better way to do this. When the user right clicks on a view, I get the OnRButtonDown(...), there I show the context menu. I have a command handler for the menuitems in the context menu also, clicking which will take me to the handler. What I want is to know is the point where the context menu popped up inside the command handler. Is there a way? Or Is caching the CPoint in OnRButtonDown and using it in the command handler the only way? Thanks Vipin &...

Userform Command Button
In a Userform, I have multiple command buttons. For example: D1_Click D2_Click Depending on another event that occurs, I want to calculate in code the button name that I will be triggering the click event and then trigger the event. For example Application.Run "D" & i & "_Click" with i being the value of 1 or 2. Application.Run doesn't appear to work in the Userform. Thanks Steve Maybe it's because most subroutines in a userform module are "Private"??? Have you tried changing the "Private" prefix to "...

Missing emails #4
Hi, I am trying to download my emails from several servers. It looks like coming. Message ,saying "Send/Receive 5/135" and so on, appears left bottom corner. But there is no any message in my inbox. When I try again, there is no new email for download. They're aslo deleted from server. Where is my emails? What can I do? Is there a problem with outlook 2003? Thanks. ...

Make lines on a graph appear with a button
All, I need to create a graph with 40 or so lines graphed but only one up o the graph at a time with the use of buttons. I would really appreciat a hand, I am very new to this level of Excel. Thanks -- Message posted from http://www.ExcelForum.com Select all the data, and create a line chart Select a cell in the table Choose Data>Filter>AutoFilter From the dropdown list in one of the heading cells, select an item The chart will display the data for that item geistritter < wrote: > I need to create a graph with 40 or so lines graphed but only one up on > the graph at a tim...

Sales for outlook folders and buttons.
ok, so I've done this many, many times.... .. Go to Tools | Options. 2. Click on the Other tab and click on the Advanced Options button. 3. Click on the COM Add-Ins button. 4. Highlight the Microsoft CRM Add-In (Note the location of the crmaddin.dll) and then click the remove button. 5. Click OK until back at the Outlook window. 6. Exit Outlook. 7. Launch Outlook and go back to the COM Add-Ins window (steps 1 - 3). 8. Click the Add button and navigate to the location of the crmaddin.dll file (from Step 4). 9. Click OK to get back to the Outlook Window. 10. Exit Outlook and...

Print withoutshowing a comand button
I am trying to print a spreadsheet but I do not want the comand button show on the paper copy. Is there a way to hide it in the printing process. les, view, toolbars, control toolbox, click on the design mode button then right click on the button and properties, there is an option there for print object, change to false, click on design mode again and close the tool bar -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "les&quo...

command buttons
Greetings, I have two command buttons A and B which are connected to subforms on the main form.I have a table"Client" and field "A/B". My goal is when I click amdA I want the field "A/B" show A, whe I click cmdB I field "A/B" will show B. If any solution? For me it's puzzle.Thx. -- Patric Patric, Assuming that the control you want to populate on the main form is named 'A/B', put the following code in the On Click event for button A: Forms![Name of main form]![Name of sub form control].Form![A/B] = A In the On Click event for button B:...

Dynamic Button Name?
Hi everone, Can a button name be made dynamic? For example, you create a button and want its name be read from cell A1. So if A1 read "Mike", the button name automatically become "Mike" and so on. Thanks, Mike for a button from the Forms toolbar, you can use this event macro. Put it in the worksheet code module (right-click the worksheet tab and choose View Code") Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Not Intersect(Target, Range("A1")) Is Nothing Then _ Me.Buttons(1).Caption = Range("A1").Value ...

run command to istall printers?
I have the following commands that I can type at the dos prompt that will install a printer into all users profiles on the computer. rundll32 printui.dll,PrintUIEntry /ga /n=94\\prnsrv\Second Floor Printer=94 net stop spooler net start spooler How can I put this into a vbs script so that I use it to run on peoples computers rather than typing it in. Thanks. Steve wrote: I have the following commands that I can type at the dos prompt that will install a printer into all users profiles on the computer. rundll32 printui.dll,PrintUIEntry /ga /n�\\prnsrv\Second Floor ...

VBA Question #4
Good morning everybody, I noticed that periodically, code (specifically macro code in Excel) that was working flawlessly in the past suddenly starts to error out. For example, in making a line-graph, macro code will add data series to a chart and assign values but suddenly it draws an error when it hits code to name the data series. But if I move that line of code above the line in front of it and rerun the procedure that line works fine but then it errors out on the data series a couple lines down so I don't think the problem is the code. This is some example code. Charts.Add...

make autofilter buttons more discernable?
My eyesight is getting worse and I'm having trouble discerning the blu or black arrow when using autofilter. Besides increasing the vie percentage (not desirable), is there anything I can do -- wolfpack9 ----------------------------------------------------------------------- wolfpack95's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=9 View this thread: http://www.excelforum.com/showthread.php?threadid=38869 Debra Dalgleish posted a response from Tom Ogilvy to get the filter criteria: http://groups.google.co.uk/group/microsoft.public.excel.worksheet.funct...