How do I bind data to a ComboBox?

Need a "beforechange" event for combobox

  • I created a userform which reads data from a worksheet into text boxes on the userform. The user selects the worksheet from which he wishes to see data via a combobox. The combobox contains actual worksheet names. When the user selects a worksheet from the combobox, values from the worksheet are written to the input fields (text boxes) on the userform. The user can then run some calculations, change the values, run some more calculations, change the values again, etc. This does not affect the original values on the worksheet. When the user is finished he can either switch worksheets (using the combobox) or he can exit the user form. I would like to give him the opportunity to save the changes he made to the input fields if he desires. If the user decides to change worksheets I scan all the input fields on the userform and compare the values to those on the worksheet to see if anything has changed. If a change has been made I ask the user if the changes should be saved. If he says ¡°yes¡± I write the values from the userfrom over the values on the worksheet. Here is my question: When the user changes the combobox the change event is fired. The value returned from the combobox is the name of the NEW worksheet, not the old one. If I am going to check whether the user made modifications I need to know the name of the worksheet BEFORE the change occured. I have tried the events BeforeUpdate, Click, DropButtonClick, Enter, etc, but none of them seem to get me where I want to go. Please explain the best practice when dealing with situation. Do I need to have a change event associated with every input field on the form which would throw a flag when changed? This seems like a lot wasted code? Kyle

  • Answer:

    Kyle, I follow most of what you are saying except for the part where you have a problem. I guess this is the crux of the problem: When the user is finished he can either switch worksheets (using the combobox) or he can exit the user form. So if he switches worksheet, you want to then offer him the option of saving his changes but before the event has fired, he has selected a new sheet. So I would think the easiest solution is to save the sheet names he selects and then you can get the one next to last First, I would use the click event. If the user decides to type in the combobox, the change event is going to fire on every character. You can just go to your sub and replace Change with Click Here is some pseudo code that might give you some ideas: Private shNames as String Private Sub Userform_Initialize() shName = "" end Sub Private Combobox1_Click() Dim v as Variant, numNames as String if me.combobox1.ListIndex = -1 then exit sub shName = shNames & me.value & "," v = split(left(shName,len(shName)-1),",") numNames = v(Ubound(v)) - v(lbound(v)) + 1 if numNames > 1 then sOldName = v(Ubound(v) - 1) ans = msgbox( "Do you want to write your results to " & sOldName )& "?" . . . end if . . . end Sub will that work for you? . . .

Miningco.com Visit the source

Was this solution helpful to you?

Related Q & A:

Just Added Q & A:

Find solution

For every problem there is a solution! Proved by Solucija.

  • Got an issue and looking for advice?

  • Ask Solucija to search every corner of the Web for help.

  • Get workable solutions and helpful tips in a moment.

Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.