I have defined a class to handle the user interface layer between a
form and a worksheet. The below is what is contained in the class
module:

Public Event GetJudicialOfficers(ByRef vsJOList As Variant)
Public Event GetCourtRooms(ByRef vsCourtrooms As Variant)

' Pick up change in Judicial Officer type from form
Public Sub JOTypeSelected(ByVal sType As String)
Dim vsOfficers As Variant

' Get the list of JOs from the return
vsOfficers = vUpdateJOList(sType)

' Pass the list back to the form
RaiseEvent GetJudicialOfficers(vsOfficers)

End Sub

Public Sub CourtSelected(ByVal sCourt As String)
Dim vsRooms As Variant

' Get the list of courtrooms based on selected court
vsRooms = vUpdateCourtList(sCourt)

' Pass the list back to the form
RaiseEvent GetCourtRooms(vsRooms)

End Sub

This compiles fine in both Excel 2003 and 2007. However in Excel 97
all the lines with either 'Public Event' or 'RaiseEvent' generate a
compile error (Identifier expected). Clearly this version of VBA does
not recognise these identifiers, I'm assuming along the same lines as
with Enum. Can anyone confirm that this is the case?

Cheers
Geoff

Event and RaiseEvent are not supported in Excel 97.

However, whilst it might not help you for your current needs, even in Excel
97 you can use WithEvents to trap and receive a custom event that you have
raised in another app, eg in a VB6 dll.

From what you describe there should be various alternative approaches to do
similar. Why not reference your class object directly in the form.

Regards,
Peter T

"geoff_ness" <(E-Mail Removed)> wrote in message
news:977dd03a-f072-4638-b93e-(E-Mail Removed)...
>I have defined a class to handle the user interface layer between a
> form and a worksheet. The below is what is contained in the class
> module:
>
> Public Event GetJudicialOfficers(ByRef vsJOList As Variant)
> Public Event GetCourtRooms(ByRef vsCourtrooms As Variant)
>
> ' Pick up change in Judicial Officer type from form
> Public Sub JOTypeSelected(ByVal sType As String)
> Dim vsOfficers As Variant
>
> ' Get the list of JOs from the return
> vsOfficers = vUpdateJOList(sType)
>
> ' Pass the list back to the form
> RaiseEvent GetJudicialOfficers(vsOfficers)
>
> End Sub
>
> Public Sub CourtSelected(ByVal sCourt As String)
> Dim vsRooms As Variant
>
> ' Get the list of courtrooms based on selected court
> vsRooms = vUpdateCourtList(sCourt)
>
> ' Pass the list back to the form
> RaiseEvent GetCourtRooms(vsRooms)
>
> End Sub
>
> This compiles fine in both Excel 2003 and 2007. However in Excel 97
> all the lines with either 'Public Event' or 'RaiseEvent' generate a
> compile error (Identifier expected). Clearly this version of VBA does
> not recognise these identifiers, I'm assuming along the same lines as
> with Enum. Can anyone confirm that this is the case?
>
> Cheers
> Geoff

Thanks Peter, I thought that might be the case. I'll most likely ditch
the class altogether as it basically serves no purpose other than to
raise those events for the form to trap. I think the form can just
call the functions to populate those lists directly, it's just never
as nice to do it that way. The VB6 idea is a good one, unfortunately
I'm restricted to VBA with this project.

Thanks
Geoff

On Jun 3, 8:54*pm, "Peter T" <peter_t@discussions> wrote:
> Event and RaiseEvent are not supported in Excel 97.
>
> However, whilst it might not help you for your current needs, even in Excel
> 97 you can use WithEvents to trap and receive a custom event that you have
> raised in another app, eg in a VB6 dll.
>
> From what you describe there should be various alternative approaches to do
> similar. Why not reference your class object directly in the form.
>
> Regards,
> Peter T
>
> "geoff_ness" <geoffne...@gmail.com> wrote in message
>
> news:977dd03a-f072-4638-b93e-(E-Mail Removed)...
>
> >I have defined a class to handle the user interface layer between a
> > form and a worksheet. The below is what is contained in the class
> > module:
>
> > Public Event GetJudicialOfficers(ByRef vsJOList As Variant)
> > Public Event GetCourtRooms(ByRef vsCourtrooms As Variant)
>
> > ' Pick up change in Judicial Officer type from form
> > Public Sub JOTypeSelected(ByVal sType As String)
> > Dim vsOfficers As Variant
>
> > ' Get the list of JOs from the return
> > vsOfficers = vUpdateJOList(sType)
>
> > ' Pass the list back to the form
> > RaiseEvent GetJudicialOfficers(vsOfficers)
>
> > End Sub
>
> > Public Sub CourtSelected(ByVal sCourt As String)
> > Dim vsRooms As Variant
>
> > ' Get the list of courtrooms based on selected court
> > vsRooms = vUpdateCourtList(sCourt)
>
> > ' Pass the list back to the form
> > RaiseEvent GetCourtRooms(vsRooms)
>
> > End Sub
>
> > This compiles fine in both Excel 2003 and 2007. However in Excel 97
> > all the lines with either 'Public Event' or 'RaiseEvent' generate a
> > compile error (Identifier expected). Clearly this version of VBA does
> > not recognise these identifiers, I'm assuming along the same lines as
> > with Enum. Can anyone confirm that this is the case?
>
> > Cheers
> > Geoff