Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find values on another sheet | Excel Worksheet Functions | |||
How to find non-blank cell values "hidden" under merged regions? | Excel Discussion (Misc queries) | |||
How can I find/unhide hidden excel sheet tabs on a worksheet? | Excel Worksheet Functions | |||
how to find and copy values on sheet 2, based on a list on sheet 1 | Excel Programming | |||
find out if cell is hidden in if statement | Excel Programming |