Hi All

In XL2002, I'd like to be able to apply dot trailers to a cell if the text
in the cell is too long to fit with the current column width - much the same
as it does in Windows Explorer.

e.g.
If the text in the cell read: "This is text in the cell but it's too long"
and the column width is set to 10, then the visible text would read
"This is text in a ce..."

An ideas, or pre-defind code I can use?

Thanks

Trevor Williams

Here is one rough and ready way that you can tune

In a standard code module add

Option Explicit

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
' Copyright ©1996-2005 VBnet, Randy Birch, All Rights Reserved.
' Some pages may also contain other copyrights by the author.
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
' Distribution: You can freely use this code in your own
' applications, but you may not reproduce
' or publish this code on any web site,
' online service, or distribute as source
' on any media without express permission.
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
Private Const MAX_PATH As Long = 260

Private Declare Function PathCompactPath Lib "shlwapi.dll" _
Alias "PathCompactPathA" _
(ByVal hdc As Long, _
ByVal lpszPath As String, _
ByVal dx As Long) As Long

Private Declare Function PathCompactPathEx Lib "shlwapi.dll" _
Alias "PathCompactPathExA" _
(ByVal pszOut As String, _
ByVal pszSrc As String, _
ByVal cchMax As Long, _
ByVal dwFlags As Long) As Long

Private Declare Function lstrlenW Lib "kernel32" _
(ByVal lpString As Long) As Long

Public Function MakeCompactedPathPixels(ByVal sPath As String, _
dwHdc As Long, _
dwPixels As Long) As String
Dim nReturn As Long
Dim sBuffer As String

'the path to compact and the return buffer are the same string
'and must be MAX_PATH in length
sBuffer = sPath & Chr$(0) & Space$(MAX_PATH - Len(sPath) - 1)
nReturn = PathCompactPath(dwHdc, sBuffer, dwPixels)
MakeCompactedPathPixels = TrimNull(sBuffer)

End Function

Private Function TrimNull(item As String) As String
Dim iPos As Long
iPos = InStr(item, vbNullChar)
TrimNull = IIf(iPos 0, Left$(item, iPos - 1), item)
End Function

Then in your target worksheet add

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit
Const MAX_LEN As Long = 25 '<== change to suit
Const MULTIPLIER As Long = 2.5 '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Len(.Value) MAX_LEN Then
.Value = MakeCompactedPathPixels(.Value, 0, .Width *
MULTIPLIER)
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Trevor Williams" wrote in
message ...
Hi All

In XL2002, I'd like to be able to apply dot trailers to a cell if the text
in the cell is too long to fit with the current column width - much the
same
as it does in Windows Explorer.

e.g.
If the text in the cell read: "This is text in the cell but it's too long"
and the column width is set to 10, then the visible text would read
"This is text in a ce..."

An ideas, or pre-defind code I can use?

Thanks

Trevor Williams
--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Trevor Williams" wrote in
message ...
Hi All

In XL2002, I'd like to be able to apply dot trailers to a cell if the text
in the cell is too long to fit with the current column width - much the
same
as it does in Windows Explorer.

e.g.
If the text in the cell read: "This is text in the cell but it's too long"
and the column width is set to 10, then the visible text would read
"This is text in a ce..."

An ideas, or pre-defind code I can use?

Thanks

Trevor Williams

Thanks Bob

Unfortunately it's not quite what I was looking for as its specific to a
character count rather than a column width, plus it TRIMS the non fitting
text completely off so the cell only contains the predefind amount of
characters, not the total sentance.

Any other thoughts?

Trevor

"Bob Phillips" wrote:

Here is one rough and ready way that you can tune

In a standard code module add

Option Explicit

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
' Copyright ©1996-2005 VBnet, Randy Birch, All Rights Reserved.
' Some pages may also contain other copyrights by the author.
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
' Distribution: You can freely use this code in your own
' applications, but you may not reproduce
' or publish this code on any web site,
' online service, or distribute as source
' on any media without express permission.
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
Private Const MAX_PATH As Long = 260

