+ Reply to Thread
Results 1 to 19 of 19

Un-Hiding and Hiding Columns based on Drop-Down Values in a Column.

  1. #1
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Un-Hiding and Hiding Columns based on Drop-Down Values in a Column.

    Un-Hiding and Hiding Columns based on Drop-Down Values in a Column.

    Dear Forum,

    I have a requirement to Hide Certain Bunch of Columns based on the value in the cell which decides the fate of all these columns..

    I have a Column in which I have a drop-down where there are 4 Elements to store the 4 most used Modes of Payments such as below:
    Cheque
    Cash
    RTGS/NEFT
    Credit Card

    Just want to Un-Hide the Relevant Column on selection of the Drop-Down and Hiding the other columns and again Hiding the column when the entire data entry for that mode is done..

    Warm Regards
    e4excel
    Last edited by e4excel; 10-11-2011 at 06:52 AM.

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Un-Hiding and Hiding Columns based on Drop-Down Values in a Column.

    Is it possible to attach a spreadsheet? Also mention which columns need to be hidden for each element.

  3. #3
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Un-Hiding and Hiding Columns based on Drop-Down Values in a Column.

    Actually due to SLOW internet speed 2 posts got created unbeknownst to me and the duplicate one has more info and also the attachment..

    Will just post it again in this thread..

    Sincere Apologies for any inconvenience..

    Regards
    e4excel

  4. #4
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Un-Hiding and Hiding Columns based on Drop-Down Values in a Column.

    PFA which explains the requirement in detail
    Attached Files Attached Files

  5. #5
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Un-Hiding and Hiding Columns based on Drop-Down Values in a Column.

    One question: What if 1 row has cheque selected and the other has NEFT? It will be confusing to know which columns to hide.

  6. #6
    Registered User
    Join Date
    10-11-2011
    Location
    Portsmouth
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Un-Hiding and Hiding Columns based on Drop-Down Values in a Column.

    You would need to create an Worksheet_change even on the desired sheet (I.E not a seperate module). This should do what you require

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Un-Hiding and Hiding Columns based on Drop-Down Values in a Column.

    Dear Arlu,

    I am making this Event to Minimize the Erorrs occuring during Data Entry as there are Numerous Columns fro Modes of Payments however each Row is a Different Record and therefore needs to be treated seperately so the Even tchange should be Target.Row and not just Column Based as I dont care even if the Columns for the earlier record are Hidden as my job is to ensure the safe passage for gathering the information by prevent ting the users from making mistakes due to extra Columns..

    SO , we let him make the selection in the Column T and based on that we Reveal only those Columns and again HIDE the Entire COLUMNS after the last information is filled for that column..

    Please refer the attachment to get a clear picture..

    Zhorian, thanks for the code but I dont know how can ise that ? it has not variable rows and also not mention for the different Drop-Down Options..

    Warm regards
    e4excel

  8. #8
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Bump NO Response

    Bump No Response

  9. #9
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Un-Hiding and Hiding Columns based on Drop-Down Values in a Column.

    Dear Forum,

    I think I got the partial solution myself but still need to be absolutely sure of its usability..

    The other thing which I still need is to HIDE the Columns after finishing Data Entry in the appropriate columns like in the Columns of the 4 Payments which happen to be the last column of the respective mode..The Entire column needs to be hidden after the Enter KEY is pressed..

    Please find the code which I tried for HIDING/UN-HIDING COLUMNS...

    Please Login or Register  to view this content.
    I have just tried this but it works but if needs any improvisation please feel free to add as I am not good in VBA to really get it...

    And importantly, please advise on HIDING the Specific Columns which are opened on the
    Change event after the information is entered..in the Columns such as
    Column X ------Cheque Payment
    Column Z ------Cash Payment
    Column AD ------RTGS/NEFT Payment
    Column AH ------Credit Card Payment

    On entering the Information in these Columns I want the Complete Range of Columns to be HIDDEN..

    Warm regards
    e4excel

  10. #10
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Bump NO Response

    Bump NO Response

  11. #11
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Un-Hiding and Hiding Columns based on Drop-Down Values in a Column.

    Your code looks fine. However, if you want to reduce the number of lines, you can combine the column alphabets where the function is true for both. For e.g.
    Please Login or Register  to view this content.
    can be written as
    Please Login or Register  to view this content.
    I too am trying a way to find on how to hide all columns after entry. Will let you know in sometime.
    Last edited by arlu1201; 10-29-2011 at 01:23 PM.

  12. #12
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Un-Hiding and Hiding Columns based on Drop-Down Values in a Column.

    Dear Arlu,

    I too am trying a way to find on how to hide all columns after entry. Will let you know in sometime.
    Thanks for the assurance..

    Good observation Arlu about reducing the no of lines in the code but I have done it intentionally to know which Columns are containing what type of information such as :
    Cheque, Cash, RTGS/NEFT or Credit Card and nothing else..

    I dont know how to use the Lost Focus Event for a Cell on finishing entering data in it..
    Dont even know whether it exists or not in the first place for a cell.

    Warm Regards
    e4excel

  13. #13
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Un-Hiding and Hiding Columns based on Drop-Down Values in a Column.

    I was checking online and Lost Focus Event is for controls on a form. I dont think it applies to a cell.

  14. #14
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Un-Hiding and Hiding Columns based on Drop-Down Values in a Column.

    That's correct I am aware of that but thought maybe ther would be some leftover trick left in the bag..

    This is very helpful if possible as for every new row or payment entry all the options stay hidden and only open on selecting either of the options.

    Warm regards
    e4excel..


    Please let me know if its possible or else I can always forget that

    Thanks in advance

  15. #15
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Un-Hiding and Hiding Columns based on Drop-Down Values in a Column.

    The only complication arises because we have to first have the columns unhidden, then there will be time-lag to update the records and then again the event needs to be triggered. Also, its not just one entry but 3-4 cells need to be filled, so the key presses will be many.

    Maybe you can post that as a separate question asking if you can have an event triggered once all the cells have been filled up.

  16. #16
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Un-Hiding and Hiding Columns based on Drop-Down Values in a Column.

    Dear Arlu,

    I dont mind starting a New thread however a lot has already been explained in this one and this requirement was and is a part of the Main Requirement itself as it concerns HIDING and UNHIDING..

    The only complication arises because we have to first have the columns unhidden, then there will be time-lag to update the records and then again the event needs to be triggered. Also, its not just one entry but 3-4 cells need to be filled, so the key presses will be many.
    The Columns would be HIdden whenever one chooses the Option in the Drop-Down so that will not be deterrent.

    For Example : When one chooses the Cheque Option from the Beginning All other Columns would be Hidden and the Columns Under the Cheque Mode would be Un-Hidden..

    Now I believe the Got Focus should or the cursor should go to the First Column for the Cheque Option pertaining to this example and all the information would be entered and then once the las column-cell is filled and Enter is pressed then the Cheque Columns should again get Hidden..

    If you still insist I can start a New thread but everything would be needed to explained which is done in this query..

    Thanks in advance.

    Warm Regards
    e4excel

  17. #17
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Un-Hiding and Hiding Columns based on Drop-Down Values in a Column.

    The reason i said to start a separate thread is because more people tend to reply to those kind of threads. Threads which already have 10+ posts get fewer replies .

    I understand your logic completely. But we should find a way to get the macro to know when the user has finished entry on the last cell in that category, so that it can hide those columns.

  18. #18
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Un-Hiding and Hiding Columns based on Drop-Down Values in a Column.

    Quote Originally Posted by arlu1201 View Post
    The reason i said to start a separate thread is because more people tend to reply to those kind of threads. Threads which already have 10+ posts get fewer replies .

    I understand your logic completely. But we should find a way to get the macro to know when the user has finished entry on the last cell in that category, so that it can hide those columns.
    Thanks Arlu, for mentioning that, I appreciate your good intentions..

    Anyways, lets wait and see if it works or else I will do as u said Start a New Thread..

    Warm Regards
    e4excel
    Last edited by e4excel; 10-30-2011 at 08:55 AM.

  19. #19
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Un-Hiding and Hiding Columns based on Drop-Down Values in a Column.

    Hey np at all. You can open the new thread only for the unsolved part and title it differently.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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