VBA Command Button

Copper Contributor

I created a command button with a VBA code to print invoices for clients.  Yesterday, when I clicked the button it worked, but now when I click the button nothing happens.  I have tried all the fixes.  I have deleted the button and reset it.  I have gone into the VBA design mode and tried running the code from there.  Nothing happens.  


I can't figure out why. I have updated, ran the fix from Microsoft, deleted the .exd files, and still nothing.  Can someone please help?


Private Sub CommandButton1_Click()

Dim clientname As String
Dim location As String
Dim address As String
Dim r As Long
Dim slrate As String
Dim clientrate As String
Dim discount As String
Dim payments As String
Dim balancedue As String
Dim notes As String
Dim billingdate As String
Dim duedate As String
Dim pastdue As String
Dim path As String
Dim myfilename As String
lastrow = Sheets("June 2017 SL Fees").Range("O" & Rows.Count).End(xlUp).Row
r = 10
For r = 10 To lastrow
If Cells(r, 15).Value = "Detox" Then GoTo nextrow
If Cells(r, 15).Value = "INPT" Then GoTo nextrow
If Cells(r, 15).Value = "Discharged" Then GoTo nextrow
If Cells(r, 15).Value = "" Then GoTo nextrow

clientname = Sheets("June 2017 SL Fees").Cells(r, 16).Value
location = Sheets("June 2017 SL Fees").Cells(r, 15).Value
address = Sheets("June 2017 SL Fees").Cells(r, 23).Value
slrate = Sheets("June 2017 SL Fees").Cells(r, 24).Value
clientrate = Sheets("June 2017 SL Fees").Cells(r, 25).Value
discount = Sheets("June 2017 SL Fees").Cells(r, 26).Value
payments = Sheets("June 2017 SL Fees").Cells(r, 28).Value
balancedue = Sheets("June 2017 SL Fees").Cells(r, 29).Value
notes = Sheets("June 2017 SL Fees").Cells(r, 30).Value
billingdate = Sheets("June 2017 SL Fees").Cells(r, 32).Value
duedate = Sheets("June 2017 SL Fees").Cells(r, 31).Value
pastdue = Sheets("June 2017 SL Fees").Cells(r, 27).Value

Cells(r, 15).Value = "Detox"
Cells(r, 15).Value = "INPT"
Cells(r, 15).Value = "Discharged"
Cells(r, 15).Value = ""
Workbooks.Open ("C:\Users\SobaUser\Documents\SL Invoice\SimpleInvoice.xlsx")
ActiveWorkbook.Sheets("Simple Invoice").Activate
ActiveWorkbook.Sheets("Simple Invoice").Range("B11").Value = billingdate
ActiveWorkbook.Sheets("Simple Invoice").Range("A16").Value = clientname
ActiveWorkbook.Sheets("Simple Invoice").Range("A17").Value = address
ActiveWorkbook.Sheets("Simple Invoice").Range("B23").Value = slrate
ActiveWorkbook.Sheets("Simple Invoice").Range("B24").Value = location
ActiveWorkbook.Sheets("Simple Invoice").Range("B25").Value = discount
ActiveWorkbook.Sheets("Simple Invoice").Range("B26").Value = clientrate
ActiveWorkbook.Sheets("Simple Invoice").Range("B27").Value = pastdue
ActiveWorkbook.Sheets("Simple Invoice").Range("B28").Value = payments
ActiveWorkbook.Sheets("Simple Invoice").Range("B29").Value = balancedue
ActiveWorkbook.Sheets("Simple Invoice").Range("B33").Value = duedate
ActiveWorkbook.Sheets("Simple Invoice").Range("A47").Value = notes

path = "C:\Users\SobaUser\Documents\SL Invoice"
ActiveWorkbook.SaveAs Filename:=path & clientname & "-" & billingdate & "-" & ".pdf"
myfilename = ActiveWorkbook.FullName
Application.DisplayAlerts = True
'ActiveWorkbook.Printout copies:=1
ActiveWorkbook.Close SaveChanges:=False


Next r

End Sub

2 Replies
I'm not sure but it might be worth checking the Trust Centre for Active X and Macros

Are you sure it's running? Put your cursor in the sub routine and hit F8 to step through it. If nothing happens, events are probably disabled. Restart Excel, or, in the Immediate Window type "Application.EnableEvents = True" (w/o quotes) and hit ENTER, then try again.