Single Click Combo Validation with Updating Source List

[BIGMOOSE88]

Hey Guys & Gals I have a bit of a challenge here and was wondering If you could help me...

I have looked all over the internet for a solution to my problem but found half the solution in each workbook (funny ain't it?)

I am trying to have a drop down validation so when I click it once, appears a combo box with an autocomplete feature, at the same time if whatever I am entering inside the cell doesnt exist in the source list I would like it to be added as well as an update to the list. But I need the source lists and data to be in vba and not in the sheet for security reasons.

Like I said before its a bit of a challenge for me to get both these features in one workbook but here they are below:

http://www.contextures.com/DataValListAddSort_Multi.zip

http://www.contextures.com/DataValComboClickDepend.zip

Thanks so much for your time!!

 

[xenou]

I was under the impression that combo boxes have a "not in list" event of some kind, which fires when you type something in that isn't already in the list. But I may be confusing Excel ActiveX comboboxes with Access combo boxes. In either case, you'd be using this event or your own handwritten code to compare the new values to existing values, and then save new ones or add them to the combobox value list. It's only a question of where the data source for the combobox is kept - typically on a sheet is good in Excel, or in a table in Access. That is, there needs to be a persistent storage somewhere if you are to permanently add those values and have them show up in future sessions. However, I'd caution that Excel is not really secure - a malicious user can break into your hidden worksheets, protected workbooks, and code. Moreover, I'm not sure I see the point. If the data is visible in the combobox, then it isn't a a secret, is it?

ξ

 

[BIGMOOSE88]

I was under the impression that combo boxes have a "not in list" event of some kind, which fires when you type something in that isn't already in the list. But I may be confusing Excel ActiveX comboboxes with Access combo boxes. In either case, you'd be using this event or your own handwritten code to compare the new values to existing values, and then save new ones or add them to the combobox value list. It's only a question of where the data source for the combobox is kept - typically on a sheet is good in Excel, or in a table in Access. That is, there needs to be a persistent storage somewhere if you are to permanently add those values and have them show up in future sessions. However, I'd caution that Excel is not really secure - a malicious user can break into your hidden worksheets, protected workbooks, and code. Moreover, I'm not sure I see the point. If the data is visible in the combobox, then it isn't a a secret, is it?

ξ

Your absolutely right I didnt even realize that, the addition of data to the sourcelist is the reason its "updated" to the combo box validation. The Data isnt a secret but its one of those situations where I wanted a clean workbook for the enduser but I guess I could just do my best to hide the sheets. Would you have any idea though on how to combine these two features in one workbook because I have tried and failed at it several times

 

[xenou]

Can you confirm the following:

• Version of XL being used (xl2003, xl2007, xl2010, etc.)
• Type of combobox control being used (i.e, is it from the "ActiveX" group/toolbar or the "Forms" group/toolbar)
• How do you populate the combobox (at startup) - from a data range on a sheet, or via vba code, or other method?

ξ

 

[BIGMOOSE88]

Can you confirm the following:

• Version of XL being used (xl2003, xl2007, xl2010, etc.)
• Type of combobox control being used (i.e, is it from the "ActiveX" group/toolbar or the "Forms" group/toolbar)
• How do you populate the combobox (at startup) - from a data range on a sheet, or via vba code, or other method?

ξ

Im using excel 2003 as for which type of form im using im not sure thats why I posted up the workbooks figured thatll be easier, the data is within the range on the sheet

 

[xenou]

The two workbook examples in your links do not appear compatible in their design, at least not at first glance. The one has an ActiveX combo box control that is used for displaying and selecting information in a combo box control. The other uses a validation list for that purpose. I think you should decide whether you want your user to see an ActiveX combo box or a validation list. Once you've made your choice, you can go from there and edit the appropriate example for your requirements. Both implementations can capture new entries and add them to the list. As far as hiding secret information I'm not sure I can help with that - as I said before, vba code or hidden worksheets can both be used to hide data from users, but these aren't really very secure. It still might make for a cleaner appearance with the extra tabs out of the way.

I haven't spent too much time looking at this so that's just my quick take on this.

HTH, ξ