Prevent ShowAllData Method from failing

Hi,
I have an Excel 2007 worksheet that has autofilters and macros assigned to 
option buttons in a Group Box that carry out some of the filtering.  One 
button is the ShowAllData button that removes the filter. If the ShowAllData 
button is selected and then selected again (before any other button in the 
group is selected) I get a Run Time Error that the ShowAllData method of 
worksheet class failed.  How do I prevent this error? I'm very new to VB.  
The code below is what I'm having trouble with.
Thanks,
Jim

Sub UnhideBlanks()
'
' UnhideBlanks Macro
'
    ActiveSheet.Unprotect Password:="sivle"
    If ActiveSheet.AutoFilterMode = True Then
    ActiveSheet.ShowAllData
    ElseIf ActiveSheet.AutoFilterMode = False Then
    Else
    ActiveSheet.Protect Password:="sivle", DrawingObjects:=True, 
Contents:=True, Scenarios:=True _
        , AllowSorting:=True, AllowFiltering:=True
    End If

End Sub

0
Utf
1/8/2010 5:58:02 PM
excel.programming 6508 articles. 2 followers. Follow

4 Replies
2328 Views

Similar Articles

[PageSpeed] 18

    With ActiveSheet
        .unprotect ...
        If .FilterMode Then
            .ShowAllData
        End If
        .protect...
    end with



JimAA wrote:
> 
> Hi,
> I have an Excel 2007 worksheet that has autofilters and macros assigned to
> option buttons in a Group Box that carry out some of the filtering.  One
> button is the ShowAllData button that removes the filter. If the ShowAllData
> button is selected and then selected again (before any other button in the
> group is selected) I get a Run Time Error that the ShowAllData method of
> worksheet class failed.  How do I prevent this error? I'm very new to VB.
> The code below is what I'm having trouble with.
> Thanks,
> Jim
> 
> Sub UnhideBlanks()
> '
> ' UnhideBlanks Macro
> '
>     ActiveSheet.Unprotect Password:="sivle"
>     If ActiveSheet.AutoFilterMode = True Then
>     ActiveSheet.ShowAllData
>     ElseIf ActiveSheet.AutoFilterMode = False Then
>     Else
>     ActiveSheet.Protect Password:="sivle", DrawingObjects:=True,
> Contents:=True, Scenarios:=True _
>         , AllowSorting:=True, AllowFiltering:=True
>     End If
> 
> End Sub

-- 

Dave Peterson
0
Dave
1/8/2010 7:23:27 PM
Hi Jim,

AutoFilterMode tests if AutoFilter is applied to the worksheet (Like 
AutoFilter drop downs are present)

FilterMode test if a filter has actually been set

Sub UnhideBlanks()
'
' UnhideBlanks Macro
'
    ActiveSheet.Unprotect Password:="sivle"
    If ActiveSheet.AutoFilterMode = True Then
      If ActiveSheet.FilterMode = True Then
        ActiveSheet.ShowAllData
      End If
      ElseIf ActiveSheet.AutoFilterMode = False Then
      Else
      'ActiveSheet.Protect Password:="sivle", DrawingObjects:=True, _
  Contents:=True, Scenarios:=True _
          , AllowSorting:=True, AllowFiltering:=True
    End If

End Sub

-- 
Regards,

OssieMac


"JimAA" wrote:

> Hi,
> I have an Excel 2007 worksheet that has autofilters and macros assigned to 
> option buttons in a Group Box that carry out some of the filtering.  One 
> button is the ShowAllData button that removes the filter. If the ShowAllData 
> button is selected and then selected again (before any other button in the 
> group is selected) I get a Run Time Error that the ShowAllData method of 
> worksheet class failed.  How do I prevent this error? I'm very new to VB.  
> The code below is what I'm having trouble with.
> Thanks,
> Jim
> 
> Sub UnhideBlanks()
> '
> ' UnhideBlanks Macro
> '
>     ActiveSheet.Unprotect Password:="sivle"
>     If ActiveSheet.AutoFilterMode = True Then
>     ActiveSheet.ShowAllData
>     ElseIf ActiveSheet.AutoFilterMode = False Then
>     Else
>     ActiveSheet.Protect Password:="sivle", DrawingObjects:=True, 
> Contents:=True, Scenarios:=True _
>         , AllowSorting:=True, AllowFiltering:=True
>     End If
> 
> End Sub
> 
0
Utf
1/8/2010 7:36:01 PM
Hi again Jim,

The following method of testing both AutoFilterMode and FilterMode together 
in the one If statment also works.

Note there is no need to use = True because it is understood. (If testing 
for False then you use = False)

Also I think your use of ElseIf and Else is not correct. I think the Else 
part will never be processed under any conditions.

