Addin auto run

[hg21]

HI
im trying to activate a macro using auto run addin. i want an action to activate when it found "Nmber" in the cloumn header, but Auto_Open works before the data of the file is loaded.
Im using:

Private Sub Auto_Open()
Cells.Find(What:="Number", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
a = ActiveCell.Column
Columns(a).Select
Selection.NumberFormat = "0"
End Sub

how can i make it work after the data is shown?

[GTO]

HI
im trying to activate a macro using auto run addin. i want an action to activate when it found "Nmber" in the cloumn header, but Auto_Open works before the data of the file is loaded...

HI there,

Firstly, welcome to vbaexpress!:hi: I'm sure you'll be very glad you joined:cloud9:

As to your question, do you actually mean you created and loaded an Add-In (.xla) file?

Mark

[mdmackillop]

Hard to understand why you would want this.
Consider using a template
You could create some code to format the column when Number is entered or to change existing workbooks to format all occurrences.

[hg21]

hi
Yes, i've created a xla addin file. I want to apply it on every workbook that i open.
i have an issue with 13 digits numbers that automatically looks like 5E+12 in the excel format and i want to change them all to numeric format without decimal point in each file i open.

thanks

[GTO]

Hi HG,

I am with Malcom in wondering if this is the best way to go about this, but it did sound to me as you might be opening workbooks that are being sent to you or otherwise were not created by you?

Anyways, if you are experimenting with using an Add-In, I would first address the issue you mentioned in Auto_Open. See, Auto_Open will only run once, and that is when the book it is housed in opens. So, in this case, it is running when your Add-In Opens or Loads, when you first start Excel, and before any workbooks open. The same would hold true for the more commonly used Workbook_Open() event.

So rather than an event detecting when ThisWorkbook (in this case the Add-In) opens, what you would need is an Application level event detecting when any workbook is opened in the current instance of Excel.

In vba Help, look up Using Events with the Application Object. In the example, you can see that a new Class module is used. Since we have just a wee bit of code, I just snuck my example into ThisWorkbook module, so it is a smidgeon different.

In ThisWorkbook Module:
Option Explicit

Private WithEvents XLApplication As Application

'========================================================================== =============
' Workbook Event(s)
'========================================================================== =============

Private Sub Workbook_Open()

Set XLApplication = Application
End Sub

'========================================================================== =============
' Application Event(s)
'========================================================================== =============

Private Sub XLApplication_WorkbookOpen(ByVal Wb As Workbook)
Dim COL_Ranges As Collection
Dim wks As Worksheet
Dim rngFoundNumberHeaderCell As Range
Dim i As Long
Dim strMsg As String

If Not Wb.FullName = ThisWorkbook.FullName Then
Set COL_Ranges = New Collection
For Each wks In Wb.Worksheets
'// "Reset" rngFoundNumberHeaderCell to Nothing, in case we found something last//
'// loop //
Set rngFoundNumberHeaderCell = Nothing
'// Attempt to set a reference to the found cell. If no cell is found, //
'// rngFoundNumberHeaderCell will still be Nothing. //
Set rngFoundNumberHeaderCell = wks.Range("1:1").Find(What:="Number", _
After:=wks.Cells(1), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=True)
'// So, if we found something, then rngFoundNumberHeaderCell will NOT be //
'// Nothing and we'll add the found cell to our collection of ranges. The Key //
'// is not critical for what we are doing, but does need to be unique, and in //
'// Collection's case, needs to be a String, so I used the worksheet's name in //
'// conjunction with the cell address for a unique Key value. //
If Not rngFoundNumberHeaderCell Is Nothing Then
COL_Ranges.Add Item:=rngFoundNumberHeaderCell, _
Key:=CStr(rngFoundNumberHeaderCell.Parent.Name & _
rngFoundNumberHeaderCell.Address)
End If
Next
'// See if we ever added anything to our collection... //
If COL_Ranges.Count > 0 Then
strMsg = "I found ""Number"" in the following header locations:" & vbCrLf
'// ...and if so, build a string to ask user if we want to modify the opening wb//
For i = 1 To COL_Ranges.Count
strMsg = strMsg & "Sheet: " & COL_Ranges.Item(i).Parent.Name & vbTab & _
" Cell: " & COL_Ranges.Item(i).Address(0, 0) & vbCrLf
Next

strMsg = strMsg & vbCrLf & "Would you like these columns changed to Number format?"

If MsgBox(strMsg, vbYesNo, "Found Header Val") = vbYes Then
'// Run back through the collection, changing the number format of rows 2 //
'// through last row of column we found "Number" in. //
For i = 1 To COL_Ranges.Count
COL_Ranges.Item(i).Offset(1) _
.Resize(COL_Ranges.Item(i).Parent.Rows.Count - 1).NumberFormat = "0"
Next
End If
End If
End If
End Sub

As you can see, I limited the searching to row 1 of each sheet. Also, it does not check for more than one instance of "Number" per sheet.

In closing, a downside I see is that it would format the columns each time the workbook is opened.

Hope that helps,

Mark