Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox_Change event fires on workbook close
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox_Change event fires on workbook close
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox_Change event fires on workbook close
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox_Change event fires on workbook close
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ComboBox.Clear method calling ComboBox_Change event? | Excel Programming | |||
ComboBox_Change Event to Populate another Combox | Excel Programming | |||
Combobox_change event problem | Excel Programming | |||
worksheet_change event fires multiple times | Excel Programming | |||
File|Close w/Save fires Workbook_Open twice | Excel Programming |