Make long strings visible?

[Robert Crandal]

After data is saved in our spreadsheet each day, we usually mark
both the spreadsheet and workbook as password "protected".
This prevents users from changing data on our spreadsheets.

The problem is, sometimes we have string data that exceeds the
width of cells, which hides most of the string in that cell. Are there any
methods to allow a user to view the complete string in the above type
of scenario?? If the user double clicks on a string cell can I just display
that string in MsgBox or something?? Any other ideas?

Thank you!

Robert

 

[Howard]

After data is saved in our spreadsheet each day, we usually mark

both the spreadsheet and workbook as password "protected".

This prevents users from changing data on our spreadsheets.



The problem is, sometimes we have string data that exceeds the

width of cells, which hides most of the string in that cell. Are there any

methods to allow a user to view the complete string in the above type

of scenario?? If the user double clicks on a string cell can I just display

that string in MsgBox or something?? Any other ideas?



Thank you!



Robert

Hi Robert,
Maybe something like this where all you have to do is select the a cell.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim x As String
x = ActiveCell.Value
If x = "" Then Exit Sub
MsgBox x
End Sub

Regards,
Howard

 

[GS]

Robert Crandal expressed precisely :

After data is saved in our spreadsheet each day, we usually mark
both the spreadsheet and workbook as password "protected".
This prevents users from changing data on our spreadsheets.

The problem is, sometimes we have string data that exceeds the
width of cells, which hides most of the string in that cell. Are there any
methods to allow a user to view the complete string in the above type
of scenario?? If the user double clicks on a string cell can I just display
that string in MsgBox or something?? Any other ideas?

Thank you!

Robert

The contents will display in the FormulaBar when the cell is selected.
Does this not work for you?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[MerseyBeat]

After data is saved in our spreadsheet each day, we usually mark
both the spreadsheet and workbook as password "protected".
This prevents users from changing data on our spreadsheets.

The problem is, sometimes we have string data that exceeds the
width of cells, which hides most of the string in that cell. Are there
any
methods to allow a user to view the complete string in the above type
of scenario?? If the user double clicks on a string cell can I just
display
that string in MsgBox or something?? Any other ideas?

Thank you!

Robert

Just another idea

In the Worksheet_Change event, test if ActiveCell.value is over a certain
number of characters using the LEN function. If so, set the activecell to
wrap text or shrink to fit contents.

 

[MerseyBeat]

Just another idea

In the Worksheet_Change event, test if ActiveCell.value is over a certain
number of characters using the LEN function. If so, set the activecell to
wrap text or shrink to fit contents.

Better yet, don't even test the length, just set each cell to wrap text or
shrink to fit contents

 

[Robert Crandal]

GS expressed precisely :

The contents will display in the FormulaBar when the cell is selected.
Does this not work for you?

This will not work, because our spreadsheet/workbook is marked
as "password protected" after a few days. If you try to click on any
cell Excel displays the following message:

"The cell or chart that you are trying to change is protected
and therefore read-only"

Users are not allowed to modify/edit old workbooks, so, it's just slightly
annoying when long strings are not easily visible in these read-only
workbooks.

Robert

 

[GS]

Robert Crandal presented the following explanation :

This will not work, because our spreadsheet/workbook is marked
as "password protected" after a few days. If you try to click on any
cell Excel displays the following message:

"The cell or chart that you are trying to change is protected
and therefore read-only"

Users are not allowed to modify/edit old workbooks, so, it's just slightly
annoying when long strings are not easily visible in these read-only
workbooks.

Robert

Well, that's just a protection setting. You could enable selecting
locked cells and they still won't be able to edit them. (By the way,
selection of locked cells is enabled by default and so removing that
was deliberate on the part of the person applying the protection!) Of
course, if the values are the result of formulas then users would see
the formula unless you chose to hide those.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Ben McClave]

Robert,

When I applied protection to a sheet in my test workbook, I could see the full text of a given cell in the formula bar by simply entering the cell address in the Name Box (next to the formula bar).

If that doesn't work for you (for example, if the formulas are hidden for the cell in question), this macro worked for me:

Sub ShowString()
Dim sText As String
Dim rCell As Range

On Error Resume Next
Set rCell = Application.InputBox("Please type a cell reference to view the full text.", "Reference cell?", , , , , , 8)

If rCell Is Nothing Then Exit Sub
If rCell.Cells.Count > 1 Then
MsgBox "Error: Reference cannot contain more than one cell."
Exit Sub
End If

sText = rCell.Value

If Len(sText) > 0 Then
MsgBox "Text in cell " & rCell.Address & ":" & vbCr & vbCr & sText
Else
MsgBox "No text found in selected cell"
End If

End Sub