combo box change event

T

Tom

I have a combobox on a sheet and when I save the workbook under a new name
and start to perform any actions (i.e. run some code or enter data) the
combobox change event fires. It does this even though I haven't clicked the
combobox or changed the value displayed in the combobox. Also it only does
this once after the file has been saved. After the event fires the first
time it does not fire again if I make any further changes (i.e. run code or
enter data).

Why is the change event firing when I don't touch or access the comboBox and
why is it doing only once?
 
C

Charlie

I presume somewhere in your code you initially load the combobox? Could you
post that? Maybe that will give us a clue.
 
T

Tom

No the comboBox is not initially loaded. The values for the comboBox are
coming from a value list in another sheet. So the comboBox is always loaded.
There is button to allow the user to save the file under another name. That
code is:

Sub Save_Under_New_Name()
' Save file under new name

Dim fFilter As String
Dim NewName As String
Dim NewBook As Workbook
Dim fName As String

enter_file_name:

NewName = "<Save Under New Name>"
fFilter = "Excel Files (*.xls), *.xls"
fName = Application.GetSaveAsFilename _
(NewName, fileFilter:=fFilter)

If fName = "False" Then
MsgBox "Save Canceled ... "
Else
If fName = Application.ActiveWorkbook.Name Then
MsgBox "File cannot be saved under this name. Please enter
different name."
GoTo enter_file_name
Else
Application.ActiveWorkbook.SaveAs (fName)
End If

End If
MsgBox "File saved under a different name. If you wish to requalify more
units please download the master spreadsheet from DDS"

Application.ActiveSheet.Range("A9").Select
Application.ActiveSheet.Protect ("unit")
Application.ActiveWorkbook.Protect "unit", True
' disable the ComboBox before saving
Application.ActiveSheet.ComboBox1.Enabled = False

End Sub
 
C

Charlie

I suspect somewhere in one of your subs or functions the combobox is being
set for the first time, but that first time may not be when you are expecting
it. What will fire the Change event is a line something like

Sheets(1).ComboBox1 = "???"
Sheets(1).ComboBox1.Text = "???"

once the combobox text has been set, executing the line above will not fire
the change event a second time because the text in the box already matches
what is being put into it. Hence it stops firing. Try and find that line
and then try and follow when THAT sub is being executed.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top