asked on

VB.Net - Excel Automation Add Chceckbox to Cell

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

SOLUTION

Nasir Razzaq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jimbo99999

ASKER

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.

Jimbo99999

ASKER

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

Nasir Razzaq

For that, you would need to change the alignment of cell probably.

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range.horizontalalignment(v=office.11).aspx

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range.verticalalignment(v=office.11).aspx

Jimbo99999

ASKER

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

Jimbo99999

ASKER

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

ASKER CERTIFIED SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jimbo99999

ASKER

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