Zyrus Khambatta

Hi all,

I've used these forums extensively in the past, and I've finally come across something that has me totally stumped!

I'm developing an Excel 2003 application using VSTO / Visual C# 2005. For it to function correctly, I need to fire an event when a column / row size changes. I've racked my brains and searched all over the place to no avail.

Could someone let me know if it is at all possible, and what needs to be done to make it happen

Any help is much appreciated!

Thanks.

Zyrus




Re: Visual Studio Tools for Office Detect column/row resize in Excel

Dennis Wallentin

Hi Zyrus,


As far as I know there exist no event to track rows/columns size changes. We can define rangee where the sizes can be checked but it will be a rather clunsy solution with limitations.






Re: Visual Studio Tools for Office Detect column/row resize in Excel

Zyrus Khambatta

Thanks for that Dennis. Although not the answer I was hoping for unfortunately.

The way I perceived it being possible was to somehow:

- subclass the Excel application instance (not sure if this is possible),

- handle the WM_LBUTTONDOWN message,

- in the handler, verify we're on the right worksheet and read some window metrics (this is another big unknown) to determine if the mouse was clicked in either the row or column header area and then perform the actions I'm trying to.

I'm not sure if this is a correct way to achieve this, or if it is even possible, but I'd really like to get this figured out!

Thanks.

Zyrus






Re: Visual Studio Tools for Office Detect column/row resize in Excel

Dennis Wallentin

Hi Zyrus,

I'm not the person to answer You about VMI and subclassing is from my point of view not possible.

Anyway, let us know why You need to track the sizes of rows/columns. Is it for a specific range






Re: Visual Studio Tools for Office Detect column/row resize in Excel

Zyrus Khambatta

Well, I'm manually drawing some lines based on the geometry of the page (and specifically the cells) at any point in time. This means that when the user changes the column/row sizes, it needs to redraw the lines.

The range is not fixed as it does the calculations based upon an arbitrary number of cells depending on how much data the user has entered. It could potentially (although unlikely) span the entire set of columns in the sheet.

If you can conceive another way to approach this, it would be great!

Thanks.

Regards

Zyrus






Re: Visual Studio Tools for Office Detect column/row resize in Excel

Dennis Wallentin

Zyrus,

One approach that may be an acceptable workaround is to first remove the gridlines on the targeting sheet and then add border lines to the actually used range. So when the rows/columns are resized the border lines will dynamically be updated.


The following VB code snippet shows how to remove the gridlines and add customized border lines to a wanted range.

Code Snippet
Globals.ThisWorkbook.Application.ActiveWindow.DisplayGridlines = False
Dim rnArea As Excel.Range = Globals.Sheet1.Range("A2:B2")
With rnArea
.Borders(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlContinuous
.Borders(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous
.Borders(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlContinuous
.Borders(Excel.XlBordersIndex.xlEdgeRight).LineStyle = Excel.XlLineStyle.xlContinuous
End With