Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generic Userform class
As I have a lot of userforms I made a generic userform class to get events
of the different forms. This is the code that sets this up: In a Class module called FormClass: ------------------------------------------------------- Option Explicit Public WithEvents objForm As MSForms.UserForm Private Sub objForm_MouseDown(ByVal Button As Integer, _ ByVal Shift As Integer, _ ByVal X As Single, _ ByVal Y As Single) Dim i As Long Dim lContextHelpID As Long Dim arrHelpIDs arrHelpIDs = Array(1000, 1001, 1002, 1004) If Button = 2 Then 'see what form it is For i = 0 To 3 If Forms(i).objForm Is objForm Then lContextHelpID = arrHelpIDs(i) Exit For End If Next If lContextHelpID 0 Then ShowHelp bWebHelp, lContextHelpID End If End If End Sub In a normal module: ------------------------------- Public Forms(22) As New FormClass In the different userform initialize events: ------------------------------------------------------- Set Forms(0).objForm = Me The problem is that I can't get the properties of the userform I want. Ideally I would want the HelpContextID, but the caption would be OK. When I do objForm.HelpContextID I get Runtime error Object doesn't support property or method. When I do objForm.Caption I get an empty string. The above method with looping through the forms array does work, but I would think there should be a better way to do this. RBS |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generic Userform class
Hi Bart,
That's a new idea on me, a public array of withevents Forms class's, as you say it works. Why not set whatever unique form variables you are going to need in each instance, eg in normal module Public myForms(22) As FormClass ' not as New in the initialize event of each form Private Sub UserForm_Initialize() ' 0 being the unique index for this form, 1, 2 etc in others ' or set & use a local level var of Ubound(myForms) ' or, iso array, add to a collection Set myForms(0) = New FormClass Set myForms(0).objForm = Me myForms(0).propHelpID = Me.HelpContextID End Sub Private Sub UserForm_Terminate() 'assuming we know this form's location in the array is 0 Set myForms(0) = Nothing End Sub in FormClass Public WithEvents objForm As MSForms.UserForm Dim lContextHelpID As Long Public Property Let propHelpID(n As Long) lContextHelpID = n End Property Private Sub objForm_MouseDown(ByVal Button As Integer, _ ByVal Shift As Integer, _ ByVal X As Single, _ ByVal Y As Single) MsgBox lContextHelpID ''not sure why we can't do simply this - 'MsgBox objForm.HelpContextID ''looking at objForm in locals HelpContextID isn't there End Sub In passing, as it's unlikely (?) all your forms will always be loaded at the same time I think I would declare New only when needed, and destroy in the form's terminate. Also, unless you particularly need the public array for other purposes, why not declare a single ref to the class in each form. Then when the form is unloaded you don't have to worry about cleaning up objects as you go. Regards, Peter T "RB Smissaert" wrote in message ... As I have a lot of userforms I made a generic userform class to get events of the different forms. This is the code that sets this up: In a Class module called FormClass: ------------------------------------------------------- Option Explicit Public WithEvents objForm As MSForms.UserForm Private Sub objForm_MouseDown(ByVal Button As Integer, _ ByVal Shift As Integer, _ ByVal X As Single, _ ByVal Y As Single) Dim i As Long Dim lContextHelpID As Long Dim arrHelpIDs arrHelpIDs = Array(1000, 1001, 1002, 1004) If Button = 2 Then 'see what form it is For i = 0 To 3 If Forms(i).objForm Is objForm Then lContextHelpID = arrHelpIDs(i) Exit For End If Next If lContextHelpID 0 Then ShowHelp bWebHelp, lContextHelpID End If End If End Sub In a normal module: ------------------------------- Public Forms(22) As New FormClass In the different userform initialize events: ------------------------------------------------------- Set Forms(0).objForm = Me The problem is that I can't get the properties of the userform I want. Ideally I would want the HelpContextID, but the caption would be OK. When I do objForm.HelpContextID I get Runtime error Object doesn't support property or method. When I do objForm.Caption I get an empty string. The above method with looping through the forms array does work, but I would think there should be a better way to do this. RBS |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generic Userform class
Hi Peter,
Thanks, that looks better indeed. I will try it out and let you know. Would you know my other question about the order of controls in a For Each loop? RBS "Peter T" <peter_t@discussions wrote in message ... Hi Bart, That's a new idea on me, a public array of withevents Forms class's, as you say it works. Why not set whatever unique form variables you are going to need in each instance, eg in normal module Public myForms(22) As FormClass ' not as New in the initialize event of each form Private Sub UserForm_Initialize() ' 0 being the unique index for this form, 1, 2 etc in others ' or set & use a local level var of Ubound(myForms) ' or, iso array, add to a collection Set myForms(0) = New FormClass Set myForms(0).objForm = Me myForms(0).propHelpID = Me.HelpContextID End Sub Private Sub UserForm_Terminate() 'assuming we know this form's location in the array is 0 Set myForms(0) = Nothing End Sub in FormClass Public WithEvents objForm As MSForms.UserForm Dim lContextHelpID As Long Public Property Let propHelpID(n As Long) lContextHelpID = n End Property Private Sub objForm_MouseDown(ByVal Button As Integer, _ ByVal Shift As Integer, _ ByVal X As Single, _ ByVal Y As Single) MsgBox lContextHelpID ''not sure why we can't do simply this - 'MsgBox objForm.HelpContextID ''looking at objForm in locals HelpContextID isn't there End Sub In passing, as it's unlikely (?) all your forms will always be loaded at the same time I think I would declare New only when needed, and destroy in the form's terminate. Also, unless you particularly need the public array for other purposes, why not declare a single ref to the class in each form. Then when the form is unloaded you don't have to worry about cleaning up objects as you go. Regards, Peter T "RB Smissaert" wrote in message ... As I have a lot of userforms I made a generic userform class to get events of the different forms. This is the code that sets this up: In a Class module called FormClass: ------------------------------------------------------- Option Explicit Public WithEvents objForm As MSForms.UserForm Private Sub objForm_MouseDown(ByVal Button As Integer, _ ByVal Shift As Integer, _ ByVal X As Single, _ ByVal Y As Single) Dim i As Long Dim lContextHelpID As Long Dim arrHelpIDs arrHelpIDs = Array(1000, 1001, 1002, 1004) If Button = 2 Then 'see what form it is For i = 0 To 3 If Forms(i).objForm Is objForm Then lContextHelpID = arrHelpIDs(i) Exit For End If Next If lContextHelpID 0 Then ShowHelp bWebHelp, lContextHelpID End If End If End Sub In a normal module: ------------------------------- Public Forms(22) As New FormClass In the different userform initialize events: ------------------------------------------------------- Set Forms(0).objForm = Me The problem is that I can't get the properties of the userform I want. Ideally I would want the HelpContextID, but the caption would be OK. When I do objForm.HelpContextID I get Runtime error Object doesn't support property or method. When I do objForm.Caption I get an empty string. The above method with looping through the forms array does work, but I would think there should be a better way to do this. RBS |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generic Userform class
"RB Smissaert" wrote in message ... Would you know my other question about the order of controls in a For Each loop? I've just seen subject "Order of controls in For Each Loop", I had a go! Regards, Peter T |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
refedits in a userform control class | Excel Programming | |||
Generic Macro | Excel Programming | |||
RaiseEvent from a class contained in a 2nd class collection? | Excel Programming | |||
Userform with template class module problem | Excel Programming | |||
Userform with template class module problem | Excel Programming |