Note the space and underscore at the end of a line is a line break in an 
otherwise single line of code. (I used them extensively when posting here 
because it prevents unwanted line breaks in the post and the code can be 
copied directly into your project.

Sub UnhideBlanks()
'
' UnhideBlanks Macro
'
    ActiveSheet.Unprotect Password:="sivle"
    If ActiveSheet.AutoFilterMode _
      And ActiveSheet.FilterMode Then
      
      ActiveSheet.ShowAllData

    ElseIf ActiveSheet.AutoFilterMode = False Then
      ActiveSheet.Protect Password:="sivle", _
        DrawingObjects:=True, _
        Contents:=True, _
        Scenarios:=True, _
        AllowSorting:=True, _
        AllowFiltering:=True
    End If

End Sub

-- 
Regards,

OssieMac


0
Utf
1/8/2010 8:04:01 PM
Thanks Dave and OssieMac, I tried both solutions and they did exactly what I 
wanted.

"OssieMac" wrote:

> Hi again Jim,
> 
> The following method of testing both AutoFilterMode and FilterMode together 
> in the one If statment also works.
> 
> Note there is no need to use = True because it is understood. (If testing 
> for False then you use = False)
> 
> Also I think your use of ElseIf and Else is not correct. I think the Else 
> part will never be processed under any conditions.
> 
> Note the space and underscore at the end of a line is a line break in an 
> otherwise single line of code. (I used them extensively when posting here 
> because it prevents unwanted line breaks in the post and the code can be 
> copied directly into your project.
> 
> Sub UnhideBlanks()
> '
> ' UnhideBlanks Macro
> '
>     ActiveSheet.Unprotect Password:="sivle"
>     If ActiveSheet.AutoFilterMode _
>       And ActiveSheet.FilterMode Then
>       
>       ActiveSheet.ShowAllData
> 
>     ElseIf ActiveSheet.AutoFilterMode = False Then
>       ActiveSheet.Protect Password:="sivle", _
>         DrawingObjects:=True, _
>         Contents:=True, _
>         Scenarios:=True, _
>         AllowSorting:=True, _
>         AllowFiltering:=True
>     End If
> 
> End Sub
> 
> -- 
> Regards,
> 
> OssieMac
> 
> 
0
Utf
1/8/2010 8:36:02 PM
Reply:

Similar Artilces:

preventing date from changing
I have an If condition that sets the date to "today()" true or "today() + 1" if false. My question is how do I prevent the date from changing? If the date is set for today, then tomorrow I don't want to "update". How can this be done? thanks You could copy the cell, and paste as values, before you close the file. (Edit>Paste Special, Values) Or, you could enter today's date in a cell in the row (Ctrl+; ), then refer to that cell in the formula. For example, instead of: =IF(A2="Local",TODAY(),TODAY()+1) enter: =IF(A2="Local...

outlook feature to prevent forgetting attachments
Often people forget to send attachments with emails. Very often they will write something along the lines of "I have attached the minutes from our meeting" and then forget to atcually attach a file. Is there a way therefore that outlook can check the text of an email for words like attach, attached, attachment and query the user before sending if there does not appear to be an attachment? ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree"...

Prevent the sales from Tendering when another Item is Scan at POS
Hello, We have serveral items that our Retail does have price assign to them. The cashier will scan the next item. They will not notice the last item they did not enter a price. Now the 2nd item is the price for the 1st item. Then the cashier will tender out the sales without noticing they have an incorrect total for this transaction. Then the store manager have to performed a post void for this transaction, which hold up the customer awaiting to check out. Have anyone else encounter this and know a way to limited sale from being total out of an acceptable sales amount...

Process EMSMTA.EXE (PID=2968). Topology Discovery failed, error 0x80040952.
Everything is working but what is this, it keeps happining in my event logs, any ideas to fix it? Process EMSMTA.EXE (PID=2968). Topology Discovery failed, error 0x80040952. Try the following: run setspn utility to list the registered Services Principal Names on Exchange Virtual Server (MAIL): setspn -l MAIL Registered ServicePrincipalNames for CN=MAIL,CN=Computers,DC=xxxxxx,DC=local: exchangeRFR/MAIL exchangeRFR/MAIL.xxxxxx.local exchangeMDB/MAIL exchangeMDB/MAIL.xxxxxx.local MSClusterVirtualServer/MAIL.xxxxxx.local MSClusterVirtualServer/MAIL HOST/...

prevent user from deleting a tab prevent running macro from menu
how can one prevent a user from 1. deleting a specific tab in a workbook? 2. prevent a user from running a macro from the tools menu (where user can only run a macro from a command button)? thanks! #1. Protect the workbook's structure. In xl2003 menus: tools|protection|protect workbook|check structure #2. Make the sub private: Sub Testme() becomes Private Sub testme() This will stop the user from seeing that subroutine--but if they know the name, they can still type it and run it. joemeshuggah wrote: > > how can one prevent a user from...

Exchange router install: 405 method not allowed
Hi, I'm trying to install the crm email router for an environment that is running for 3 months and has some customization. When trying to install the router, the wizard checks the user input. I get the warning "cannot connect to microsoft crm server. the request failed with http 405: method not allowed." I havent found anything about this error in connection to the email router. Can someone give me a hint of just the solution. I have been trying stuff for 2 days now. Thanx a lot! ...

KB970653 update was failed
Hi i have a question my updated KB970653 was failed how do i reinstal it??? December 2009 cumulative time zone update for Microsoft Windows operating systems: http://support.microsoft.com/kb/976098 If KB976098 installed, don't worry about KB970652: You don't need it now. joakim wrote: > Hi i have a question my updated KB970653 was failed how do i reinstal > it??? joakim wrote: > I have a question my updated KB970653 was failed how do I > reinstall it??? Is it still failing? -- Shenan Stanley MS-MVP -- How To Ask Questions The Smart Way ...

Outbound mail failes
Hello all, We are currently working on a migration from a Exchange 5.5 server to a Exchange 2003 cluster. The migration of the data, such as mailboxes and public and system folders is complete. Because outbound mail was still being deliverd by the Exchange 5.5 server, we created a SMTP connector on the Exchange 2003 server, and deleted the IMC on the Exchange 5.5 server. We assumed by doing this that the Exchange 2003 cluster would handle all email going outside the organisation. We notice that when somebody at our company tries to send a message outside our organisation, an error (55...

Re: preventing distribution lists from printing
I work for a large corporation and I often get outlook mail with ver long distribution lists. Is there any way to prohibit these lenght list from printing so that I only get the message? Thank - buddy ----------------------------------------------------------------------- Posted via http://www.mcse.m ----------------------------------------------------------------------- View this thread: http://www.mcse.ms/message548641.htm ...

valuation method as moving average bUT detailed per warehouse and.
today GP has moving average perpetual but it averges by item iD regardless of location. Customers would need detailed average cost per each separate warehouse/locations even though same ID. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Busi...

Prevent calculation of fields in split view
Hi all! I have following problem: Most of my forms are displayed in split view, within some of them i am doing different calculations, displaying the calculated values in text boxes. Since some of those calculations are quite time-consuming, i dont want Access to calc. the values for _each_ record in the split-view (which it is doing right now). I want it to just calc the values for the record currently viewed, is there any way to do this? best regards, --Michael ...

Macro needed to Paste Values and prevent Macro operation #2
Sub Macro1() ' ' Dim myBk As Workbook Set myBk = Workbooks.Open(Application.GetOpenFilename(, , "Select the File")) Range("To_Database").Copy ThisWorkbook.Worksheets(1).Range("A65536").End(xlUp)(1).Offset(2, 1) myBk.Close False End Sub In the above macro I need to prevent macros operating in the External File when it opens. I also need the Named Range ("To_Database") from the External File to be copied into the file from which this macro operates as PasteValues. At the moment the Formula in the Named Range is coming across, so that I'm ...

Administration of Exchange 2000 after 2003 install on the domain is failing!
Setup: Windows 2003 running Exchange 2003 winse-exch1.com PDC (Mixed Mode) Windows 2000 running Exchange 2000 winse-exch1.com Replication DC. The Problem: Ms Exchange Management no longer views the Exchange 2000 server, but all mail seems to still be going to it. I am trying to migrate a mailbox off of the Exchange 2000 setup onto my 2003 setup, and I can no longer acccess the Exchange 2000 box through Exchange system manager! The strange thing is that the Exchange 2000 setup still seems to working for email, so "user1" email is still coming in through outlook, but I can't s...

Prevent Forwarding #2
Hi. I need to know how to make an email message unable to be forwarded to a third party. For example if I sent an email to John, John couldn't then forward that message to someone else. Thanks!! Shannon <anonymous@discussions.microsoft.com> wrote: > Hi. I need to know how to make an email message unable to > be forwarded to a third party. For example if I sent an > email to John, John couldn't then forward that message to > someone else. Can't be done, in general. -- Brian Tillman You'd need to use a service such as those listed at http://www.slipst...

.NET Security updates fail
Opening a new thread as suggested by PA Bear. OS: Windows XP Pro SP3 running IE8 but Chrome for anything useful. Part on an office network with an SBS2003 domain server. The above updates won't install and fail with error 0x80070643. I have read KB976982 and followed its procedure to remove all .NET versions and replace them in the order 3.5SP1, 1.1, 1.1SP1 followed by Windows update. This has not resolved the problem and the updates sit in my systray, waiting.. I am also running AVG Free and take the view that any AV is better than none. Is there any suggestion that A...

fail to do Backup
hi, I have my exchange server not completing the NTbackup. I am suspecting two resons causing that problem: 1- hardware failure with one hardisk out of 4 hardisks (RAID5). 2- an incedent occured to one user that he cannot recieve e-mails due to corruption in the mailbox.. in that case I have exported all the existing e-mails, deleted the mailbox and then created it again. I am recieving errors 747 and 217, and the backup is not working what to do in this case. please help -- Zuhair Attya IT Administrator Bahrain On Wed, 28 Dec 2005 01:55:02 -0800, Zuhair Attya <ZuhairAttya@discussion...

Delete Dups Query Failing
I have a form set up for data entry with a subform linking child records in a different table. In the case where a user closes the form halfway through entering child records then reopens it to finish adding the child records, I don't want to keep the duplicate master record they create when they reenter the information in the controls bound to the master table. So I made a simple find duplicates query, added a "MaxofID" field, and joined it to a delete query that should delete the second instance of the duplicate record. I was going to run this query whenever the form...

Prevent users from sending email?
Hi all - Objective: I would like to limit users from being able to send email to everyone in our organization. Problem: We do have an Everyone distribution list that I have configured to only accept messages from certain users. This is ok, but once the user is presented with the access denied message they just select all the users in their contact list or they select enough of the groups to have the entire organization covered and send the email that way. Putting company policies aside, is there a technology solution for this? Any ideas?? -Donnie Set the maximum number of reci...

Outlook fails to remember password
I currently run Windows XP, with Microsoft Office 2000. Up until a couple of months ago, Outlook would remember my password when checking for new messages. Now if I clck remember password, it doesn't. Through Control Panel, e-mail properties, I have remember password checked, but it won't etain my password. I have also run Outlook's Detect and Repair...still no help. What do you suggest next? Thanks in advance I found the solution on the Microsoft support website. You have to search the Microsoft Knowledge Base. It is article number 235864. If you do not know where...

Preventing user name from appearing in new cell Comments.
I use the comment feature in Excel 2000 regularly. Every time a new comment is started, my user name in bold appears. I always have to overwrite it an turn off bold text before I create the comment. As I am the only user on this computer, and I do not share the information on-line with any other users, can some Excel expert please tell me how can I turn off the automatic insertion of my user name? Thank you. Hi Robert, ] XL2000: Sample Macro to Remove User Name from Comment http://support.microsoft.com/?kbid=213766 -- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov...

Preventing users from saving forms into unprotected formats
I need to stop users from saving forms into unprotected formats, e.g. RTF (believe it or not, my users like to mess about with form layout, creating business interoperability problems) I have successfully intercepted the FileSave and FileSaveAs commands. I also need to intercept the Save As dialog which comes up for a new dialog when the file is closed, so I'm using a BeforeDocumentClose event handler. I would like them to be able to choose not to save, so I give them a YesNoCancel message, but I can't prevent the regular message coming up if they click No on my message...

Preventive Maintenance
Hi! Have a problem with 2 buttons in Edit List of Preventive Maintenance window (PM Edit List), the buttons 'Cancel' and 'Delete' aren't functioning properly, because display a message 'This document can not be deleted', when I choose from the list. -- Thank''''s for yours commentaries ...

Conditional locking to prevent editing
We have a form that is used to construct our sales invoices. Up until the point where the data is transferred to our accounting system, users need to be able to edit the forms and amend the invoice contents. Once an invoice has been transferred, the status field on the form is changed from Draft to (surprise surprise) Transferred. Once this has occurred I would like to prevent anyone editing the invoice any further. So it's a conditional situation: if the value in the status control is Draft, users should be able to edit the form in full. If the value is Transferred, they should be a...

Any WXP registry to prevent changing OL2003 tracking option?
Hi there, For those stand alone computers (i.e. won't/can't logon to AD), I would like to enforce outlook2003 email tracking. After I enforced the settings in outlook2003, users can accidentally/purposely revert the settings so that I can't track the email status. If there is any registry settings in winxp which can prevent users to change that email tracking settings? Many thanks! ...

Preventing a menu from closing
Hi, I need (for the purpose of a CBT) to perform the following task. (a) Prevent a menu from closing if a user clicks outside the menu. A menu typically closes if a user clicks outside it. I need to prevent this from happening (preferably without redrawing the menu as it may cause noticeable flicker.) Can anyone give me any suggestion as to how this can be done? I was thinking of using Hooks, but I'm not sure whether this is the right direction to take. Any help would be appreciated... Thanks in advance for the help... Anand Balaji. ...