Command Button in Pivot Tabls

[winnie123]

Hi,

I have cteated a pivot table which list all the spare parts sells we have
made this year. The list is every sale and the pivot table is looking at
salesa value, profit margin, actual costs, etc by customer and parts category.

I have just been asked if I can create a box where a customer name can be
inputted and it brings up the rsults. Is this possible and can anyone help?

Thanks
Winnie

 

[Dave Peterson]

You could drag the customer field to a page field and have something very close
to what you want.

 

[winnie123]

Thanks Dave,

It does work but my boss just doesnt like filters and he just wants to type
in the customer name. Any other suggestions?

Thanks

 

[Dave Peterson]

Not from me.

Well, maybe one. Tell your boss that if he (and you) uses the features that are
built into excel, then you'll have more time to spend on the work that brings in
revenue.

But that's just my opinion.

Maybe someone else will have an idea that suits your boss better.

 

[winnie123]

Thanks,

I guess he will just have to use the filter or the (crtl+F)

Winnie

if you create the pivot with the customer field on the far left hand side
like Dave suggested and leave the table to show all customers, you could do a
search for customer names (ctrl+F), and then once found scroll down the sales
data. This would avoid using the filters.

Thanks
Matt

Thanks Dave,

It does work but my boss just doesnt like filters and he just wants to type
in the customer name. Any other suggestions?

Thanks

You could drag the customer field to a page field and have something very close
to what you want.

[quoted text clipped - 10 lines]

Thanks
Winnie

--
Matt Lynn

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200810/1

 

[winnie123]

Thanks Dave,

I agree with you.

Winnie

Not from me.

Well, maybe one. Tell your boss that if he (and you) uses the features that are
built into excel, then you'll have more time to spend on the work that brings in
revenue.

But that's just my opinion.

Maybe someone else will have an idea that suits your boss better.

 

[Dave Peterson]

I wouldn't use this!

I created a single pivottable (in xl2003) on a sheet. It had Customer as the
single page field.

I used the worksheet_change event (not a button) to allow the user/boss to type
the customer name. If it didn't match one of the names in the customer field,
then it would beep.

I used F1 as my input cell.

If you want to try (I still wouldn't!), this may get you closer:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'one cell at a time
Dim myPT As PivotTable
Dim pCtr As Long
Dim FoundAMatch As Boolean

If Target.Cells.Count > 1 Then Exit Sub

If Intersect(Target, Me.Range("F1")) Is Nothing Then
Exit Sub
End If

Set myPT = Me.PivotTables(1)

FoundAMatch = False
With myPT.PageFields("customer")
For pCtr = 1 To .PivotItems.Count
If LCase(Target.Value) = LCase(.PivotItems(pCtr).Value) Then
FoundAMatch = True
Exit For
End If
Next pCtr
If FoundAMatch = True Then
.CurrentPage = Target.Value
.Parent.RefreshTable
Else
Beep
End If
End With
End Sub

Right click on the worksheet tab that holds the pivottable. Select view code
and paste this in the code window that just opened.

Then go back to excel and format F1 (or whatever cell you used) nicely--so it
stands out.

Then try typing in a different customer.

Have I said that I wouldn't use this???

Thanks Dave,

I agree with you.

Winnie