+ Reply to Thread
Results 1 to 25 of 25

Select Cell though drop down list

  1. #1
    Registered User
    Join Date
    02-11-2022
    Location
    Athens
    MS-Off Ver
    MS Office 2019
    Posts
    13

    Question Select Cell though drop down list

    Hello there Excel experts !

    I have the following request:

    I want the user to choose the value that he wants thought a drop down list and then the code (on the same sheet) will find the value and autoscroll and select it !

    I have a drop down list that is being populated from a different sheet.
    Lets say that this list is on cell E2 on the actual sheet.

    Thanks in advance,
    John

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Select Cell though drop down list

    Please see the yellow banner at top of the page on how to post a sample work with clear explanation of what is required, together with example results.
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  3. #3
    Registered User
    Join Date
    02-11-2022
    Location
    Athens
    MS-Off Ver
    MS Office 2019
    Posts
    13

    Re: Select Cell though drop down list

    Here is a small Demo of the file....

    What I need: After choosing the value that you want from the dropdown list on E2 the sheet should autoselect and autoscroll to
    the cell with the same value at column I (I5:I229)

    Thanks !
    Attached Files Attached Files

  4. #4
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Select Cell though drop down list

    Macro/VBA in sheet module

    Please Login or Register  to view this content.
    Torachan,

    Mission statement; Promote the use of Tables, Outlaw the use of 'merged cells' and 'RowSource'.

  5. #5
    Registered User
    Join Date
    02-11-2022
    Location
    Athens
    MS-Off Ver
    MS Office 2019
    Posts
    13

    Re: Select Cell though drop down list

    This works perfectly thanks my friend !!

    I do have some other maybe more complicated requests for this sheet...(dont know if I have to open a new thread for these)

    a) how can i get a pop up box form that asks the user if he wants to enter an X on column H next to the selected cell of column I ?
    b) if X is entered can then the value (column I cells) be removed from the drop down list of cell E2?

    Thanks again really appreciate it !,
    John
    Last edited by AliGW; 02-12-2022 at 05:23 AM. Reason: PLEASE don't quote unnecessarily!

  6. #6
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Select Cell though drop down list

    attached file updated with requests a) & b).
    note: one line code in WorkBook module refreshes the second table on opening.
    the second weapons table is used to populate and update the data validation selection list.
    note: I have used 'structured tables' instead of range lists (tables are self maintaining (they expand/contract dynamically as you add/delete), you do not have to manually resize the lists, Excel does this for you.
    Please note @Ali's edit, post #5, do not reply with quote unless absolutely necessary as it clutters the thread.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    02-11-2022
    Location
    Athens
    MS-Off Ver
    MS Office 2019
    Posts
    13

    Re: Select Cell though drop down list

    Sorry for the mistake with the quoted answer !

    This seems to work again and thanks for it once more but if you find the time can you amend it as follows?

    a) After the code enters the x can you stay at the selected cell and not move upwards at the beginning of the sheet ?

    b) If the user makes a mistake and enters an x, the cell value disappears from the drop down list table as I asked, but if i delete the x it it does not reappear inside it

    Questions, since I will make more list tables I need to understand how these work:
    a) do i just make them table style by naming the ranges with "tbl_" at the beginning ?
    b) i see at your workbook code that you are using two weapon lists so I suppose I should always make two of them ?
    c) did you name the new second table both as tbl_weapons2 and WEAPONS ? cause I see at the drop down list's data validation source is taking its' values from the name WEAPONS

    Thanks again !

  8. #8
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Select Cell though drop down list

    read the articles in the attached link - to understand structured tables and linking with the 'name manager.
    a) done - sheet now stay at related row b) double click the 'X' it will disappear and put the item back in the table/list

    a) see link
    b) you will only need two tables if you adopt the same method of loading and deleting.
    c) see 'name manager' to learn how the tables are linked to the data validation.
    It is difficult to give comprehensive advice when only seeing a 'snippet' from your application - it would appear this is far larger than what has been seen.
    I have left most of the code written in 'long-hand' blocks so that is readable - when understood and running O.K. there are numerous ways of condensing the code to run faster.

    Attached Files Attached Files

  9. #9
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Select Cell though drop down list

    glad to have helped - thanks for the feedback and added rep.

  10. #10
    Registered User
    Join Date
    02-11-2022
    Location
    Athens
    MS-Off Ver
    MS Office 2019
    Posts
    13

    Talking Re: Select Cell though drop down list

    Thanks for the effort once more ! This is great !

    Since I am going to share this with the Diablo 2 Game Community anyway (I will let them know about your help too !) I am sending you the whole sheet (which i finished all data validations - formatting today) to help me if you find the time. This now does not have all of your codes with the tables, drop down list, adjustent cell etc...

    Notes:

    a) code with x on adjustent cell does not work if user enters X manually ! only if you use it through the drop down list ! can this work manually too ?
    b) can the user form appear a little on the left not on top of column I ?
    c) I noticed that if user accidentally presses for the second time the X mark to delete it then the item reappers for a second, third time on the list !

    I think that if these are corrected its ready !

    Thanks !
    John
    Attached Files Attached Files

  11. #11
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Select Cell though drop down list

    if i correct/modify a), b), c) are you then O.K. to code the app for the other three sheets ?

    I have just tried to replicate the duplication caused by pressing the 'X' more than once, it does not do it for me, describe in more detail how and when this happens ????
    Last edited by torachan; 02-13-2022 at 11:09 AM.

  12. #12
    Registered User
    Join Date
    02-11-2022
    Location
    Athens
    MS-Off Ver
    MS Office 2019
    Posts
    13

    Re: Select Cell though drop down list

    yes i will try to replicate to the other sheets I hope I wont mess it up ! in case i do will ask you if not problem....

    let's say you choose the item that you need from the drop down list, form appears, it puts an X. FINE BY NOW..
    User deletes the X by double pressing the X. X is being deleted and item reappears on list. FINE BY NOW..
    Now If user accidentally double clicks on the empty "X cell" again then the item appears once more on the list !
    Is it clear now ?

  13. #13
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Select Cell though drop down list

    a) done.
    b) the standard message box position is fixed - I have used a UserForm - it now follows the selected row during the page scroll.
    c) fixed
    When you look on the 'Listings' sheet you will notice the second table is hidden (by setting its width to zero) so that any manual additions/deletions are done to the correct table.
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    02-11-2022
    Location
    Athens
    MS-Off Ver
    MS Office 2019
    Posts
    13

    Re: Select Cell though drop down list

    I have transferred all of your excellent codes (Module 1-Weapons code [here i changed the sheet name and some ranges]-ThisWorkbook [here i changed the sheet name and some ranges] -Userform to the original workbook but i cannot get it to work....Dropdown list does not work on Weapons sheet...

    Can you please tell me why ?

    Also if you have the time can you copy the code for just one more list-sheet for the Armor sheet for example and then I will be able to make all others I think !

    Here is the sheet..

    Thanks again and sorry for bothering you constantly....
    Attached Files Attached Files

  15. #15
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Select Cell though drop down list

    Torachan,

    Mission statement; Promote the use of Tables, Outlaw the use of 'merged cells' and 'RowSource'.
    I see my footer is only attached at post #4, you are getting intermittent errors caused by the merged columns 'H' & 'I' splitting the column 'H' which needs to be continuous for the worksheet event to work without errors.
    The use of data validation, conditional formatting,formula and VBA all jumbled together is likely to present problems the larger the app becomes.
    I have replaced one of your data validations with one line of code that forces the 'X' to upper case if lower case is entered.
    In post #8 I have given you a link to enable you to brush up on 'structured tables' - your second listings are merely range listings not tables.
    See top menu 'Insert' select 'Table' with the entire list highlighted, then select 'Has Headers' after this look in the 'Name Manager' to see structure.
    The reason nothing seemed to work was you have changed sheet names but not altered all the references in the code.
    If building from 'scratch' I would be using a single UserForm with a 'MultiPage' embedded, Excel sheets are poor graphic interfaces.
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    02-11-2022
    Location
    Athens
    MS-Off Ver
    MS Office 2019
    Posts
    13

    Re: Select Cell though drop down list

    I think I have managed to fix everything on the larger sheet!

    Three things remain and hopefully am not going to bother you anymore :

    a) can you amend the code a little bit so that after the Userform appears, the user chooses yes, the code enters x on cell at column H, then ---> the active cell becomes two cells on the right, on column J (Mules column)?

    b) if in the future someone enters a new item on the lists tab, lets say the tbl_WEAPONS one, the very first one, can we make that to be sorted automatically? I suppose tbl_WEAPON2 the hidden one should be sorted automatically too ?
    (here for the Lists sheet to be updated the workbook has to close first and then reopen, maybe this should change to sheet change onto the lists tab code ? -
    i have tried this but I think but the code runs for too long ?)

    c) What is the part of the code I can amend so that I can move the userform a little on the left if I need or wherever I need it to go ?
    Attached Files Attached Files

  17. #17
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Select Cell though drop down list

    sorry late reply, regrettably the rigors of life have taken over the foreseeable future, so posting will be intermittent.
    I have annotate pieces of code in the attached file, hopefully these will give you sufficient to enable you build on.
    See UserForm1, Module2 - Sub Cover() this deals with b) (no need to sort the hidden file), see code in sheet2 module and alter other sheets similarly.
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    02-11-2022
    Location
    Athens
    MS-Off Ver
    MS Office 2019
    Posts
    13

    Re: Select Cell though drop down list

    These all great thank you so much !
    Dont worry about late replies ! 3 kids here so I definitely understand !

    I have two more requests could not resist:

    a) I have this zoom code for the validation drop down list on cell E2. How can this be fixed so that it only works for this E2 cell and not anyother dropdown list ?

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'updateby Extendoffice
    On Error GoTo LZoom
    Dim xZoom As Long
    xZoom = 100
    If Target.Validation.Type = xlValidateList Then xZoom = 140
    LZoom:
    ActiveWindow.Zoom = xZoom
    End Sub


    b) can you write a code so that excel opens automatically a hyperlink with the default browser after your drop down list code chooses the item-active cell on column I ?
    I am going to put hyperlinks on every item on column(s) I.

    Here is the final version. There is an example hyperlink on I5 on weapons tab !

    Thanks again !
    Attached Files Attached Files

  19. #19
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Select Cell though drop down list

    i will look at the rest later.

    Please Login or Register  to view this content.

  20. #20
    Registered User
    Join Date
    02-11-2022
    Location
    Athens
    MS-Off Ver
    MS Office 2019
    Posts
    13

    Re: Select Cell though drop down list

    For some weird reason the zoom code only works on one of the sheets... ???

  21. #21
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Select Cell though drop down list

    try this in each respective sheet module.

    Please Login or Register  to view this content.

  22. #22
    Registered User
    Join Date
    02-11-2022
    Location
    Athens
    MS-Off Ver
    MS Office 2019
    Posts
    13

    Re: Select Cell though drop down list

    Yes that worked fine once more ! You are ace !

    Reminding you the (b) part of the hyperlink code for whenever you ll find the time !
    Last edited by JohnKanidis; 03-05-2022 at 10:22 AM.

  23. #23
    Registered User
    Join Date
    02-11-2022
    Location
    Athens
    MS-Off Ver
    MS Office 2019
    Posts
    13

    Re: Select Cell though drop down list

    Kind Reminder !

  24. #24
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Select Cell though drop down list

    maybe someone else will pick this up and help you.
    sorry but present meds interfere with my concentration span.

  25. #25
    Registered User
    Join Date
    02-11-2022
    Location
    Athens
    MS-Off Ver
    MS Office 2019
    Posts
    13

    Re: Select Cell though drop down list

    Νο problem mate ! I wish you all the best !
    whenever you feel well again you can come back !

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 11-17-2018, 03:15 AM
  2. Replies: 5
    Last Post: 04-11-2014, 03:13 PM
  3. Allow to Select Only 1 Cell from Drop List in a Section of a Row.
    By JasChu in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-30-2012, 11:07 AM
  4. [SOLVED] If I select a value from drop-down list, how to display related info in another cell?
    By slovas in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 08-24-2012, 12:29 PM
  5. [SOLVED] Select from drop down list to show another drop down list in a different cel
    By ExcelRanger in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-22-2012, 04:27 PM
  6. Replies: 3
    Last Post: 02-23-2012, 04:51 AM
  7. Replies: 5
    Last Post: 10-27-2005, 01:55 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1