user form

[aoc]

hi,

I have the attached file. When I copy the sheet into another new workbook below code does not work because of not seeing user form. I tried to put the modules and form into personal.xls, but it did not work.What should I do?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.EnableEvents = False

If Target.Address = "$D$1" Then
Target.Value = Format(Date, "dd.mmmm.dddd.yyyy")
End If

If Target.Address = "$D$5" Then
userform.Show
End If

If Target.Address = "$D$6" Then
userform.Show
End If
Application.EnableEvents = True

End Sub

[lucas]

Well since there is only one sheet in the workbook why not just copy the whole workbook.

You could export the form from the first workbook and import it into the second workbook also.

If you want it to work on every workbook(which I don't see as the case because the userform is called from specific sheet calls) you could create an addin.

[aoc]

hi,

I created an add-in and activated it but I still get the same error "object required" error line is UserForm.Show

[lucas]

Hi OSMan,
I don't think it will be quite that simple since your calling it from sheet code...could you post your addin?

[aoc]

you can try the attached add-in

[lucas]

I don't think it's normal to run a userform in the personal.xls from a sheet change event but I got it working like this:
1 Put your form in personal
2 Add a module in personal with userform.show
[VBA]Option Explicit
Sub ShowIt()
UserForm.Show
End Sub
[/VBA]

Then In the workbook with the worksheet change event try using this.
[VBA]Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.EnableEvents = False

If Target.Address = "$D$1" Then
Target.Value = Format(Date, "dd.mmmm.dddd.yyyy")
End If
If Target.Address = "$D$5" Then
Run "PERSONAL.XLS!ShowIt"
End If

If Target.Address = "$D$6" Then
Run "PERSONAL.XLS!ShowIt"
End If
Application.EnableEvents = True

End Sub[/VBA]
You can call it the same way from an addin but you will have to call the macro in the addin that calles the userform.show.