VB.Net - Excel Automation Add Chceckbox to Cell

Jimbo99999
Jimbo99999 used Ask the Experts™
on
Good Day Experts!

I have been tasked with automating Excel from my VB.net project to display report data from a VB.net form.  The report is a evry simple layout.  I have column headings that I need to indicate yes or no in the cell under each of them.  

I could just add test yes or no but I thought adding a checkbox with it being checked or unchecked to indicate yes or no would look nicer.  

While I have come across pieces here and htere in my search, I have been able to figure out how t do this.

Can you assist with adding a checkbox to a cell from VB.net?

Thanks,
jimbo99999
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2014
Commented:

Author

Commented:
That is a novel idea...excellent.  Yes, I have noticed that the information I have been finding on adding checkboxes does not appear to be very straightforward.  

I will try the two images here now.

Author

Commented:
Ok, I am struggling a bit here.  I have the checkmrk.ico getting to the cell with the following code.  However, I am unable to figure out how to center the picture in the cell.

            Dim AppPath As String
            AppPath = "C:\Documents and Settings\User\My Documents\Visual Studio 2005\Projects\VelocityViewer\VelocityViewer"
            AppPath = AppPath + "\" + "CHECKMRK.ico"
            oRng = oSheet.Range("A4", "A4")
            System.Windows.Forms.Clipboard.SetDataObject(Image.FromFile(AppPath), True)
            oSheet.Paste(oRng, Image.FromFile(AppPath))
            System.Windows.Forms.Clipboard.Clear()

Is it possible?

Thanks,
jimbo99999
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Most Valuable Expert 2012
Top Expert 2014

Commented:

Author

Commented:
Valiant effort with the centering of the checkbox image and to no avail.  I have the following code:

            With oSheet.Range("A4", "H4")
                .Font.Size = 10
                .Font.Name = "Segoe UI"
                .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
            End With

So, I coded Yes or No to be sent to the cell instead of the image.  Yes or no was able to be centered by the above code.

Perhaps the image can't be centered?

Thanks,
jimbo99999

Author

Commented:
With a little more detective work, I have found that the cells are actually getting the centering applied based on the toolbar centering icon after I select the cell.  So, for some reason the image is not getting centered...but I don't know why or how to fix.

Thanks,
jimbo99999
Analyst Assistant
Commented:
Do you just want a checkmark?

If you do then you can use the Marlett font.
    With oSheet.Range("A4", "H4")
        .Font.Size = 10
        .Font.Name = "Marlett"
        .Value = "a"
        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
    End With

Open in new window

You can also use 'r' for a cross.

No idea if that helps.

Author

Commented:
Thanks for the responses.  The Marlett format works great...I have my checkmark now.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial