Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Show all data
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 -- WH99 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Show all data
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" skrev i meddelelsen ... 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 -- WH99 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Show all data
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 -- WH99 "Per Jessen" wrote: 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" skrev i meddelelsen ... 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 -- WH99 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Show all data
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 -- WH99 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Show all data
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 WH99 wrote: 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 -- WH99 -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Show all data
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" skrev i meddelelsen ... 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 -- WH99 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Show all data
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. -- WH99 "Dave Peterson" wrote: 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 WH99 wrote: 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 -- WH99 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
show 3 sets of data per data point in a scatter plot | Charts and Charting in Excel | |||
how do i sort a worksheet data to show repetitve data and show | Excel Worksheet Functions | |||
Don't Show Zero Data | Charts and Charting in Excel | |||
Can you show data in a data table but not plot it on the chart? | Charts and Charting in Excel | |||
HOW DO I HIDE DATA AND SHOW A PLUS SIGN THAT I HAVE HIDDEN DATA H. | Excel Discussion (Misc queries) |