Runing a Macro Before Save

PKW57 · #1 09-24-2018, 08:47 AM

Greetings,

This is my first attempt at VBA in Word. I'm used to VBA in Excel and the differences are driving me nuts.

I have code that is supposed to execute before the document is saved. I created a Class Module based on Google results (along with the Register Event Handler sub in ThisDocument & a Module with the Sub Register Event Handler), and placed my code in it as follows:

Code:

Public WithEvents App As Word.Application

Private Sub App_DocumentBeforeSave _
 (ByVal Doc As Document, _
 SaveAsUI As Boolean, _
 Cancel As Boolean)

Qual_Status
Doc_Status
Equip_Status


End Sub
Private Sub Qual_Status()

'Set Qual status to Conditional, Pass or Fail
If OptionButton13 = True Then
    Label1 = "CONDITIONAL"
    Label1.ForeColor = &H80FF&
    End
End If

If OptionButton5 And OptionButton7 And OptionButton71 And OptionButton11 = True Then
    Label1 = "PASS"
    Label1.ForeColor = &H8000&
Else: Label1 = "FAIL"
    Label1.ForeColor = &HC0&
End If

End Sub

Private Sub Doc_Status()

'Set Documentation Status
If OptionButton17 And OptionButton16 And OptionButton15 = True Then
    Label2 = "RELEASED"
    Label2.ForeColor = &H8000&
Else: Label2 = "In Process"
    Label2.ForeColor = &H0&
End If

End Sub

Private Sub Equip_Status()

'Set Equipment status
If Label1 = "PASS" And Label2 = "RELEASED" Then
    Label3 = "RELEASE TO PRODUCTION"
    Label3.ForeColor = &H8000&
ElseIf Label1 = "PASS" And Label2 = "CONDITIONAL" Then
    Label3 = "RELEASE TO PRODUCTION"
    Label3.ForeColor = &H80FF&
Else: Label3 = "ON HOLD"
    Label3.ForeColor = &H0&
End If


End Sub

It runs when I save but errors out on the 1st Else statement (colored red above) with a 424: Object Required, even though the previous command for Label1 forecolor executed cleanly.

If I put this exact same code in ThisDocument, I can execute each sub manually with no problems.

Any assistance with this would be greatly appreciated.

Thanks,
~ Phil

macropod · #2 09-24-2018, 05:10 PM

You have a mix of an in-line statement with another on the next line. You should have:

Code:

Else
    Label1 = "FAIL"
    Label1.ForeColor = &HC0&

PKW57 · #3 09-25-2018, 05:50 AM

Paul,

Thanks for the timely response.

I made the change as you suggested, but it still hangs at the same place with the same Object Required error.

macropod · #4 09-25-2018, 02:29 PM

If believe you need to specify the forecolour as:
Label1.ForeColor = &H000000C0&

macropod · #5 09-25-2018, 03:32 PM

Cross-posted at: https://www.excelforum.com/word-prog...fore-save.html
For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184

PKW57 · #6 09-26-2018, 01:17 PM

Many thanks for the assistance!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
VBA Macro to Save PPT to PDF	mashiulalam	PowerPoint	1	05-10-2017 08:11 AM
Macro to save as pdf with ability to choose save as folder	rvessio	Word VBA	4	07-25-2016 12:37 PM
Macro: How to get this macro to save to a specific location	LOUF	Word VBA	1	12-07-2015 06:47 PM
Continuously runing a macro	Eduardo Care	Word VBA	4	09-01-2015 12:11 PM
Macro Won't Save	lou0915	Word VBA	2	10-17-2009 08:13 PM