Private Declare Function PathCompactPath Lib "shlwapi.dll" _
Alias "PathCompactPathA" _
(ByVal hdc As Long, _
ByVal lpszPath As String, _
ByVal dx As Long) As Long

Private Declare Function PathCompactPathEx Lib "shlwapi.dll" _
Alias "PathCompactPathExA" _
(ByVal pszOut As String, _
ByVal pszSrc As String, _
ByVal cchMax As Long, _
ByVal dwFlags As Long) As Long

Private Declare Function lstrlenW Lib "kernel32" _
(ByVal lpString As Long) As Long

Public Function MakeCompactedPathPixels(ByVal sPath As String, _
dwHdc As Long, _
dwPixels As Long) As String
Dim nReturn As Long
Dim sBuffer As String

'the path to compact and the return buffer are the same string
'and must be MAX_PATH in length
sBuffer = sPath & Chr$(0) & Space$(MAX_PATH - Len(sPath) - 1)
nReturn = PathCompactPath(dwHdc, sBuffer, dwPixels)
MakeCompactedPathPixels = TrimNull(sBuffer)

End Function

Private Function TrimNull(item As String) As String
Dim iPos As Long
iPos = InStr(item, vbNullChar)
TrimNull = IIf(iPos 0, Left$(item, iPos - 1), item)
End Function

Then in your target worksheet add

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit
Const MAX_LEN As Long = 25 '<== change to suit
Const MULTIPLIER As Long = 2.5 '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Len(.Value) MAX_LEN Then
.Value = MakeCompactedPathPixels(.Value, 0, .Width *
MULTIPLIER)
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Trevor Williams" wrote in
message ...
Hi All

In XL2002, I'd like to be able to apply dot trailers to a cell if the text
in the cell is too long to fit with the current column width - much the
same
as it does in Windows Explorer.

e.g.
If the text in the cell read: "This is text in the cell but it's too long"
and the column width is set to 10, then the visible text would read
"This is text in a ce..."

An ideas, or pre-defind code I can use?

Thanks

Trevor Williams
--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Trevor Williams" wrote in
message ...
Hi All

In XL2002, I'd like to be able to apply dot trailers to a cell if the text
in the cell is too long to fit with the current column width - much the
same
as it does in Windows Explorer.

e.g.
If the text in the cell read: "This is text in the cell but it's too long"
and the column width is set to 10, then the visible text would read
"This is text in a ce..."

An ideas, or pre-defind code I can use?

Thanks

Trevor Williams

How do you envisage having the full text in there but seeing the trimmed
text? A cell can only hold one value.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Trevor Williams" wrote in
message ...
Thanks Bob

Unfortunately it's not quite what I was looking for as its specific to a
character count rather than a column width, plus it TRIMS the non fitting
text completely off so the cell only contains the predefind amount of
characters, not the total sentance.

Any other thoughts?

Trevor

"Bob Phillips" wrote:

Here is one rough and ready way that you can tune

In a standard code module add

Option Explicit

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
' Copyright ©1996-2005 VBnet, Randy Birch, All Rights Reserved.
' Some pages may also contain other copyrights by the author.
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
' Distribution: You can freely use this code in your own
' applications, but you may not reproduce
' or publish this code on any web site,
' online service, or distribute as source
' on any media without express permission.
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
Private Const MAX_PATH As Long = 260

Private Declare Function PathCompactPath Lib "shlwapi.dll" _
Alias "PathCompactPathA" _
(ByVal hdc As Long, _
ByVal lpszPath As String, _
ByVal dx As Long) As Long

Private Declare Function PathCompactPathEx Lib "shlwapi.dll" _
Alias "PathCompactPathExA" _
(ByVal pszOut As String, _
ByVal pszSrc As String, _
ByVal cchMax As Long, _
ByVal dwFlags As Long) As Long

