| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
What you see is what I want
I am looking for a UDF that will return the visible text in a cell. For
example, if A1 contains the sentence: "Now is the time for all good men to come to the aid of their party" The end of the sentence will "bleed" over into B1 unless column A is very wide. I want to see the portion of the sentence that actually appears in A1. I have been able to get this with a Sub, but not a Function. -- Gary''s Student - gsnu200905 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What you see is what I want
Gary''s Student;506433 Wrote: I am looking for a UDF that will return the visible text in a cell. For example, if A1 contains the sentence: "Now is the time for all good men to come to the aid of their party" The end of the sentence will "bleed" over into B1 unless column A is very wide. I want to see the portion of the sentence that actually appears in A1. I have been able to get this with a Sub, but not a Function. -- Gary''s Student - gsnu200905 How about posting your code so that we don't have to start from scratch? -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=139234 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What you see is what I want
may we see the code yu did for the sub please?
"Gary''s Student" wrote: I am looking for a UDF that will return the visible text in a cell. For example, if A1 contains the sentence: "Now is the time for all good men to come to the aid of their party" The end of the sentence will "bleed" over into B1 unless column A is very wide. I want to see the portion of the sentence that actually appears in A1. I have been able to get this with a Sub, but not a Function. -- Gary''s Student - gsnu200905 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What you see is what I want
Sub measure()
Dim c As Range, v As String, w As Variant Dim shorter As String v = Range("A1").Value Set c = Columns("A") w = c.Width c.AutoFit w2 = c.Width If w = w2 Then MsgBox (v) Exit Sub End If For i = 1 To Len(v) shorter = Left(v, Len(v) - i) Range("A1").Value = shorter c.AutoFit w2 = c.Width If w2 <= w Then MsgBox (shorter) Range("A1").Value = v Exit Sub End If Next End Sub Basically, the sub does trial AutoFits. If you AutoFit and the column width has not changed, then the entire text fits in the cell. If the column width has changed, I keep chopping characters off the right until the the AutoFit does not expand the column. This approach has at least three problems: 1. A Sub can change the column width, a UDF cannot. 2. AutoFit responds to other cells in column. 3, The approach assumes the text is left justified. Any help will be greatly appreciated. -- Gary''s Student - gsnu200905 "p45cal" wrote: Gary''s Student;506433 Wrote: I am looking for a UDF that will return the visible text in a cell. For example, if A1 contains the sentence: "Now is the time for all good men to come to the aid of their party" The end of the sentence will "bleed" over into B1 unless column A is very wide. I want to see the portion of the sentence that actually appears in A1. I have been able to get this with a Sub, but not a Function. -- Gary''s Student - gsnu200905 How about posting your code so that we don't have to start from scratch? -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=139234 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What you see is what I want
It would be extremely difficult to do that in a UDF. The normal way is to
copy the formatted cell to a cell in an empty column and Autowidth. Another less common but more accurate way is with an AutoSize textbox. Either way means code to change the interface, which of course is not (normally) possible in a UDF. Maybe a Change event would suit your purposes. Regards, Peter T "Gary''s Student" wrote in message ... I am looking for a UDF that will return the visible text in a cell. For example, if A1 contains the sentence: "Now is the time for all good men to come to the aid of their party" The end of the sentence will "bleed" over into B1 unless column A is very wide. I want to see the portion of the sentence that actually appears in A1. I have been able to get this with a Sub, but not a Function. -- Gary''s Student - gsnu200905 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What you see is what I want
I see the issue here - and that is of course that a function can only affect the caller - so the line that autofits the column width failes in a function while of course its fine in a sub The only thing that I could think of was to calculate the width of each letter then compare to the actual cell width. "Gary''s Student" wrote: Sub measure() Dim c As Range, v As String, w As Variant Dim shorter As String v = Range("A1").Value Set c = Columns("A") w = c.Width c.AutoFit w2 = c.Width If w = w2 Then MsgBox (v) Exit Sub End If For i = 1 To Len(v) shorter = Left(v, Len(v) - i) Range("A1").Value = shorter c.AutoFit w2 = c.Width If w2 <= w Then MsgBox (shorter) Range("A1").Value = v Exit Sub End If Next End Sub Basically, the sub does trial AutoFits. If you AutoFit and the column width has not changed, then the entire text fits in the cell. If the column width has changed, I keep chopping characters off the right until the the AutoFit does not expand the column. This approach has at least three problems: 1. A Sub can change the column width, a UDF cannot. 2. AutoFit responds to other cells in column. 3, The approach assumes the text is left justified. Any help will be greatly appreciated. -- Gary''s Student - gsnu200905 "p45cal" wrote: Gary''s Student;506433 Wrote: I am looking for a UDF that will return the visible text in a cell. For example, if A1 contains the sentence: "Now is the time for all good men to come to the aid of their party" The end of the sentence will "bleed" over into B1 unless column A is very wide. I want to see the portion of the sentence that actually appears in A1. I have been able to get this with a Sub, but not a Function. -- Gary''s Student - gsnu200905 How about posting your code so that we don't have to start from scratch? -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=139234 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What you see is what I want
following on, I don't think that there's an excel way to get the font size
out, but I suspect that you could do this with some API call. "Patrick Molloy" wrote: I see the issue here - and that is of course that a function can only affect the caller - so the line that autofits the column width failes in a function while of course its fine in a sub The only thing that I could think of was to calculate the width of each letter then compare to the actual cell width. "Gary''s Student" wrote: Sub measure() Dim c As Range, v As String, w As Variant Dim shorter As String v = Range("A1").Value Set c = Columns("A") w = c.Width c.AutoFit w2 = c.Width If w = w2 Then MsgBox (v) Exit Sub End If For i = 1 To Len(v) shorter = Left(v, Len(v) - i) Range("A1").Value = shorter c.AutoFit w2 = c.Width If w2 <= w Then MsgBox (shorter) Range("A1").Value = v Exit Sub End If Next End Sub Basically, the sub does trial AutoFits. If you AutoFit and the column width has not changed, then the entire text fits in the cell. If the column width has changed, I keep chopping characters off the right until the the AutoFit does not expand the column. This approach has at least three problems: 1. A Sub can change the column width, a UDF cannot. 2. AutoFit responds to other cells in column. 3, The approach assumes the text is left justified. Any help will be greatly appreciated. -- Gary''s Student - gsnu200905 "p45cal" wrote: Gary''s Student;506433 Wrote: I am looking for a UDF that will return the visible text in a cell. For example, if A1 contains the sentence: "Now is the time for all good men to come to the aid of their party" The end of the sentence will "bleed" over into B1 unless column A is very wide. I want to see the portion of the sentence that actually appears in A1. I have been able to get this with a Sub, but not a Function. -- Gary''s Student - gsnu200905 How about posting your code so that we don't have to start from scratch? -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=139234 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What you see is what I want
Thanks for the advise.
-- Gary''s Student - gsnu200905 "Patrick Molloy" wrote: following on, I don't think that there's an excel way to get the font size out, but I suspect that you could do this with some API call. "Patrick Molloy" wrote: I see the issue here - and that is of course that a function can only affect the caller - so the line that autofits the column width failes in a function while of course its fine in a sub The only thing that I could think of was to calculate the width of each letter then compare to the actual cell width. "Gary''s Student" wrote: Sub measure() Dim c As Range, v As String, w As Variant Dim shorter As String v = Range("A1").Value Set c = Columns("A") w = c.Width c.AutoFit w2 = c.Width If w = w2 Then MsgBox (v) Exit Sub End If For i = 1 To Len(v) shorter = Left(v, Len(v) - i) Range("A1").Value = shorter c.AutoFit w2 = c.Width If w2 <= w Then MsgBox (shorter) Range("A1").Value = v Exit Sub End If Next End Sub Basically, the sub does trial AutoFits. If you AutoFit and the column width has not changed, then the entire text fits in the cell. If the column width has changed, I keep chopping characters off the right until the the AutoFit does not expand the column. This approach has at least three problems: 1. A Sub can change the column width, a UDF cannot. 2. AutoFit responds to other cells in column. 3, The approach assumes the text is left justified. Any help will be greatly appreciated. -- Gary''s Student - gsnu200905 "p45cal" wrote: Gary''s Student;506433 Wrote: I am looking for a UDF that will return the visible text in a cell. For example, if A1 contains the sentence: "Now is the time for all good men to come to the aid of their party" The end of the sentence will "bleed" over into B1 unless column A is very wide. I want to see the portion of the sentence that actually appears in A1. I have been able to get this with a Sub, but not a Function. -- Gary''s Student - gsnu200905 How about posting your code so that we don't have to start from scratch? -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=139234 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
