Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Auto display from cell validation

0

Hello,

Updated the question. And thanks Variatus .

I have a data validated cell on G2 and when you type I would like to see the names suggestion when I type in that cell.

Hope someone can helpme out

I added my workbook for you guys.

The sheet is Testmain2

Thank you

Answer
Discuss

Discussion

Neither of your questions are "ready to go". You left room to ask back details for either one but there is no room in this forum's format to do so except in the Discussion. So, we'll end up with a thread nobody can read except you which isn't the purpose of any forum, including this one.
I suggest you focus your question on one topic. Nice page design, BTW :-)
Variatus (rep: 4879) Nov 27, '20 at 7:36 pm
Hello Variatus,

Sorry, didn't known that.
So updated my question hope its better now :)
GhostofWanted (rep: 42) Nov 28, '20 at 2:16 am
Add to Discussion

Answers

0
Selected Answer

I think your problem relates to the named range used in the data validation formula on cell G2 of TESTMAIN2 (where the drop down arrow shows blank cells; though if you scroll up, you will reach the few names currently in the CustomerList sheet BB:BB).

It looks like you've tried to make a dynamicallly sized Named range "EmployeeName" using the formula:

=OFFSET(CustomersList!$BB$3,1,,COUNTA(CustomersList!$BB$4:$BB$997)-1,1)

Trouble is you have employee data in BB4:BB16 at present but you have a formula in many cells below that so the COUNTA part returns 312 (so the range is way beyond the real data range and the drop down list for G2 shows blanks from row 311 therefore you don't see the names in B4:B16).

If you change your formula for EmployeeName (in Formulas/NameManager) to this:

=OFFSET(CustomersList!$BB$3,1,,COUNTA(CustomersList!$BB$4:$BB$20)-1,1)

then you'll always see the end of the current employee list in the drop-down of G2.

While this is okay if you only ever have a short list, it probably isn't a viable solution however if you plan to add lots more employees (not sure why they're in a sheet called CustomerList though!).

I suggest instead you delete the formula from all cells in column BB and instead populate those as cells are completed in columns A:C (that way you can state a large range -see below- but the COUNTA will return only the number of rows with employee data). You can do this using the WorksheetChange event (see Don's tutorial Run a Macro When a Specific Cell Changes in Excel for the principle).

In the attached version of your spreadsheet I've:

1) Added this Worksheet event macro to Sheet11 (CustomerList) with comments for your benefit:

Private Sub Worksheet_Change(ByVal Target As Range)
'see if the change was in columns A to C
If Not Intersect(Target, Range("A:C")) Is Nothing Then
    n = Target.row ' get the row and use that to...
    Cells(n, 54) = Cells(n, 2) & ", " & Cells(n, 3) 'populate BB (column 54) from whatever is in B:C so far
End If

End Sub

2) Deleted the formula from BB:BB and changed the EmployeeName range to refer to the whole the column (knowing COUNTA will work now):

=OFFSET(CustomersList!$BB$3,1,,COUNTA(CustomersList!BB:BB)-1,1)
(instead of B4:B997).

Now, if you add entries in CustomerList columns A:C, they will appear in the dropdown of G2 on TESTMAIN2 (I've added a couple which you'll need to delete).

Hope this helps. If so, don't forget to Select this answer. If not, others may suggest better ways.

Revision 1: I've updated the file to include (on the CustomerList sheet) a delayed screen garp of what happens if I open the file (with no suggested links) then click on the down arrow in G2 of  TESTMAIN2. Nothing else has changed (and it works for me, suggests employee names withoput any scrolling).

Discuss

Discussion

Just realised this approach might not work since you have Add and Delete buttons on TESTMAIN4 (but the links to those macros aren't available to us via the workbook you sent). 
There's a chance (if events aren't disabled) that your Add macro will trigger the new Change event macro to populate BB:BB. If not,  I hope you can use the revised range for CustomerID with modifications to your Add and Delete macros to populate BB:BB on CustomerList.
John_Ru (rep: 4312) Nov 28, '20 at 7:11 am
Hello John_Ru
Yes i haven't add any marco's to those buttons yet.
Because i like to make this problem solved first and some other things
Before i going to try that.
GhostofWanted (rep: 42) Nov 28, '20 at 12:00 pm
Hello John_Ru;

Thank you for the updated code.
But I notice that when I type in cell G2, no suggestions for the existing names appear?
GhostofWanted (rep: 42) Nov 28, '20 at 12:06 pm
Did you try in my version of the workbook?
John_Ru (rep: 4312) Nov 28, '20 at 12:39 pm
Yes i did.
It's like when you use a combobox and when you type you get some previews that matches.
But didn't get that in G2
GhostofWanted (rep: 42) Nov 28, '20 at 12:58 pm
It works for me without typing.

We can't post images in a discussion so I've added one to the file- see Revision 1 of my Answer and the new file.
John_Ru (rep: 4312) Nov 28, '20 at 1:15 pm
Thank for the image,
Yes i get that also
But I meant if I eg type john in G2 no match will show up if when I type in G2 immediately shows which name matches.
GhostofWanted (rep: 42) Nov 28, '20 at 1:25 pm
Not sure I follow whet you said just but even with simple (list based) data validation typing, you can pick from a list (from the down arrow) typing a name or part of it doesn't pick the item from the list. The purpose of  data validation is to limit what can go in given cells. 
John_Ru (rep: 4312) Nov 28, '20 at 1:38 pm
Okay i see.
Thanks for the updated code.
Always a pleasure to learn more what we can do with excel

Thanks again John_Ru
GhostofWanted (rep: 42) Nov 28, '20 at 1:42 pm
No problem. Kindly Select my answer if you're okay now.
John_Ru (rep: 4312) Nov 28, '20 at 2:20 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login