Peter
Fri May 09 02:12:12 CDT 2008
You might find this line will error if the shape is not a forms control
if (shp.Type.ToString() == "msoFormControl" &&
shp.FormControlType.ToString() == "xlCheckBox")
That's why in the VBA example I suggested
If sh.Type = msoFormControl Then
If sh.FormControlType = xlCheckBox Then
Did you actually try the VBA I posted, it will only take you a few seconds.
There's a very small possibility you might get different results looping by
index rather than For each.
> 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?
Why not explain to your client the problem and suggest you rename them. I
don't see what's error prone about doing that. Seems like a sensible and
easy thing to do. Alternatively you will need some logic to uniquely
identify your checkboxes, eg, location or caption. Of course either of these
may accidentally change. Index is definitely not a reliable way to
permanently identify them.
In your position I'd want to get to find out why the chekboxes all ended up
with the same name, quite difficult to achieve. Normally the only way to do
that is:
- rename the shape from its default original
- group it with other shape(s)
- copy the group
When you ungroup the copied group, any previously renamed shapes will be
duplicated. Try looking at the AlternativeText property. If the are all the
same, eg "Check Box 1", that'd be a strong indicator that someone has done
what I described above, a bit odd though.
Regards,
Peter T
"Amit Kathuria" <AmitKathuria@discussions.microsoft.com> wrote in message
news:A91724A6-CC1A-4414-873F-CF6F79828083@microsoft.com...
> 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 Kathuria@discussions.microsoft.com> wrote in
message
> > news:62133E7B-E65B-4C26-B47E-C0520F4DCB11@microsoft.com...
> > > 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("WorksheetName");
> > > Excel.CheckBox cb =
> > >
(Excel.CheckBox)workSheet.Shapes.Item("CheckBoxName").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.
> >
> >
> >