Thanks for your reply, Peter. I used the following C# code to verify the
names of the check boxes:
textBox1.Text = "";
foreach (Excel.Shape shp in workSheet.Shapes)
{
if (shp.Type.ToString() == "msoFormControl" &&
shp.FormControlType.ToString() == "xlCheckBox")
{
Excel.CheckBox cb = (Excel.CheckBox)shp.OLEFormat.Object;
string output = cb.Index.ToString() + "\t" + shp.Name.ToString() + "\t" +
cb.Text.ToString() + "\t" + cb.Value.ToString() + "\r\n";
textBox1.Text += output;
}
}
With this I get the index, name, text and value for each check box in my
worksheet, but all the names returned are "CheckBox".
My spreadsheets contain some survey results which my client conducted. I'm
supposed to enter the contents of these spreadsheets into a database.
Modifying the names of all check boxes is clearly out of scope of my work
and, I think, is also error prone. Is there any other way out?
Thanks again.
"Peter T" wrote:
But now I see that all the check boxes have the same name
"CheckBox" which is very strange.
Indeed very strange. Try a bit of VBA to verify their names (in this case
loop by index rather than For Each)
Sub test()
Dim i As Long
Dim ws As Worksheet
Dim sh As Shape
Dim cb As CheckBox
Set ws = ActiveSheet
For i = 1 To ws.CheckBoxes.Count
With ws.CheckBoxes(i)
Debug.Print i, .Name, .Value, _
.TopLeftCell.Address(0, 0)
End With
Next
Debug.Print
For i = 1 To ws.Shapes.Count
Set sh = ws.Shapes(i)
If sh.Type = msoFormControl Then
If sh.FormControlType = xlCheckBox Then
Debug.Print i, sh.Name, sh.OLEFormat.Object.Value, _
sh.TopLeftCell.Address(0, 0)
End If
End If
Next
End Sub
Normally you should see similar results in both loops although the index
numbers i may be different if the sheet contains other shapes besides
checkboxes.
If all the names are the same, theoretically possible but unusual, use VBA
to rename them. Use some logical naming convention looping the Checkboxes
collection.
FWIW you might come across xlOn & xlOff as referring to 1 & -4146
respectively.
Regards,
Peter T
"Amit Kathuria" <Amit wrote in message
...
I have an Excel spreadsheet which contains multiple un-grouped check boxes
(from the Forms toolbar) which are not linked to any particular cell(s). I
want to read their values in .NET code. For this I'm using 'Microsoft
Office
Interop Excel' and 'Microsoft Vbe Interop Forms' libraries. This is the
code
I'm using:
String excelFile = "C:\\FileName.xls";
Excel.Application estimate = new Excel.Application();
Excel.Worksheet workSheet = new Excel.Worksheet();
estimate.Workbooks.Open(excelFile, 0, true, 5, "", "", false,
Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
workSheet =
(Excel.Worksheet)estimate.Worksheets.get_Item("Wor ksheetName");
Excel.CheckBox cb =
(Excel.CheckBox)workSheet.Shapes.Item("CheckBoxNam e").OLEFormat.Object;
cb.Value returns the value of the check box (the value is -4146 if the
check
box is unchecked, 1 if it is checked and null if it is mixed).
At some point in time I was able to see the names of all the check boxes
in
my worksheet (they were named "Check Box 1", "Check Box 2" and so on). But
now I see that all the check boxes have the same name "CheckBox" which is
very strange. This is making it impossible for me to retrieve the values
of
the check boxes by specifying the check box name. I know I can use the
index
also but it poses a similar problem - 'how do I know the index of each
check
box?'. Coming back to the name issue, is there any way I can see the
unique
names of all my check boxes again? Is there any mode which I have to use
to
be able to see the unique names? What am I missing here?
Any help would be greatly appreciated. Thanks in advance.