UnMerge Opened Workbook from Add-In

[scott]

Below is a sub I have that unmerges any cells in the workbook. My problem is
that I now need this sub to exist in an add-in and run on any opened
worksheet (I'd prefer it to test if merged cells exist 1st, but not a
requirement). Ideally, it would iterate through all opened workbooks and run
on any sheets.

Given the fact that I'm not going to know the name of the workbook that will
have the merged cells and that I will be performing actions on from the sub
functions contained in the add-in, how can I force the below add-in sub to
unmerge cells on the opened workbook? Right now it gives an error when run
from Auto_Open() sub in the add-in. Error is "method cells of object _global
failed"


Sub UnMerge()

Cells.Select
With Selection
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

End Sub

 

[Tom Ogilvy]

If I understand what you want to do, you would need to instantiate
application level events. Using the application level workbook_open
equivalent, you would then be able to react to each workbook as it is
opened. The workbook_Open event of your addin only fires when the addin is
first opened - thus your problem.

for each sh in Activeworkbook.Worksheets
sh.Cells.Unmerge
Next

would work for me to unmerge all cells - I don't know what that code is you
have, but maybe you recorded it that way.

http://www.cpearson.com/excel/appevent.htm
Chip Pearson's page on application level events.

 

[Dave Peterson]

I wouldn't bother checking for merged cells. Just unmerge.

But...

Option Explicit
Sub UnMerge()

Dim wkbk As Workbook
Dim wks As Worksheet
For Each wkbk In Workbooks
For Each wks In wkbk.Worksheets
With wks.Cells
If IsNull(.MergeCells) _
Or .MergeCells = True Then
' .VerticalAlignment = xlTop
' .WrapText = False
' .Orientation = 0
' .AddIndent = False
' .ShrinkToFit = False
' .ReadingOrder = xlContext
.MergeCells = False
End If
End With
Next wks
Next wkbk

End Sub


I commented the non-merge lines.

 

[Dave Peterson]

And I missed the part that you want it run whenever you open a workbook.

 

[scott]

I read Chip's page on "Application Events" and still have an issue. I did
exactly like his download example except I'm forced to put below code in a
class module that resides in my add-in (which is always loaded).

His example works because the class module resides in a workbook. My problem
is my class module doesn't fire because it's in my add-in, not a workbook. I
can't put it in the workbook because it is being sent automatically from a
machine.

Is it possible to fire an event on open event in an add-in?


Private Sub App_WorkbookOpen(ByVal Wb As Excel.Workbook)

MsgBox "Application Event: WorkbookOpen: " & Wb.Name
Call UnMerge

End Sub

 

[Tom Ogilvy]

Sample addin sent.
--
Regards,
Tom Ogilvy

I read Chip's page on "Application Events" and still have an issue. I did
exactly like his download example except I'm forced to put below code in a
class module that resides in my add-in (which is always loaded).

His example works because the class module resides in a workbook. My problem
is my class module doesn't fire because it's in my add-in, not a workbook. I
can't put it in the workbook because it is being sent automatically from a
machine.

Is it possible to fire an event on open event in an add-in?


Private Sub App_WorkbookOpen(ByVal Wb As Excel.Workbook)

MsgBox "Application Event: WorkbookOpen: " & Wb.Name
Call UnMerge

End Sub