training access vba london - Word vba

The UK's most regular instructor-led training courses.
Training information: training access vba london · Excel london

Resolved · Low Priority · Version Standard

Word vba

Jane has attended:
Word VBA course
Word VBA course

by - delegate Jane [2 posts] (2007 Nov 15 Thu, 13:38) Reply

am preparing a template and am asking a question which i need a message box to appear. i have inserted the following code

Private Sub EUVAT_Click()
If BillAmounts.EUVAT = Yes Then
Sub NewMessage()
MsgBox("Is the Client VAT number shown above correctly?", vbYesNo) = vbYes
End If
End Sub

when i click yes in the template BillAmounts gets highlighted and error Expected End Sub.

I would be grateful for some help, I havent done any vba since training in June !

RE: word vba

by - trainer Carlos gold contributer [360 posts] (2007 Nov 21 Wed, 10:29) Reply

Hi Jane

The error message is due to the fact you did a Sub Newmessage in the middle of the if statement. Try the following code istead:

Private Sub EUVAT_Click()

Dim Reply As Byte

If BillAmounts.EUVAT = Yes Then

Reply = MsgBox("Is the Client VAT number shown above correctly?", vbYesNo)

If Reply = vbYes Then

'Code for Yes reply

Else

'Code for No reply

End If

End If

End Sub

Hope this helps

Carlos

· What Is Microsoft Excel VBA and How Can It Help You Work Smarter?
· Company Roles in Visual Basic Training

Vba tip:

Suspend DisplayAlerts in VBA

To stop Excel asking you things like "Do you want to delete this file...", use the following line of code at the beginning of the relevant VBA procedure:

Application.DisplayAlerts = False

At the end of the procedure make sure you use the following code to reactivate Display Alerts:

Application.DisplayAlerts = True

View all VBA hints and tips