how to find if an excel sheet has any hidden values in a cell in VB.NET
 

I am trying to check if any worksheet has hidden values in a cell. I am
very new to VBA programming. I came across these---
Excel.XlCellType.xlCellTypeBlanks and
Excel.XlCellType.xlCellTypeVisible

But I am not sure if this is what would serve my purpose.

Thanks

how to find if an excel sheet has any hidden values in a cell in VB.NET
 

"sparrow" wrote in message
oups.com...
I am trying to check if any worksheet has hidden values in a cell. I am
very new to VBA programming. I came across these---
Excel.XlCellType.xlCellTypeBlanks and
Excel.XlCellType.xlCellTypeVisible

But I am not sure if this is what would serve my purpose.

Thanks


Depends on what you mean by "hidden values" . It's difficult to hide values
in cells. It can be done by custom formatting, but values normally also
appear in the formula bar, so you will have to tweak your view options,
protect your sheet etc.

Or do you mean check, if a worksheet has hidden cells? (Technically, cells
can't be hidden, but rows or columns can).

xlCellTypeBlanks are cells without values, probably not of interest in your
case.
xlCellTypeVisible are visible cells, quite the opposite from what you are
looking for. Still might serve your purpose somehow, but again, without
knowing what you are looking for it's just guesswork.

Joerg

how to find if an excel sheet has any hidden values in a cell in VB.NET
 

Hi Joerg,
Thanks for your response.
I was looking for a code to check if a worksheet has been formatted
such that the cell values are not visible unless you click on the cell
and see its value in the formula bar. So if a user was to open a
worksheet of this formatting, he may see some cells blank, but when he
selects that cell, he can see its value in the formula bar. I need to
identify any worksheet that has this setting.

The xlCellTypeVisible might serve my purpose, but I am not sure how to
use it.

Thanks once again.



Joerg wrote:
"sparrow" wrote in message
oups.com...
I am trying to check if any worksheet has hidden values in a cell. I am
very new to VBA programming. I came across these---
Excel.XlCellType.xlCellTypeBlanks and
Excel.XlCellType.xlCellTypeVisible

But I am not sure if this is what would serve my purpose.

Thanks


Depends on what you mean by "hidden values" . It's difficult to hide values
in cells. It can be done by custom formatting, but values normally also
appear in the formula bar, so you will have to tweak your view options,
protect your sheet etc.

Or do you mean check, if a worksheet has hidden cells? (Technically, cells
can't be hidden, but rows or columns can).

xlCellTypeBlanks are cells without values, probably not of interest in your
case.
xlCellTypeVisible are visible cells, quite the opposite from what you are
looking for. Still might serve your purpose somehow, but again, without
knowing what you are looking for it's just guesswork.

Joerg

how to find if an excel sheet has any hidden values in a cell in VB.NET
 

"sparrow" wrote in message
ps.com...
Hi Joerg,
Thanks for your response.
I was looking for a code to check if a worksheet has been formatted
such that the cell values are not visible unless you click on the cell
and see its value in the formula bar. So if a user was to open a
worksheet of this formatting, he may see some cells blank, but when he
selects that cell, he can see its value in the formula bar. I need to
identify any worksheet that has this setting.

The xlCellTypeVisible might serve my purpose, but I am not sure how to
use it.

Thanks once again.


OK, sparrow, now we are getting close.
Let's assume a user has hidden cell values by formatting cells with a custom
format like "";""';"";"" (that's 4 pairs of double quotes, separated by
semicolons). Cells appear to be empty (exception: errors are visible), but
can contain any value . The following macro would select all such cells or
pop up a message, that no such cells were found. You may want to tweak the
macro to suit your need.

Sub FindHiddenCells()
Dim MyArea As Range
For Each cell In ActiveSheet.UsedRange
If Not IsEmpty(cell) And Trim(cell.Text) = "" Then
If MyArea Is Nothing Then Set MyArea = cell
Set MyArea = Union(MyArea, cell)
End If
Next cell
If MyArea Is Nothing Then
MsgBox "No hidden cells found!"
Else
MyArea.Select
End If
End Sub


Hope this help.

Have a nice weekend
Joerg

how to find if an excel sheet has any hidden values in a cell in VB.NET
 

Hi Joerg,
I am sorry to respond so late. I hadn't got the chance to try out your
code. It worked very well for me. Thanks a lot for your help.

Joerg wrote:
"sparrow" wrote in message
ps.com...
Hi Joerg,
Thanks for your response.
I was looking for a code to check if a worksheet has been formatted
such that the cell values are not visible unless you click on the cell
and see its value in the formula bar. So if a user was to open a
worksheet of this formatting, he may see some cells blank, but when he
selects that cell, he can see its value in the formula bar. I need to
identify any worksheet that has this setting.

The xlCellTypeVisible might serve my purpose, but I am not sure how to
use it.

Thanks once again.


OK, sparrow, now we are getting close.
Let's assume a user has hidden cell values by formatting cells with a custom
format like "";""';"";"" (that's 4 pairs of double quotes, separated by
semicolons). Cells appear to be empty (exception: errors are visible), but
can contain any value . The following macro would select all such cells or
pop up a message, that no such cells were found. You may want to tweak the
macro to suit your need.

Sub FindHiddenCells()
Dim MyArea As Range
For Each cell In ActiveSheet.UsedRange
If Not IsEmpty(cell) And Trim(cell.Text) = "" Then
If MyArea Is Nothing Then Set MyArea = cell
Set MyArea = Union(MyArea, cell)
End If
Next cell
If MyArea Is Nothing Then
MsgBox "No hidden cells found!"
Else
MyArea.Select
End If
End Sub


Hope this help.

Have a nice weekend
Joerg