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!

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

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