L
ludo.soete
Hi,
I wrote some code to create a pivot table, with following
PivotFieldNames:
membernames (column B), date (column C), activity location (column D),
travelexpence (column G)
I have also a userform with 2 listboxes.
Listbox1 is filled with the pivottable FieldItems from column D
(activity location)
Listbox2 is filled with the NOT SELECTED items from listbox1, this is
used to create my pivot table
All this code is placed into my Personal.xls workbook, and works
perfect.
The problem i have now, is that i copied this code into an other
workbook (Admin51.xls) and i can't fill the listbox anymore.
I do have folowing error: Object required
here's the ( little messy - need to clean up when it runs) code :
Note that the NOT declared variables in the example below are declared
in the fist module as Public.
Private Sub UserForm_Initialize()
With Me
.cmdExit.Picture = LoadPicture(strThisPath & "\trffc04.ICO")
.cmdExec.Enabled = False
End With
'
' open kostennota sjabloon - open expence template
Workbooks.Open FileName:=strThisPath & "\Init\Kostennota NDF.XLT"
Kostennota = ActiveWorkbook.Name
' open kilometer kostenafrekening - open expence data file
Workbooks.Open FileName:=strThisPath & "\Data\KM kostenafrekening
" & Year(Now) & ".XLS"
Kostenlijst = ActiveWorkbook.Name
' DraaitabelOpdrachten Macro
Const FirstRowColumn = "$B$4"
Dim LastRowColumn As String
If Me.obPaidEvents = True Then
Sheets("Opdrachten").Select ' select needed sheet in
workbook "Kostenlijst"
Else
Sheets("Clubdagen").Select
End If
Range(FirstRowColumn).Select
LastRowColumn = selection.CurrentRegion.Rows.Count '+
Range(FirstRowColumn).Row
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:= _
"Opdrachten!R4C2:R" & LastRowColumn & "C7").CreatePivotTable
TableDestination:="", TableName:= _
"Draaitabel1"
ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("Draaitabel1").SmallGrid = False
ActiveSheet.Name = "Pivottable"
' Haal PivotField Items op en zet die in listbox1- Get PivotFieldItems
& place in Listbox1
' example comes from the help file
With Worksheets("Pivottable").PivotTables(1)
c = 1
i = 3
r = 1
Cells(r, c) = .PivotFields(i).Name
r = r + 1
For X = 1 To .PivotFields(i).PivotItems.Count
frmPivottable.listbox1.AddItem .PivotFields(i).PivotItems(X).Name
<<<<< Generate Error message : Object required
r = r + 1
Next
End With
' vul listbox2 met de NIET geselecteerde items uit listbox1
For X = 0 To listbox1.ListCount - 1
If listbox1.Selected(X) = False Then
ListBox2.AddItem (listbox1.List(X))
End If
Next X
End Sub
I guess that the error is due to the fact that the code doesn't run
anymore from my Personal.xls workbook, but from my other workbook
Admin51, and the right reference is missing.
Can anyone point me out to solve the problem?
I need it to print the expence notes who need to be signed before
paiment of the expences are done.
Thanks in advance.
Regards,
Ludo
I wrote some code to create a pivot table, with following
PivotFieldNames:
membernames (column B), date (column C), activity location (column D),
travelexpence (column G)
I have also a userform with 2 listboxes.
Listbox1 is filled with the pivottable FieldItems from column D
(activity location)
Listbox2 is filled with the NOT SELECTED items from listbox1, this is
used to create my pivot table
All this code is placed into my Personal.xls workbook, and works
perfect.
The problem i have now, is that i copied this code into an other
workbook (Admin51.xls) and i can't fill the listbox anymore.
I do have folowing error: Object required
here's the ( little messy - need to clean up when it runs) code :
Note that the NOT declared variables in the example below are declared
in the fist module as Public.
Private Sub UserForm_Initialize()
With Me
.cmdExit.Picture = LoadPicture(strThisPath & "\trffc04.ICO")
.cmdExec.Enabled = False
End With
'
' open kostennota sjabloon - open expence template
Workbooks.Open FileName:=strThisPath & "\Init\Kostennota NDF.XLT"
Kostennota = ActiveWorkbook.Name
' open kilometer kostenafrekening - open expence data file
Workbooks.Open FileName:=strThisPath & "\Data\KM kostenafrekening
" & Year(Now) & ".XLS"
Kostenlijst = ActiveWorkbook.Name
' DraaitabelOpdrachten Macro
Const FirstRowColumn = "$B$4"
Dim LastRowColumn As String
If Me.obPaidEvents = True Then
Sheets("Opdrachten").Select ' select needed sheet in
workbook "Kostenlijst"
Else
Sheets("Clubdagen").Select
End If
Range(FirstRowColumn).Select
LastRowColumn = selection.CurrentRegion.Rows.Count '+
Range(FirstRowColumn).Row
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:= _
"Opdrachten!R4C2:R" & LastRowColumn & "C7").CreatePivotTable
TableDestination:="", TableName:= _
"Draaitabel1"
ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("Draaitabel1").SmallGrid = False
ActiveSheet.Name = "Pivottable"
' Haal PivotField Items op en zet die in listbox1- Get PivotFieldItems
& place in Listbox1
' example comes from the help file
With Worksheets("Pivottable").PivotTables(1)
c = 1
i = 3
r = 1
Cells(r, c) = .PivotFields(i).Name
r = r + 1
For X = 1 To .PivotFields(i).PivotItems.Count
frmPivottable.listbox1.AddItem .PivotFields(i).PivotItems(X).Name
<<<<< Generate Error message : Object required
r = r + 1
Next
End With
' vul listbox2 met de NIET geselecteerde items uit listbox1
For X = 0 To listbox1.ListCount - 1
If listbox1.Selected(X) = False Then
ListBox2.AddItem (listbox1.List(X))
End If
Next X
End Sub
I guess that the error is due to the fact that the code doesn't run
anymore from my Personal.xls workbook, but from my other workbook
Admin51, and the right reference is missing.
Can anyone point me out to solve the problem?
I need it to print the expence notes who need to be signed before
paiment of the expences are done.
Thanks in advance.
Regards,
Ludo