+ Reply to Thread
Results 1 to 13 of 13

VBA Code for Wrapping Text for Multiple Results and Preventing a Sheet from Scrolling

  1. #1
    Registered User
    Join Date
    09-13-2017
    Location
    United States
    MS-Off Ver
    MS Office 365
    Posts
    43

    Question VBA Code for Wrapping Text for Multiple Results and Preventing a Sheet from Scrolling

    I have googled multiple sites and seen different code but none of them have worked. I created an excel sheet that if the user inputted a word, then a certain amount of rows will return results that contains that word the user inputted. There are results that exceeds the row height, so it shows it partial cut off. I want to know if there is a vba code that can wrap the text based on the results. I have tried this code but doesn't work:
    Please Login or Register  to view this content.
    Lastly, the other thing I wanted to do was to prevent scrolling in one of my sheets. I tried this code and initially it worked but when I open the workbook again, it stopped working. The code I used was this:
    Please Login or Register  to view this content.
    Thank you for your help!

  2. #2
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: VBA Code for Wrapping Text for Multiple Results and Preventing a Sheet from Scrolling

    Try:

    This goes in ThisWorkbook module
    Please Login or Register  to view this content.
    and this goes in "Finder" sheet module
    Please Login or Register  to view this content.
    use both
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  3. #3
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: VBA Code for Wrapping Text for Multiple Results and Preventing a Sheet from Scrolling

    Look at this page from microsoft - it may explain why you are having problem with row height not being adjusted

  4. #4
    Registered User
    Join Date
    09-13-2017
    Location
    United States
    MS-Off Ver
    MS Office 365
    Posts
    43

    Re: VBA Code for Wrapping Text for Multiple Results and Preventing a Sheet from Scrolling

    Thanks Kev_ this works great!! Can you explain the Me in "Me.Range("A1:M31")"? Thank you

  5. #5
    Registered User
    Join Date
    09-13-2017
    Location
    United States
    MS-Off Ver
    MS Office 365
    Posts
    43

    Re: VBA Code for Wrapping Text for Multiple Results and Preventing a Sheet from Scrolling

    Sorry Kev_. I might not have explained it well enough. Hope these pictures help.
    pic2.PNG

    In this picture, I inputted a keyword "example" and my excel will find all the result in another excel sheet containing that keyword.
    I was hoping to find a code that could help me automatically wrap the text no matter what result shows up in row 4 to row 20. I can do this manually by double clicking the row or going to format > AutoFit Row Height, but it will get tedious repeating this process.

    Thank you!

  6. #6
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: VBA Code for Wrapping Text for Multiple Results and Preventing a Sheet from Scrolling

    Can you explain the Me in "Me.Range("A1:M31")"? Thank you
    VBA interprets "Me" differently depending on where the code is placed - the module determines how VBA decides

    Above, the code is in sheet module of sheet "Finder". So:
    Me = Sheets("Finder")

    Or another example - with UserForm with name "uf_Expenses"
    If the code is in the UserForm module:
    Me = UserForm named "uf_Expenses"

    Etc
    Last edited by kev_; 10-09-2017 at 04:44 PM.

  7. #7
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: VBA Code for Wrapping Text for Multiple Results and Preventing a Sheet from Scrolling

    If your range does not change you should be able to manually set wrap text and auto-fit row heights
    - but this is one of those things that can "go out of whack" from time to time
    - cells that have always autofitted can suddenly mis-behave without obvious clues as to the cause
    - Excel can ignore autofit if row height previously set manually

    If a cell is pasted from elsewhere, it takes the formatting of the donating cell etc - which overwrites previous "rules"

    I try to avoid formatting complete rows (or columns)
    - and format only the range used
    - so Range("A4:C30").WrapText = True (rather than Rows("4:30") ....)
    - dynamic named ranges are useful if range likely to grow

    To reset that range whenever any change occurs in the worksheet - "a sledgehammer to crack a nut"
    try putting this into the sheet module of sheet "Finder"
    Please Login or Register  to view this content.
    If results arrive in those cells via VBA, then as an alternative, include above code instead at end of that procedure (with reference to sheet "Finder")

  8. #8
    Registered User
    Join Date
    09-13-2017
    Location
    United States
    MS-Off Ver
    MS Office 365
    Posts
    43

    Re: VBA Code for Wrapping Text for Multiple Results and Preventing a Sheet from Scrolling

    Kev_, you are the master of excel. Thank you again for providing this awesome code. Also, thank you for your explanation about "Me". If you don't mind me asking, where did you learn how to do this type of vba coding? Thank you so much again!!

  9. #9
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: VBA Code for Wrapping Text for Multiple Results and Preventing a Sheet from Scrolling

    VBA needs to be puzzled it out step by step
    - increase knowledge by always taking things further than actually required to solve the problem in hand

    These days, there's a heap of free help available
    - forums like this, technical websites, youTube videos, newsletters, books etc
    - there is always something new to learn.

    When something fails to work
    - fixing it solves only the immediate crisis
    - property understanding the failure is more valuable in the long term

    Helping others on a forum (like this one) is an excellent way to develop
    - there's an extraordinary range of issues to overcome
    - benefit from other members offering totally different but equally valid aproaches

  10. #10
    Registered User
    Join Date
    09-13-2017
    Location
    United States
    MS-Off Ver
    MS Office 365
    Posts
    43

    Re: VBA Code for Wrapping Text for Multiple Results and Preventing a Sheet from Scrolling

    Thanks again Kev_ for everything you provided!

  11. #11
    Registered User
    Join Date
    10-04-2017
    Location
    France
    MS-Off Ver
    2016
    Posts
    15

    Re: VBA Code for Wrapping Text for Multiple Results and Preventing a Sheet from Scrolling

    Hi everyone,
    I'd like to tag along on this great post. I've used a code found in another post in order to create an item selector based on a number of criteria.
    Each cell which is filled in is supposed to look in another sheet and return a number of possible options as a result.

    My problem is that not all possible results show up in the destination cell, as it seems that the returned value is the 1st one found anf then the search stops. Furthermore, I have the feeling that at some stage the search is not on anymore, as some search fields don't seem to be taken into account (C14:C19)

    I've attached the file and would very much appreciate any help I can get in order to understand what I'm doing wrong.
    Many thanks,

    Private Sub Worksheet_Change(ByVal Target As Range)


    Please Login or Register  to view this content.
    Attached Files Attached Files

  12. #12
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: VBA Code for Wrapping Text for Multiple Results and Preventing a Sheet from Scrolling

    @Macpic Your post appears to have no relevance whatsoever to this thread
    PLEASE START A SEPARATE THREAD WITH A RELEVANT TITLE (think google search)

    Extract from forum rules
    2. Don't post a question in the thread of another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread. You may include up to 3 links to other URLs in a single post, no more, so only link to the relevant pages.

  13. #13
    Registered User
    Join Date
    10-04-2017
    Location
    France
    MS-Off Ver
    2016
    Posts
    15

    Re: VBA Code for Wrapping Text for Multiple Results and Preventing a Sheet from Scrolling

    Understood Kev. Apologies, although it does have relevance because the result I'm trying to obtain will need the wrapping feature. Nevertheless, I'll start another thread.

+ 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. VBA: Wrapping text and transversing text between nubers or given speific text
    By sroysroy in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 10-08-2015, 07:52 PM
  2. Preventing ComboBox' list to detach when scrolling
    By AstroZelda in forum Excel General
    Replies: 3
    Last Post: 06-01-2015, 04:42 AM
  3. Replies: 4
    Last Post: 01-20-2014, 10:52 AM
  4. Vba code help pulling data from one sheet and generate results to another sheet
    By OATMAS in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-21-2013, 11:45 AM
  5. Dear Users, please send me the code for scrolling text in status bar.
    By RAKESH MESHRAM in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-09-2012, 04:00 PM
  6. Replies: 1
    Last Post: 11-14-2007, 03:41 PM
  7. Preventing Info From Scrolling ... Possible ??
    By Mhz in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 08-13-2006, 06:50 AM

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