[SOLVED] 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

"sparrow" <parvathypillai@gmail.com> wrote in message
news:1154565707.731734.245890@m73g2000cwd.googlegroups.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

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" <parvathypillai@gmail.com> wrote in message
> news:1154565707.731734.245890@m73g2000cwd.googlegroups.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

"sparrow" <parvathypillai@gmail.com> wrote in message
news:1154644774.632173.47830@75g2000cwc.googlegroups.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

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" <parvathypillai@gmail.com> wrote in message
> news:1154644774.632173.47830@75g2000cwc.googlegroups.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