Hi Again,
I've created a form with a combobox on it. I've named a range using the code below. My problem is that when data is added it doesn't show in the combobox until I save and close the WB. Upon reopening the WB, the drop down contains all the original data and the added data.
This is the code I used to create the named range:
Please Login or Register to view this content.
What command am I missing or is this normal behavior for a dynamic range drop down list?
TIA, Don
Last edited by DonG; 10-30-2011 at 03:25 PM.
try changing your name range referes to
Please Login or Register to view this content.
You will need to reinitialize that control on your form again. When you open the form the first time it initializes automatically because it hasn't been initialized yet, but after that you have to force it.
Typically, the code to open the form is simple, like:
Please Login or Register to view this content.
So, take the code from your UserForm_Initialize() sub that fills in that control and add it to the OpenForm sub before your "show" it.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
What kind of form are you referring to?
Hope that helps.
RoyUK
--------
For Excel Tips & Solutions, free examples and tutorials why not check out my web site
Free DataBaseForm example
If you are talking about a ComboBox on a sheet then, use a Form Control not an ActiveX control
The ActiveX ComboBox control on a worksheet is nothing but trouble, and will behave much as you describe. Avoid it.
The linked cell with a Form Control ComboBox will return the index of your selection you will have to refer to this to return the actual value.
e.g.
On a user form there should be no problemPlease Login or Register to view this content.
Name:= "Dynamic_List"
Refers to:=
Please Login or Register to view this content.
Form Control
Control > Input range:=
Cell Link:= $B$6Please Login or Register to view this content.
On User Form
ComboBox1
Row source:=
Please Login or Register to view this content.
Hope this helps.
If you need any more information, please feel free to ask.
However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....
Also
اس کی مدد کرتا ہے اگر
شکریہ کہنے کے لئے سٹار کلک کریں
If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.
Marcol,Originally Posted by Marcol
The ComboBox is on a Form not on a Sheet. The Form is shown via a Command button on a Sheet.
To the others,
I've played with this most all night and am just not getting anywhere.
I'm still not having success with this....
The data entry form is opened from a CommandButton on Sheet1:
Please Login or Register to view this content.
The form contains several TextBoxes, two ComboBoxes and two CommandButtons (one to reset values to "" , does not save data and hides the form)(one saves data and saves WB and hides form). For now I'll just deal with ComboBox1 and CommandButton1. That should show the way for the other commands. The form contains the following subs:
Please Login or Register to view this content.
I have CommandButton1 checking the data and loading it appropriately, saving and hiding the form...this works well. However when new data is added to the Range("Supply") that data will not show when the form is opened again. I have found that if the form is opened again and closed using the "X" up in the upper right corner, the Combobox repopulates itself.
Got me baffled, but that doesn't take much for an old guy....lol
TIA....Don
The answer is still the same way back from post #3.
"take the code from your UserForm_Initialize() sub that fills in that control"
Please Login or Register to view this content.
".... and add it to the OpenForm sub before you "show" it."
Please Login or Register to view this content.
Since this is literally exactly what I suggested, did you even try it?
Last edited by JBeaucaire; 10-30-2011 at 09:46 AM.
JB,
Yes, I did try what you posted, several times and got a compile error each time. "Compile error: Method or data member not found"
Here, I've attached a WB with a very simple example of what I'm trying to do.
Sheet1 has a CommandButton1 that, when clicked Shows Form1. Col A (with a header) on Sheet1 I used just for testing.
Sheet2 ColB is used for the data in a dynamic range ("List")
Named "List" (using dynamic VBA code posted above)
Form1 has one Combox and one CommandButton
Clicking CommandButton on Sheet1, Form1 opens. You can choose the data in the dropdown list or add new data.
If you choose from the dropdown and click the CommandButton on Form1 the data will be displayed in Col A on Sheet1 and nothing will be added to Sheet2. Form1 will be hidden and the WB will be saved.
If you add data not on the list and click the CommandButton on Form1, that data will also be displayed in Col A Sheet1 as well as Col B Sheet2. Form1 will be hidden and the WB will be saved.
If you chose to add your own data and click the CommandButton on Sheet1 again (before closing the WB), the newly added data does not appear in the dropdown. However, if you close Form1 by clicking the "X" in the upper right hand corner, the dropdown will display all previously added data. Of course clicking the "X" does not store any data you may have on the form.
Very sorry for not understanding what you've posted but maybe some day the light will be brighter....Thanks for you time on this simple procedure..
Don
Hello Don,
I created a dynamic named range called "List" on "Sheet2" and added the event procedure below to the UserForm. Now the drop down updates when the dynamic range changes.
Please Login or Register to view this content.
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
Leith,
Perfect....worked like a charm. Thank you so much, and the others for their interest in helping...
Have a great day,
Don
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks