Questions on Combo Box

[Souny]

Hello all,

I have a combo box (from Control Toolbox) in Sheet1, and there are
selections in the combo box. The code for that combo box will execute based
on the selection that we choose from it.

Somehow, the combo box executes when the Excel file is opened. From my
understanding, the code should only execute when we make the selection from
it. Am I correct? The code is in Sheet1 and does not have anything like
"Private Sub Auto_Open()" would cause it to execute when the file is opened.

Did I do something wrong? Below is the structure of my code.

Private Sub cboCode_Click()

Application.ScreenUpdating=False

Select Case cboCode.Value
Case "Selection1"
'Do the following
Case "Selection2"
'Do the following
Cash "Selection3"
'Do the following
Case Else
'Do the following
End Select

Application.ScreenUpdating = True
End Sub

Thanks.

 

[JLGWhiz]

There is nothing in the code you posted that would cause the code to execute
upon opening of the file. But if there is a Workbook_Open calling sub, it
could cause it to execute. Look in the ThisWorkbook code module for code
that calls:

cboCode_Click

 

[Souny]

JLGWhiz,

Thank you very much for your helps.

I don't have Workbook_Open calling sub nor ThisWorkbook code module.
However, I do have a line "ActiveWorkbook.unProtect" right below the line
"Application.ScreenUpdating = False".

I think the code "ActiveWorkbook" triggles the auto execution because after
I move that line in the Select Case section, auto execution does not happen
anymore when the file is opened.

I am learning something that by having ActiveWorkbook at the beginning of
the code, it would triggle the auto execution.

I think I am correct about the ActiveWorkbook. Please tell me if I am
wrong.

Thanks again.

 

[Souny]

JLGWhiz,

I am almost pulling my hairs out.

I am still having the same problem. Even I move the list with
ActiveWorkbook.unProtect as part of Select Case statement as I mentioned in
my previous message, I am still having the problem.

I can't really think of what causes to execute the combobox code when the
file is opened.

Please help.

Thanks.

 

[JLGWhiz]

I cannot think of anything that would initiate a click event on the combobox
except an actual mouse click or a programmatic click called by another
macro. There is nothing that I know of in the workbook protection that ties
to a combobox click event.

 

[Souny]

JLGWhiz,

Thanks for the response.

I just did a quick test in a new Excel file. In a new Excel file, I create
a combo box in Sheet1 with the following code in VBProject of Sheet1:

private sub cboTest_Click()
application.screenupdating=false
select case cboTest.value
case "Selection1"
msgbox "selection1"
case "selection2"
msgbox "selection2"
case "selection3"
msgbox "selection3"

end select
application.screenupdating=true
end sub

Even with that test, the code automatically executes when the file is
opened. I don't understand.

As we all know a new Excel file is a clean version. Therefore, I don't have
any code buries in any sheets to call the procedures.

Is that the nature of combo box to execute automatically when the file is
opened?

Thanks.

 

[JLGWhiz]

I set up a combobox named cboTest on Sheet1 of a workbook. I copied and
pasted your code into Sheet1 code module. I added a listfill range with
values for the select case statement and a linked cell for the value. I
then tested the click event to make sure it would fire. I then closed the
workbook and reopened it but it would not duplicate your problem.
Everything worked as expected.

 

[Souny]

JLGWhiz,

Thanks for continuing to help me.

The values in the combo box is looking at cells $C$1:$C$4, and I have the
reference $C$1:$C$4 in the ListFillRange field of the combo box Properties.
I have nothing in the LinkedCell field of the Properties.

When you say "I added a listfill range with values for the select .....", do
you populate the combo box values via the code? Could you send me the code
that you tested that worked?

Thanks.

 

[JLGWhiz]

It was your code that I used. The only difference is that I used cells in
column A for the ListFillRange instead of column C. It is not your code
that is causing the problem. You have something elsewhere in your workbook
that causes the cboTest_Click to fire. But I have not a clue as to what it
is.

 

[JLGWhiz]

This is a long shot, but you might try it:

On the menu bar, Tools>Options>Calculation....>>Uncheck Update remote
references and Save extended link values.

Then try saving and reopening the file.