I have a workbook with a combobox 'CUPrevious' on one of its worksheets, with
change event coded (in the worksheet module) as follows:

Private Sub CUPrevious_Change()

Dim t As String

Application.ScreenUpdating = False
If Application.EnableEvents Then
Application.EnableEvents = False
End If

t = CUPrevious.LinkedCell

If Range(t).Value <> "" Then
Call CUFunc(Range(t).Text)
End If

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

For some reason, whenever the workbook closes, this event is triggered and
if that sheet is not active at the time, the line
t = CUPrevious.LinkedCell
causes a runtime error as the value of linkedcell for the combobox seems to
be inaccessible. This is a problem which I have been able to get around by
calling the worksheet's Activate method in a Workbook.Before_Close procedure.
However, I don't see why the combobox change event should fire at all - the
value is not changing. Does anyone know why this is happening? More to the
point, is there some way to stop the event firing?

--
There are 10 types of people in the world - those who understand binary and
those who don't.

Untested...

Try putting the linked cell on a different worksheet.

If that doesn't work, how about dropping the linked cell and assigning the value
of the combobox to the cell via code.

Geoff wrote:
>
> I have a workbook with a combobox 'CUPrevious' on one of its worksheets, with
> change event coded (in the worksheet module) as follows:
>
> Private Sub CUPrevious_Change()
>
> Dim t As String
>
> Application.ScreenUpdating = False
> If Application.EnableEvents Then
> Application.EnableEvents = False
> End If
>
> t = CUPrevious.LinkedCell
>
> If Range(t).Value <> "" Then
> Call CUFunc(Range(t).Text)
> End If
>
> Application.EnableEvents = True
> Application.ScreenUpdating = True
>
> End Sub
>
> For some reason, whenever the workbook closes, this event is triggered and
> if that sheet is not active at the time, the line
> t = CUPrevious.LinkedCell
> causes a runtime error as the value of linkedcell for the combobox seems to
> be inaccessible. This is a problem which I have been able to get around by
> calling the worksheet's Activate method in a Workbook.Before_Close procedure.
> However, I don't see why the combobox change event should fire at all - the
> value is not changing. Does anyone know why this is happening? More to the
> point, is there some way to stop the event firing?
>
> --
> There are 10 types of people in the world - those who understand binary and
> those who don't.

--

Dave Peterson

In fact, if you have the listfillrange on the same sheet, you may want to move
that to a different worksheet, too.

Geoff wrote:
>
> I have a workbook with a combobox 'CUPrevious' on one of its worksheets, with
> change event coded (in the worksheet module) as follows:
>
> Private Sub CUPrevious_Change()
>
> Dim t As String
>
> Application.ScreenUpdating = False
> If Application.EnableEvents Then
> Application.EnableEvents = False
> End If
>
> t = CUPrevious.LinkedCell
>
> If Range(t).Value <> "" Then
> Call CUFunc(Range(t).Text)
> End If
>
> Application.EnableEvents = True
> Application.ScreenUpdating = True
>
> End Sub
>
> For some reason, whenever the workbook closes, this event is triggered and
> if that sheet is not active at the time, the line
> t = CUPrevious.LinkedCell
> causes a runtime error as the value of linkedcell for the combobox seems to
> be inaccessible. This is a problem which I have been able to get around by
> calling the worksheet's Activate method in a Workbook.Before_Close procedure.
> However, I don't see why the combobox change event should fire at all - the
> value is not changing. Does anyone know why this is happening? More to the
> point, is there some way to stop the event firing?
>
> --
> There are 10 types of people in the world - those who understand binary and
> those who don't.

--

Dave Peterson

Thanks Dave

In the end I just changed the code in the change event as follows:

If t <> "" Then
If Range(t).Value <> "" Then Call AnzsicCU(Range(t).Text)
End If

Somewhat convoluted but it gets the job done for both normal operation of
the combobox and for the workbook close event. The linked cell could move to
a different worksheet but that would require rather more changes to the UI
than I would like to make at this point - thanks for the reply though

I'm still mystified as to why the change event fires at all, but at least I
can close the workbook...
--
There are 10 types of people in the world - those who understand binary and
those who don't.


"Dave Peterson" wrote:

> Untested...
>
> Try putting the linked cell on a different worksheet.
>
> If that doesn't work, how about dropping the linked cell and assigning the value
> of the combobox to the cell via code.
>
> Geoff wrote:
> >
> > I have a workbook with a combobox 'CUPrevious' on one of its worksheets, with
> > change event coded (in the worksheet module) as follows:
> >
> > Private Sub CUPrevious_Change()
> >
> > Dim t As String
> >
> > Application.ScreenUpdating = False
> > If Application.EnableEvents Then
> > Application.EnableEvents = False
> > End If
> >
> > t = CUPrevious.LinkedCell
> >
> > If Range(t).Value <> "" Then
> > Call CUFunc(Range(t).Text)
> > End If
> >
> > Application.EnableEvents = True
> > Application.ScreenUpdating = True
> >
> > End Sub
> >
> > For some reason, whenever the workbook closes, this event is triggered and
> > if that sheet is not active at the time, the line
> > t = CUPrevious.LinkedCell
> > causes a runtime error as the value of linkedcell for the combobox seems to
> > be inaccessible. This is a problem which I have been able to get around by
> > calling the worksheet's Activate method in a Workbook.Before_Close procedure.
> > However, I don't see why the combobox change event should fire at all - the
> > value is not changing. Does anyone know why this is happening? More to the
> > point, is there some way to stop the event firing?
> >
> > --
> > There are 10 types of people in the world - those who understand binary and
> > those who don't.
>
> --
>
> Dave Peterson
>