Show userform for certain workbooks

Posted by Rhona on February 16, 2001 3:56 AM

Hi,

I have a group of about 100 workbooks containing project details within my organisation all with names in the format DTDxxxx.xls. In one of these workbooks I have created a userform which accepts various data e.g. details of costs, project managers comments which are then written to a protected and hidden sheet within the workbook to serve as an audit trail. Now the form and the macros I've used work fine in the one workbook but I'd like the form to be shown every time a user saves any project workbook (i.e. beginning with DTD). I don't want to have to copy the form and macros to every existing project workbook or have to add it to any new ones, just have something running in the background which will show the form automatically.

Thanks in advance to anyone who can help,

Rhona.

Posted by Dax on February 16, 2001 7:27 AM

Rhona,
Can't give you a detailed answer but here's what I did with a similar problem I had.

Create an add-in that is required in order to open any of your project workbooks (i.e. removes protection). In the addin include your form, any code required to open your workbooks and a class module named AppSet with the following code:-

Public WithEvents XL As Excel.Application

Private Sub XL_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Left(Wb.Name,3)="DTD" Then
UserForm1.Show 'The name of your form
End If
End Sub

In the ThisWorkbook code module of the addin have this macro automatically run when the addin is opened.

Dim NewXL as AppSet
Sub SetUpAdd
NewXL.XL=Application
End Sub

If you then distribute the addin or put it into a shared folder you can have those people who need access to your project workbooks install it.

Dax.