Private Declare Function lstrlenW Lib "kernel32" _
(ByVal lpString As Long) As Long

Public Function MakeCompactedPathPixels(ByVal sPath As String, _
dwHdc As Long, _
dwPixels As Long) As String
Dim nReturn As Long
Dim sBuffer As String

'the path to compact and the return buffer are the same string
'and must be MAX_PATH in length
sBuffer = sPath & Chr$(0) & Space$(MAX_PATH - Len(sPath) - 1)
nReturn = PathCompactPath(dwHdc, sBuffer, dwPixels)
MakeCompactedPathPixels = TrimNull(sBuffer)

End Function

Private Function TrimNull(item As String) As String
Dim iPos As Long
iPos = InStr(item, vbNullChar)
TrimNull = IIf(iPos 0, Left$(item, iPos - 1), item)
End Function

Then in your target worksheet add

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit
Const MAX_LEN As Long = 25 '<== change to suit
Const MULTIPLIER As Long = 2.5 '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Len(.Value) MAX_LEN Then
.Value = MakeCompactedPathPixels(.Value, 0, .Width *
MULTIPLIER)
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Trevor Williams" wrote in
message ...
Hi All

In XL2002, I'd like to be able to apply dot trailers to a cell if the
text
in the cell is too long to fit with the current column width - much the
same
as it does in Windows Explorer.

e.g.
If the text in the cell read: "This is text in the cell but it's too
long"
and the column width is set to 10, then the visible text would read
"This is text in a ce..."

An ideas, or pre-defind code I can use?

Thanks

Trevor Williams
--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Trevor Williams" wrote in
message ...
Hi All

In XL2002, I'd like to be able to apply dot trailers to a cell if the
text
in the cell is too long to fit with the current column width - much the
same
as it does in Windows Explorer.

e.g.
If the text in the cell read: "This is text in the cell but it's too
long"
and the column width is set to 10, then the visible text would read
"This is text in a ce..."

An ideas, or pre-defind code I can use?

Thanks

Trevor Williams

Create a UDF ?

Eg:
=TrimText("Something very long etc etc etc")

Within that UDF you could reference

Application.Caller.ColumnWidth

and then figure out how much text of the input text should be displayed.
However, it would only update on calculation and not on a change in the
column width.

Tim




"Trevor Williams" wrote in
message ...
Thanks Bob

Unfortunately it's not quite what I was looking for as its specific to a
character count rather than a column width, plus it TRIMS the non fitting
text completely off so the cell only contains the predefind amount of
characters, not the total sentance.

Any other thoughts?

Trevor

"Bob Phillips" wrote:

Here is one rough and ready way that you can tune

In a standard code module add

Option Explicit

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
' Copyright ©1996-2005 VBnet, Randy Birch, All Rights Reserved.
' Some pages may also contain other copyrights by the author.
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
' Distribution: You can freely use this code in your own
' applications, but you may not reproduce
' or publish this code on any web site,
' online service, or distribute as source
' on any media without express permission.
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
Private Const MAX_PATH As Long = 260

Private Declare Function PathCompactPath Lib "shlwapi.dll" _
Alias "PathCompactPathA" _
(ByVal hdc As Long, _
ByVal lpszPath As String, _
ByVal dx As Long) As Long

Private Declare Function PathCompactPathEx Lib "shlwapi.dll" _
Alias "PathCompactPathExA" _
(ByVal pszOut As String, _
ByVal pszSrc As String, _
ByVal cchMax As Long, _
ByVal dwFlags As Long) As Long

Private Declare Function lstrlenW Lib "kernel32" _
(ByVal lpString As Long) As Long

Public Function MakeCompactedPathPixels(ByVal sPath As String, _
dwHdc As Long, _
dwPixels As Long) As String
Dim nReturn As Long
Dim sBuffer As String

