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

**RXcel** · 10-08-2017, 05:00 AM

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!

**kev_** · 10-08-2017, 05:33 AM

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

**kev_** · 10-08-2017, 05:54 AM

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

**RXcel** · 10-08-2017, 09:38 PM

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

**RXcel** · 10-08-2017, 10:27 PM

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!

**kev_** · 10-09-2017, 01:03 AM

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

**kev_** · 10-09-2017, 02:41 AM

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")

**RXcel** · 10-11-2017, 02:36 AM

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!!

**kev_** · 10-11-2017, 05:55 AM

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

**RXcel** · 10-12-2017, 11:09 PM

Thanks again Kev_ for everything you provided!

**Macpic** · 10-13-2017, 07:18 AM

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.

**kev_** · 10-13-2017, 08:07 AM

@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.

**Macpic** · 10-13-2017, 08:14 AM

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.

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

VBA: Wrapping text and transversing text between nubers or given speific text

Preventing ComboBox' list to detach when scrolling

Excel bug. A file I have keeps preventing me from scrolling it.

Vba code help pulling data from one sheet and generate results to another sheet

Dear Users, please send me the code for scrolling text in status bar.

Word wrapping and text formating, data from multiple cells

Preventing Info From Scrolling ... Possible ??

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions