Datavalidation using ComboBox - Autocomplete

[css0911]

Hello to Chandoo.org

I have a project, almost completed. I found a link from here , how to do it. I amable to do it in a sheet. But in my main sheet ,for some reason am not able to do so.Pls help me. I am uploading the project for you. The sheets and VBA is password protected and i want to share the same with the person who wish to help me. Please let me know , how can i share the information ?

I want the above to work in my data entry Macro.

Thanks in advance

Regards
Chaand

 

[NARAYANK991]

Hi Chaand ,

Sorry , but I will not be able to help you out.

What you can do is start a conversation with who ever is willing to help you and give the password to that person in the conversation , so that it does not become public.

Narayan

 

[SirJB7]

Hi, css0911!
How can we get sure that it's a project of your development and not from other sources? Having the passwords isn't proof of nothing. They're easily crackable, Excel isn't bulletproof but Ementhal-ed in fact.
However you didn't mention the link you found here, it'd help to post it and to know your specific requirement.
Regards!
PS: Should we call RIGHT(NickName,3)?

 

[css0911]

Hi Sir

Thanks for your message.

Even i do not know, how to prove the ownership of the project. I can only say, this is developed by me for my own business firm. Yes, as i have only basic idea of VB, i have sought help from many Excel Forums and have collated the codes for my use.

Sorry, i have not shared the link. The link is http://www.contextures.com/xlDataVal11.html

My requirement is, in Data Entry sheet, i want theComboBox Validation for the column;
Party = List - Normal Name Range
P.Type = List - Normal Name Range
T.Type = List - Normal Name Range
Product = List with Formula as below.

=IF(C3="In",CHOOSE(MATCH(D3,In,0),Ply,Block,Adh,Door,Bead,timber,Lam,Others),IF(C3="Out",CHOOSE(MATCH(D3,Out,0),Ply,Block,Adh,Door,Bead,timber,Lam,Others),CHOOSE(MATCH(D3,Financial,0),Entry)))

Help is very much appreciated.

Regards

PS.. Instead of RIGHT, LEFT is more suitable.

 

[SirJB7]

Hi, css0911!

I played a bit with your workbook and I don't see any issue in data entry worksheet "Main". The data validation for columns B:E seems to work as you described in your posted formula and according to your defined names.

Could you elaborate and explicitly indicate what is wrong and how do you require to be?

Regards!

 

[css0911]

Hi Sir

In data Entry, for columns B:E , i want ComboBox Data Validation. So that i can display more rows and auto complete just like "Report" sheet.

Thanks in advance.

 

[SirJB7]

Hi, css0911!

In worksheet Report, there's a combo box over D1:F1 cells for selecting the party name, but column B contains the party name too, and it has data validation against a list which is filled with the same range that the combo. So either column B shouldn't have data validation (even more, it shouldn't be editable) or the combo doesn't have a functionallity that I understands.

Besides if I select any value from the combo and press command button Generator I get columns A:I hidden.

Now you say you want to replicate the worksheet Report combo behavior and validations in worksheet Main, and for columns B:E, so my doubts are:
a) How does it work the combo selection and report generation in worksheet Report? In a report worksheet, the data it's editable?
b) Where does the data for columns B:E in worksheet Main comes from? If you add a combo, isn't only for party name (column B)? And if so, should it be editable? And the other referred columns (C:E) what should be done with them (source, edition, validation)?

I'd suggest you to elaborate and explain with a couple of sample data what is the input data procedure that you want in worksheet Main, which cells should have data validation, against which ranges, how would the requested combo affect the input, etc., i.e., all the detailed procedure that you'd like to have there.

Once done that, please confirm how the combo in worksheet Report is operating, which is the relation between it and column B, etc.

Regards!

PS: In detail isn't at all like this:

In data Entry, for columns B:E , i want ComboBox Data Validation. So that i can display more rows and auto complete just like "Report" sheet.

 

[css0911]

Hi Sir

Will re frame my query.

Sheet "Main" is where the user makes the data entry on daily basis. This is the main source of data.

In "Main" B:E are the data validations, user will pick the item from drop down list.

Column B : Party Name. this name range is defined as "ShortName" and is in sheet "AddParty"

Column C : Simple list with 3 options "In", Out" and "Financial"

Column D : P.Type (Product Type), Name "Main" is in sheet "Names" A1:I1

Column E : Product - Name range is in sheet "Names" , under the respective P.Type head.


Present Scenario.
When user enters values in sheet "Main" while picking from the drop down list, only 8 lines are visible and need to scroll the items to select. These items keep increasing.

What i want.
in sheet "Main", column B:E, instead of normal Data Validation, i want Combobox validation, so that on double clicking the cell with DV, i can show more lines and increase the font size of drop drown List and also auto complete.

Please find the sample file which i have downloaded from the link - http://www.contextures.com/xlDataVal11.html

Same arrangement i am looking for B:E in sheet "Main".

Thanks in advance.

 

[SirJB7]

Hi, css0911!

Now I think that I understand your issue. And I'm good news! You don't need to do anything new to solve your issue.

But, as my old friend b(ut)ob(ut)hc always says there's always a but...t..., I'm bad news too. You don't need to do anything to solve your issue, apart from what you've done (copied!) from contextures website.

The crux resides on the tricky moving, overlapping, hiding and showing a combo box over cells with data validation against drop down lists. It's very simple, interesting and efficient, since it allows you to simulate lots of combo boxes without having to set up more than one.

It works intercepting 3 events for the worksheet (including the combo box control):
- worksheet selection change, where it resizes to zero width and hiding the combo
- worksheet double click, where it relocates the combo over the selected cell and resizes it to fixed values (15 x 5)
- combo box key down (which I'd change to key up to avoid multiple queued events if keeping pressed a key) double click, where it pops up and display the combo instead of the cell data validation list, handling 2 special key presses: Tab, Chr(9), to offset the control 1 cell to the right, and Enter, Chr(13), to offset it 1 cell down.

So you only have to:
a) Include the combo control in your worksheet
b) Include the 3 procedures for handling the events
c) Tweak the worksheet double click event code to work only on desired columns B:E with something like:
If Target.Column < 2 Or Target.Column > 5 Then Exit Sub
as 1st executable statement, i.e. after the Dims.
I guess I'm not missing anything.

Hope it helps.

Regards!

 

[css0911]

Dear Sir

Followed the same procedure as the sample file downloaded from contexture.com

Except for column B , C:E its not working.

Please assist me in understanding the issues as to why the same is not working for column C:E

I have attached the file.

Thanks

 

[SirJB7]

Hi, css0911!
Why don't you upload an unprotected workbook and that doesn't make unwanted changes in Excel options settings (formula bar, tabs, etc.) and that only closes itself and not quits the application (even if it says that it'll only close the file?
It's annoying to open such files and then have to change everything to its previous state or even worse to lose pending not saved works just because of a non explicit behavior.
Regards!

 

[css0911]

Hi Sir

Sorry for that.