'the path to compact and the return buffer are the same string
'and must be MAX_PATH in length
sBuffer = sPath & Chr$(0) & Space$(MAX_PATH - Len(sPath) - 1)
nReturn = PathCompactPath(dwHdc, sBuffer, dwPixels)
MakeCompactedPathPixels = TrimNull(sBuffer)

End Function

Private Function TrimNull(item As String) As String
Dim iPos As Long
iPos = InStr(item, vbNullChar)
TrimNull = IIf(iPos 0, Left$(item, iPos - 1), item)
End Function

Then in your target worksheet add

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit
Const MAX_LEN As Long = 25 '<== change to suit
Const MULTIPLIER As Long = 2.5 '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Len(.Value) MAX_LEN Then
.Value = MakeCompactedPathPixels(.Value, 0, .Width *
MULTIPLIER)
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Trevor Williams" wrote in
message ...
Hi All

In XL2002, I'd like to be able to apply dot trailers to a cell if the
text
in the cell is too long to fit with the current column width - much the
same
as it does in Windows Explorer.

e.g.
If the text in the cell read: "This is text in the cell but it's too
long"
and the column width is set to 10, then the visible text would read
"This is text in a ce..."

An ideas, or pre-defind code I can use?

Thanks

Trevor Williams
--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Trevor Williams" wrote in
message ...
Hi All

In XL2002, I'd like to be able to apply dot trailers to a cell if the
text
in the cell is too long to fit with the current column width - much the
same
as it does in Windows Explorer.

e.g.
If the text in the cell read: "This is text in the cell but it's too
long"
and the column width is set to 10, then the visible text would read
"This is text in a ce..."

An ideas, or pre-defind code I can use?

Thanks

Trevor Williams

But that cannot go in the same cell as the text, so my question remains.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Tim Williams" <timjwilliams at gmail dot com wrote in message
...
Create a UDF ?

Eg:
=TrimText("Something very long etc etc etc")

Within that UDF you could reference

Application.Caller.ColumnWidth

and then figure out how much text of the input text should be displayed.
However, it would only update on calculation and not on a change in the
column width.

Tim




"Trevor Williams" wrote in
message ...
Thanks Bob

Unfortunately it's not quite what I was looking for as its specific to a
character count rather than a column width, plus it TRIMS the non fitting
text completely off so the cell only contains the predefind amount of
characters, not the total sentance.

Any other thoughts?

Trevor

"Bob Phillips" wrote:

Here is one rough and ready way that you can tune

In a standard code module add

Option Explicit

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
' Copyright ©1996-2005 VBnet, Randy Birch, All Rights Reserved.
' Some pages may also contain other copyrights by the author.
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
' Distribution: You can freely use this code in your own
' applications, but you may not reproduce
' or publish this code on any web site,
' online service, or distribute as source
' on any media without express permission.
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
Private Const MAX_PATH As Long = 260

Private Declare Function PathCompactPath Lib "shlwapi.dll" _
Alias "PathCompactPathA" _
(ByVal hdc As Long, _
ByVal lpszPath As String, _
ByVal dx As Long) As Long

Private Declare Function PathCompactPathEx Lib "shlwapi.dll" _
Alias "PathCompactPathExA" _
(ByVal pszOut As String, _
ByVal pszSrc As String, _
ByVal cchMax As Long, _
ByVal dwFlags As Long) As Long

Private Declare Function lstrlenW Lib "kernel32" _
(ByVal lpString As Long) As Long

Public Function MakeCompactedPathPixels(ByVal sPath As String, _
dwHdc As Long, _
dwPixels As Long) As String
Dim nReturn As Long
Dim sBuffer As String

'the path to compact and the return buffer are the same string
'and must be MAX_PATH in length
sBuffer = sPath & Chr$(0) & Space$(MAX_PATH - Len(sPath) - 1)
nReturn = PathCompactPath(dwHdc, sBuffer, dwPixels)
MakeCompactedPathPixels = TrimNull(sBuffer)

