Combobox change event and workbook close/open

[charisma7]

I hope I can explain this properly.

I have a combobox_change sub that amends two fields depending on the value of what's selected. Those fields, however, are unlocked and able to be overwritten by the user.

The problem is that this change event seems to be running upon either workbook close and/or open, as after saving, closing, and re-opening, the fields are replaced with the values that would be provided based upon the combobox_change code, and NOT what the user may have subsequently typed.

Is there any way to either prevent this code from running upon workbook close/open...or any other ideas/workarounds?

I thought of possibly writing workbook open and beforeclose events to lock those fields and therefore prevent changes, but that seems more difficult than necessary.

 

[awatt]

I hope I can explain this properly.

I have a combobox_change sub that amends two fields depending on the value of what's selected. Those fields, however, are unlocked and able to be overwritten by the user.

The problem is that this change event seems to be running upon either workbook close and/or open, as after saving, closing, and re-opening, the fields are replaced with the values that would be provided based upon the combobox_change code, and NOT what the user may have subsequently typed.

Is there any way to either prevent this code from running upon workbook close/open...or any other ideas/workarounds?

I thought of possibly writing workbook open and beforeclose events to lock those fields and therefore prevent changes, but that seems more difficult than necessary.

Can you give a specific example of what is going on?

What are these two fields you are mentioning?

Do you have any code in ThisWorkbook for Open, BeforeSave, AfterSave, or Before Close?

 

[charisma7]

Can you give a specific example of what is going on?

What are these two fields you are mentioning?

Do you have any code in ThisWorkbook for Open, BeforeSave, AfterSave, or Before Close?

Thanks for your reply.

The "fields" are just cells that are populated with numbers based upon a vlookup (value of combobox is found in vlookup to grab the numbers that populate the cells) that runs on the combobox_change event.

Those cells may be filled with 1.50 (or anything), but the cells are unlocked and the user can type over them if they like.

The problem is that the cells may pre-fill with 1.50, then typed over with 1.10. I save the workbook, close, then re-open. At that time, the cell is back to 1.50.

I can only assume it's because it's running the combobox_change event upon either open or close.

 

[charisma7]

Sorry for the partial reply. No, there is nothing in ThisWorkbook at all.

 

[awatt]

Okay,

Can you post your code for the change event?

 

[charisma7]

Sure thing!

Private Sub ComboBox1_Change()
Dim targetRange1 As Range
Dim targetrange2 As Range
Dim classsurcharge As Single
Class = Range("Class")
Set targetRange1 = Range("Liability_Class")
Set targetrange2 = Range("PD_Class")
If Class = "(Select)" Then
targetRange1.Value = ""
targetrange2.Value = ""
Else
classsurcharge = Application.VLookup(Class, Range("Class_Surcharges"), 2, False)
targetRange1.Value = Format(classsurcharge, "Fixed")
targetrange2.Value = Format(classsurcharge, "Fixed")
End If
End Sub

 

[charisma7]

FYI - I was able to solve this, and am posting for anyone else who may experience the same issue.

First, I declared a global Boolean variable (in this case, I used gblnDD) in an existing module.

Next, I set up a new event procedure for ComboBox1_DropButt******* to set the gblnDD to True.

Then in my existing Combobox1_Change event, I added a check to see if gblnDD is True before executing my code. Then set it back to false after.

Now, the Combobox1_Change event is only triggered if the combobox has been dropped down by clicking on it first. No other system events will allow it to execute.