My Add-in causes problem with opening a file twice

  • Thread starter Thread starter masayoshi hayashi
  • Start date Start date
M

masayoshi hayashi

I have found my add-in program cause a problem with opening a file
twice. Here is a symptom:

Click an xls file, open Excel, loads the add-in, but no worksheet.
Click the xls file again and Excel opens the file.

Clicking on Excel.exe short-cut icon results in loading the add-in and
opening a default book1 workbook.

Removing the add-in solves this problem. If you could give me some
hints or directions for solving my add-in problem, I'd be appreciated.

Below is my code in ThisWorkbook module:
----------------------------------------
Option Explicit

Dim InstalledProperly As Boolean
Dim Item As AddIn
Dim wb As Workbook
Dim AddInTitle As String
Dim Toolbar As String

Private Sub Workbook_AddinInstall()
InstalledProperly = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call DeleteToolbar(Toolbar)
End Sub

Private Sub Workbook_Open()
Dim Msg As String

' Check user's excel version for compatibility
Call CheckVersion

If Not ThisWorkbook.IsAddin Then Exit Sub

If Not InAddInCollection(ThisWorkbook) Then

' Add it to the AddIns collection
AddIns.Add Filename:=ThisWorkbook.FullName

' Install it
AddInTitle = GetTitle(ThisWorkbook)
Application.EnableEvents = False
AddIns(AddInTitle).Installed = True
Application.EnableEvents = True

' Inform user
Msg = ThisWorkbook.Name & " has been installed as an add-in. "
Msg = Msg & "Use the Tools Add-Ins command to uninstall it. "
MsgBox Msg, vbInformation, AddInTitle
End If

Splash.Show

' Generating Toolbar
Toolbar = "CrossTab"
Call CreateToolbar(Toolbar)
Call AddButton(Toolbar, 107, "MainControl", "Generate Cross Tabs")
Call AddButton(Toolbar, 459, "Refresh", "Refresh")
Call AddButton(Toolbar, 558, "ShowUFPage", "Set Pages")

' Assigning the default value for Cancel Buttons of userforms.
CancelB = False

'Assigning help.chm file as help
' ThisWorkbook.VBProject.HelpFile = Path & "\Help.chm"
End Sub

Function InAddInCollection(wb) As Boolean
For Each Item In AddIns
If Item.Name = wb.Name Then
InAddInCollection = True
End If
Next Item
End Function

Function GetTitle(wb) As String
GetTitle = ""
For Each Item In AddIns
If Item.Name = wb.Name Then
GetTitle = Item.Title
End If
Next Item
End Function

Private Sub CheckVersion()
If Val(Application.Version) < 9 Then
MsgBox "This add-in works only with Excel 2000 or later"
ThisWorkbook.Close
End If
End Sub
 
I have tested ThisWorkbook module line by line and found that
splash.show caused the problem.

Splash.show ' this causes the problem that a user must click a excel
file twice.

Splash.show vbmodeless ' this solves the problem.

I refered to the link below for the fix. If you refer to John
Walkenbach's Excel 2000 Power Programming with VBA, fix the
corresponding line in Page 371. So is the splash screen page in his
homepage. But I wonder if there is any other solution to this problem
without using vbmodeless, since the splash screen does not close
itself when an xls file is opened with the Macro Warning dialog box
(Macro security set Middle).

Thanks for your attentions.

http://groups.google.com/groups?hl=...c355ea%247285deb0%24a401280a%40phx.gbl&rnum=8
 
Back
Top