Show all data

[WH99]

I have a search facility which filters the data, That works ok, but when I
press commandbutton1 (code below) it should show all data and change to
sheet7. I get an error code 1004 and the following is highlighted
"Sheet1.ShowAllData".
Any ideas?

Option Explicit

Private Sub CommandButton1_Click()
Sheet1.Unprotect
Sheet1.ShowAllData
Cells.Range("A1").Select
Sheet1.Protect
Sheet7.Select
Worksheets("menu").CommandButton3.Visible = True
End Sub

 

[Per Jessen]

Hi

You have to refer to a range, ie Selection.ShowAllData.
If you want to turn off the filter use Selection.AutoFilter.

Btw: You don't need to write Cells.Range("A1")... , just use Range("A1")...

Regards,
Per

 

[WH99]

Thanks for coming back so quickly.
Would the code look something like what I have below?

Option Explicit

Private Sub CommandButton1_Click()
Sheet1.Unprotect
Selection.ShowAllData
Selection.AutoFilter
Range("A1").Select
Sheet1.Protect
Sheet7.Select
Worksheets("menu").CommandButton3.Visible = True
End Sub

 

[Jim Cone]

Try...
If Sheet1.FilterMode Then Sheet1.ShowAllData
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"WH99"
<wrote in message
I have a search facility which filters the data, That works ok, but when I
press commandbutton1 (code below) it should show all data and change to
sheet7. I get an error code 1004 and the following is highlighted
"Sheet1.ShowAllData".
Any ideas?

Option Explicit

Private Sub CommandButton1_Click()
Sheet1.Unprotect
Sheet1.ShowAllData
Cells.Range("A1").Select
Sheet1.Protect
Sheet7.Select
Worksheets("menu").CommandButton3.Visible = True
End Sub

 

[Dave Peterson]

So commandbutton1 is on Sheet1?

If yes:

Private Sub CommandButton1_Click()
with me
.Unprotect
If .FilterMode Then
.ShowAllData
End If
.range("a1").select
.protect
end with
Sheet7.select
worksheets("Menu").commandbutton3.visible = true
end sub

Is there any reason you used the codename for Sheet7, but worksheets("menu") for
the visible statement?

If Sheet1 is not the sheet with the button:


Private Sub CommandButton1_Click()
with Sheet1
.Unprotect
If .FilterMode Then
.ShowAllData
End If
.select 'need to select the sheet before selecting a range
.range("a1").select
.protect
end with
Sheet7.select
worksheets("Menu").commandbutton3.visible = true
end sub

 

[Per Jessen]

Thanks for your reply.

It looks fine, but you have to test it, to see if it's ok.

The Selection.ShowAllData will only work if the range where the filter is
applied is selected, otherwise refer to the eact range.

Regards,
Per

 

[WH99]

Per, Jim and Dave,
Thank you all for your surgestions.
I tried Dave`s code and it work well.....thanks Dave.
I`ll stay with that one.