Userform ComboBox selection not displaying on first attempted selection

Hello.
I have ComboBoxes (CB) on a userform that I use to select an item from a range of cells in the WorkSheet, this range is in the CB properties under RowSource e.g. "Sheet1!AK61:AK67".

As I want the selected item to be placed in a WorkSheet cell, I have put the following code in the UserForm Module.

Please Login or Register  to view this content.

In the WorkSheet I copy this to other cells, but as I want to copy different data back to the cell and have it show in the ComboBox in the UserForm I have also marked the Properties of the ComboBox in the 'Control Source' as AG56
When the UserForm is opened up the comboBoxes (that are blank) i.e. no data in cell AG56

Everything works correctly except when I select the dropdown arrow on the ComboBox and click on an item for the first time, the ComboBox returns empty.
If I repeat the selection it appears in the ComboBox as wanted.

My question is, What have I got wrong? is it the event handler or something in the ComboBox properties that is having a conflict.

I would appreciate your thoughts on this immeasurably.

This is my syntax for populating and using a ComboBox:

Please Login or Register  to view this content.

Hi Logit.
Seems like a lot of code to simply transfer ComboBox data to a cell on a sheet.
Like I said everything is working fine except I am having to select the item in the combobox twice to make it stick.

Perhaps the ControlSource in the Properties of the ComboBox is responding on the first attempt to select an item in the RowSource?

I'm trying to understand the logic flow of your program. Is there a way you can post a sample of the portion of your project you refer to in your post ? Attach the file without any confidential info. I'm curious to learn what you are attempting.

Thanks Logit, for your interest.

I have correct some errors in the first 'Book1' - see workbook1 above

Hi.
Really needs to happen when the userform is open.
Thanks.

yachrishere

I am not experiencing the issues you are having, clicking on the combobox drop down arrow. On my computer, the drop down functions as designed (first click) and transfers the data to the row/column indicated.

Not certain what to advise.

Im using Win10 / Excel 2007

Hi All, Thanks for the comments.
To answer XLnitwit's question, in my head I was thinking that the Change Event would work for the data inputting (sending the input data to the sheet cell) and the Control Source would work for the 'Import' button where it copies the data into column 'A' - from Column D or F (thus Populating the ComboBoxes with the new records).

To Logit. Thanks for taking a look, I am still having this issue with Win 10/excel2013.
My actual form has a few more Combo Boxes but this shouldn't matter, I noticed another command button in my form occasionally miss fires on first click so something else is going on.

What I think what is happening is the Control Source is firing first on occasions thus the result is a blank ComboBox (before the Change Event can action it's sub).
So there is a conflict in having a Change Event and a Control Source, I removed the Change Event and left the Control Source but was still having the problem (because the Control Source Cell was blank).
Then I removed the Control Source and kept the Change Event, this resolved the issue however it doesn't allow the Import button (bringing in 'Saved' data from column D or F to Column A) to populate the ComboBoxes as the there is no Control Source linking them to data in Cells in Column A.

Do you think the Answer is to code the Import button with a "Set Control Source" for the ComboBoxes after Importing the data to cells in column A (so the ComboBoxes are populated on the userform) then set the Control Sources of the ComboBoxes to nothing before exiting the sub (returning the form back to it's original setup).

In this case, could you advise me on the best way to "Set" and then "set to nothing" the Control Source for multiple ComboBoxes? Cheers.

I attempted to edit your UserForm from the download in Post #6 (Look on right side of posting headers above for #'s). I receive an error message saying I don't have permission to make changes. Looking over your workbook however, I do not find any setting or syntax coding that would prevent me from making edits. I then added a new UserForm (#2) and received the same error message. I mention this because of it being unusual ... no indication the form is protected, that I can find.

Two questions: Will the data in columns K & L always remain static ? Are you using those entries like a small database of selections from which to choose and the info will never change ? Or at some point, will you be changing your inventory of materials, dimensions, etc ?

Secondly, (your answer to the first question may answer this one), what is the purpose of "copying" the data from columns K & L via the combobox's then pasting same in the first few columns ?
If the answer to my first question is 'YES', it may work more efficiently for you to code your data as indicated in my post #2. This approach may also clear up your issue with first selection of the combobox.

Hi Logit.
Data in Columns K & L are small data bases, they are the RowSource for the ComboBoxes.
These are expected to never change or can be coded if change required.

Re-Looking at your code, This is hard coding the selections and 'Your code action here item - I presume is something like

Please Login or Register  to view this content.

Now how would I get the imported data to show in the ComboBoxes?


Not sure why there is any permissions required, std xlsm file.

Very nice Logit, but I still need to save the results to somewhere else (for job 1 & 2).
Then if changes are made I need to import them back so they are viewed in the user form boxes where I can 'say' change Rafters to Trusses then save it to where it was imported from.
I usually use Sheet1 as my data holder.

I do appreciate what you are showing me and think I should follow your advice in hard coding possible selections for ComboBoxes in the Future.

This section of code

Please Login or Register  to view this content.

specifies that your selections would be 'pasted' tp whatever sheet is showing at the time. In the PROPERTIES section for the UserForm, the selection 'ShowModal' is set to FALSE. Meaning, the Form can be active(showing) and allow you to do other things in the program (say have two or three more forms showing all at the same time, so long as each has the ShowModal setting set to FALSE.

If the fields on the other sheets were located in same cells as Sheet1, you wouldn't have to make any changes to this Form's code. Or you could use FORMULAS in the cells on SHEET 1 to auto copy the data to the other sheets.

There are alot of options available to you. From what you have said in your last post, it sounds like you have a firm grasp on this.

Thanks for your help Logit.
I will mark this thread as solved since it has answered the original question and you have helped me immensely.
I will work on the Import Button and probably need a second userform for editing the saved 'Jobs'
Still it has been great learning something new and improved , thanks.

Your welcome. Glad I could help ... I learned from this effort as well. If you don't need another form to accomplish your goal, consider having only one.

Here's an example from one of my personal projects. It is used to enter data into a worksheet, search the worksheet and has another 'quick entry' method of entering data that can be turned on or off.
No need to use three forms.

Thank you for the opportunity to assist and for the added reputation. Cheers.

Form.jpg