question

LindseySaddler-8379 avatar image
0 Votes"
LindseySaddler-8379 asked JohnKorchok answered

VBA newbie

I am trying to create an invoice from a pivot table and I watched a YouTube video using a VBA. However, when I wrote the code it doesn't work and I have no idea why. Please help!!!!!

office-vba-dev
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

You haven't posted any useful information about your macro. We might be able to help if you posted the code, explained how it should be working and included notes about which line(s) raise error messages, if any.

0 Votes 0 ·

I tried to attach the document and it would not let me. I am not receiving an error message, it is populating the invoice.

I have a pivot table with contractor names, services performed and the amount the are to be paid for each service. I would like to be able to choose a contractor from a drop down box and hit refresh and format the invoice based on that.

Let me know what else I can provide.

0 Votes 0 ·
LindseySaddler-8379 avatar image
0 Votes"
LindseySaddler-8379 answered JohnKorchok commented

@JohnKorchok Here is the code

Private Sub CommandButton1_Click()

Dim wks As Worksheet
Dim pvt As PivotTable

For Each wks In ThisWorkbook.Worksheets
For Each pvt In wks.PivotTables
pvt.RefreshTable
Next pvt
Next wks

End Sub



Private Sub Contractor_Change()

End Sub

Private Sub Refresh_Click()

End Sub

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

Dim rng As Range
Dim wks As Worksheet
Set wks = Sheets("Contractor")
Set rng = wks.Range("A4")

ComboBox1.List = wks.Range(rng.Address, rng.End(xlDown).Address).Value


End Sub



Private Sub ComboBox1_Change()

 Application.ScreenUpdating = False
 Application.DisplayAlerts = False

 Dim objPT As PivotTable, strPitem As String
 Dim x As Integer

 'Set String equal to ComboBox
 strPitem = ComboBox1.Text

With ActiveSheet
Set objPT = .PivotTables(1)
' specify with field
With objPT.PivotFields("Contractor")
.AutoSort xlManual, "Contractor"

     'loop through specified field's items & hide them all
 For x = 0 To .PivotItems.Count
     On Error Resume Next 'error trap in case of no data
     If .PivotItems(x).Name = strPitem Then
     .PivotItems(strPitem).Visible = True
     Else
     .PivotItems(x).Visible = False
     End If
 Next x

     On Error GoTo 0

 End With

End With

Application.DisplayAlerts = True
Application.ScreenUpdating = True


End Sub

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for posting. Testing this will be much easier if we don't have to reconstruct the workbook. Please upload the file to OneDrive or other cloud service and post a Share link here.

0 Votes 0 ·
LindseySaddler-8379 avatar image
0 Votes"
LindseySaddler-8379 answered

@JohnKorchok

https://1drv.ms/x/s!AvgNvvK5B6BahnQlatGBOkcAF2oF?e=xnEwfV

Let me know if that doesn't work.

Thank you.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JohnKorchok avatar image
0 Votes"
JohnKorchok answered

I put the macro in Design Mode and checked the dropdown name. It's called DropDown4 and has a Sub DropDown4_Change empty macro stored in Module 2. But the macro code that is supposed to run is in a Sub called Private Sub ComboBox1_Change() stored in Sheet3 (AUGUST SERVICES). Either rename the dropdown, or move the code to Module2 and rename the commands in it to the actual control names you are using.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.