Use Pivot table from a User Form (2003 SP3)

[steveh]

Morning all and a happy New Year

As part of my staff planner I have a search function to check for dates that an employee has already booked or taken with a next button to loop through the range until the last booked date is shown.

I got to thinking that it might be nice if by selecting the 'Staff' name on the user form it could automatically invoke a filter to show only that employees dates and then when the close button it would close the form and go back to the orignal 'all' selection on the page.

Is this possible, and yes the inevitable, how do I go about it? I have attached an example of the page I use with the current form and a Macro (3) that I recorded to try and incoperate into the user form but I don't know how to do that and match the name to the 'staff' range rather than an individual

[HansV]

1. To suppress duplicates in the combo box list, and to sort the names in alphabetic order, change the UserForm_Initialize event procedure to

Code: Select all

Private Sub UserForm_Initialize()
  Dim wsh As Worksheet
  Dim r As Long
  Dim m As Long
  Dim col As New Collection
  cboEmployee.Clear
  Set wsh = Worksheets("CheckLeave")
  m = wsh.Range("A1").End(xlDown).Row
  On Error Resume Next
  For r = 2 To m
    col.Add Item:=wsh.Range("A" & r).Value, Key:=CStr(wsh.Range("A" & r).Value)
  Next r
  On Error GoTo 0
  BubbleSort col
  For r = 1 To col.Count
    cboEmployee.AddItem col(r)
  Next r
  Me.cmbSearch.Enabled = False
  Me.cmbFindNext.Enabled = False
End Sub

It needs the BubbleSort procedure below, copy it to a standard module, for example Module2:

Code: Select all

Sub BubbleSort(ByRef col As Collection)
  Dim varTemp As Variant
  Dim i As Long
  Dim j As Long
  For i = 1 To col.Count - 1
    For j = i + 1 To col.Count
      If col(i) > col(j) Then
        varTemp = col(j)
        col.Remove j
        col.Add varTemp, CStr(varTemp), i
      End If
    Next j
  Next i
End Sub

2. To filter the form when the user selects an employee:

Code: Select all

Private Sub CboEmployee_Change()
  Dim wsh As Worksheet
  Set wsh = Worksheets("CheckLeave")
  If Me.cboEmployee = "" Then
    Me.cmbSearch.Enabled = False
    Me.cmbFindNext.Enabled = False
    wsh.ShowAllData
  Else
    Me.cmbSearch.Enabled = True
    Me.cmbFindNext.Enabled = False
    wsh.Range("A1").AutoFilter Field:=1, Criteria1:=Me.cboEmployee
  End If
End Sub

3. Finally, to show all records when the form is closed:

Code: Select all

Private Sub cmbClose_Click()
  Dim wsh As Worksheet
  Set wsh = Worksheets("CheckLeave")
  wsh.ShowAllData
  Unload Me
End Sub

[Don Wells]

To protect against any blanks in your range of employee names I would recommend changing one line in the UserForm_Initialize event procedure

From:
     m = wsh.Range("A1").End(xlDown).Row

To:
     m = wsh.Range("A" & wsh.rows.count).End(xlUp).Row

[HansV]

LOL - that's what I had originally, but there was a hidden 0 somewhere below the list of employees, so a 0 and a blank got added to the list.

I think that in this specific situation, there will be no blanks in the range.

[steveh]

LOL - that's what I had originally, but there was a hidden 0 somewhere below the list of employees, so a 0 and a blank got added to the list.

I think that in this specific situation, there will be no blanks in the range.

Thank you Hans and Don

Correct Hans, there should be no blanks, I don't know how that slipped in. The code works wonderfully