End Function

Private Function TrimNull(item As String) As String
Dim iPos As Long
iPos = InStr(item, vbNullChar)
TrimNull = IIf(iPos 0, Left$(item, iPos - 1), item)
End Function

Then in your target worksheet add

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit
Const MAX_LEN As Long = 25 '<== change to suit
Const MULTIPLIER As Long = 2.5 '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Len(.Value) MAX_LEN Then
.Value = MakeCompactedPathPixels(.Value, 0, .Width *
MULTIPLIER)
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Trevor Williams" wrote in
message ...
Hi All

In XL2002, I'd like to be able to apply dot trailers to a cell if the
text
in the cell is too long to fit with the current column width - much
the
same
as it does in Windows Explorer.

e.g.
If the text in the cell read: "This is text in the cell but it's too
long"
and the column width is set to 10, then the visible text would read
"This is text in a ce..."

An ideas, or pre-defind code I can use?

Thanks

Trevor Williams
--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Trevor Williams" wrote in
message ...
Hi All

In XL2002, I'd like to be able to apply dot trailers to a cell if the
text
in the cell is too long to fit with the current column width - much
the
same
as it does in Windows Explorer.

e.g.
If the text in the cell read: "This is text in the cell but it's too
long"
and the column width is set to 10, then the visible text would read
"This is text in a ce..."

An ideas, or pre-defind code I can use?

Thanks

Trevor Williams

On Dec 13, 4:09 am, "Bob Phillips" wrote:
But that cannot go in the same cell as the text, so my question remains.

--
---
HTH

Bob

I guess that would depend on how the text is getting there in the
first place: if a macro is populating the cells then it could just as
easily wrap the text in the UDF.

Tim



(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Tim Williams" <timjwilliams at gmail dot com wrote in . gbl...



Create a UDF ?

Eg:
=TrimText("Something very long etc etc etc")

Within that UDF you could reference

Application.Caller.ColumnWidth

and then figure out how much text of the input text should be displayed.
However, it would only update on calculation and not on a change in the
column width.

Tim

No, there is no way that you can have the UDF in a cell and the value that
it is being worked on.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



wrote in message
...
On Dec 13, 4:09 am, "Bob Phillips" wrote:
But that cannot go in the same cell as the text, so my question remains.

--
---
HTH

Bob

I guess that would depend on how the text is getting there in the
first place: if a macro is populating the cells then it could just as
easily wrap the text in the UDF.

Tim



(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Tim Williams" <timjwilliams at gmail dot com wrote in
. gbl...



Create a UDF ?

Eg:
=TrimText("Something very long etc etc etc")

Within that UDF you could reference

Application.Caller.ColumnWidth

and then figure out how much text of the input text should be
displayed.
However, it would only update on calculation and not on a change in the
column width.

Tim

Bob,

I had mistakenly assumed the OP wanted this for *viewing* purposes only (ie.
no downstream formulas etc).
On re-reading the posts I see that's not the case.

Tim


"Bob Phillips" wrote in message
...
No, there is no way that you can have the UDF in a cell and the value that
it is being worked on.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



wrote in message
...
On Dec 13, 4:09 am, "Bob Phillips" wrote:
But that cannot go in the same cell as the text, so my question remains.

--
---
HTH

Bob

I guess that would depend on how the text is getting there in the
first place: if a macro is populating the cells then it could just as
easily wrap the text in the UDF.

Tim



(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Tim Williams" <timjwilliams at gmail dot com wrote in
. gbl...



Create a UDF ?

Eg:
=TrimText("Something very long etc etc etc")

Within that UDF you could reference

Application.Caller.ColumnWidth

and then figure out how much text of the input text should be
displayed.
However, it would only update on calculation and not on a change in
the
column width.

Tim