PDA

View Full Version : user form



aoc
06-04-2007, 12:47 PM
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
06-04-2007, 12:54 PM
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
06-04-2007, 01:16 PM
hi,

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

lucas
06-04-2007, 01:58 PM
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
06-05-2007, 01:50 PM
you can try the attached add-in

lucas
06-05-2007, 05:17 PM
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
Option Explicit
Sub ShowIt()
UserForm.Show
End Sub


Then In the workbook with the worksheet change event try using this.
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
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.