I have 15 or so controls on a userform. I want to display a reminder message
to save changes if a change was made to any of the controls on the userform.
I was able to do this by placing a single boolean variable assignment in each
of 15 or so control_keypress events. I would like to get rid all the keypress
events and replace them with a single userform_keypress event. The variable
assignment in the userform_keypress event doesn't work. Any suggestions???
Excel 2002 w/ sp2
Thanks PD

You can do this using a class module ...
there are two parts to the solution. Part 1 is creating the demo form with
the class module and then part 2 is adding a check on closing the form.
So here we go.
1) add a new CLASS MODULE, and name it clsControl
2) add this code to the class module
Option Explicit
Public WithEvents m_txtMyTextkBox As MSForms.TextBox
Public Event Changed(text As String)
Private Sub m_txtMyTextkBox_Change()
PastReply "You changed " & m_txtMyTextkBox.Name & vbLf & _
m_txtMyTextkBox.Value, m_txtMyTextkBox
m_txtMyTextkBox.Tag = "True"
End Sub

3) add a userform with a label (label1) towards the upper right corner, and
a command button (cmdClose) towards the bottom right.
4) add the following code to the form:
Option Explicit
Dim colTextBoxes As New Collection
Dim WithEvents ctTextBox As clsControl
Public ctrl As Control
Private Sub UserForm_Initialize()
Set ctTextBox = New clsControl
Dim index As Long
For index = 1 To 10
Add_A_Control
Next
End Sub
Private Sub Add_A_Control()
Dim TP As Long
TP = 10
For Each ctrl In Controls
If ctrl.Name Like "Text*" Then
TP = TP + ctrl.Height
ctrl.Top = TP
End If
Next
Set ctrl = Me.Controls.Add("Forms.Textbox.1")
With ctrl
.Top = TP + ctrl.Height
.Left = 25
.text = "TextBox " & colTextBoxes.Count
End With
Set ctTextBox = New clsControl
Set ctTextBox.m_txtMyTextkBox = ctrl
colTextBoxes.Add ctTextBox
End Sub
Private Sub cmdClose_Click()
Dim ctrl As Control
Dim OKtoClose As Boolean
OKtoClose = True
For Each ctrl In Me.Controls
If ctrl.Tag = "True" Then
OKtoClose = False
Exit For
End If
Next
If Not OKtoClose Then
OKtoClose = _
MsgBox("Data has changed. Close Anyway?", _
vbYesNo + vbDefaultButton2) = vbYes
End If
If OKtoClose Then
Unload Me
End If
End Sub

5) add a standard code moduke withthis code:
Option Explicit
Sub PastReply(msg As String, cbx As Control)
With UserForm1
.Label1.Caption = msg
Set .ctrl = cbx
End With
End Sub


The method is to add each control to a class that handles the event firing.
The userform begins by adding ten editboxes
You could iterate through existing controls, checking say the name, and the
adding the controls thus selected to the class. The class is then added to
the collection.
When an event fires - in this example the text change event, there's only
one handler to worry about, and thats called by the class.

Much of the code - especially the class module and collection is not mine.
If I could remember who I took it from I'd give full credit.



"Paul D." wrote:

I have 15 or so controls on a userform. I want to display a reminder message
to save changes if a change was made to any of the controls on the userform.
I was able to do this by placing a single boolean variable assignment in each
of 15 or so control_keypress events. I would like to get rid all the keypress
events and replace them with a single userform_keypress event. The variable
assignment in the userform_keypress event doesn't work. Any suggestions???
Excel 2002 w/ sp2
Thanks PD

Whether its your code or not, I gladly thank you for the effort to give me
such a comprehensive reply. PD

"Patrick Molloy" wrote:

You can do this using a class module ...
there are two parts to the solution. Part 1 is creating the demo form with
the class module and then part 2 is adding a check on closing the form.
So here we go.
1) add a new CLASS MODULE, and name it clsControl
2) add this code to the class module
Option Explicit
Public WithEvents m_txtMyTextkBox As MSForms.TextBox
Public Event Changed(text As String)
Private Sub m_txtMyTextkBox_Change()
PastReply "You changed " & m_txtMyTextkBox.Name & vbLf & _
m_txtMyTextkBox.Value, m_txtMyTextkBox
m_txtMyTextkBox.Tag = "True"
End Sub

3) add a userform with a label (label1) towards the upper right corner, and
a command button (cmdClose) towards the bottom right.
4) add the following code to the form:
Option Explicit
Dim colTextBoxes As New Collection
Dim WithEvents ctTextBox As clsControl
Public ctrl As Control
Private Sub UserForm_Initialize()
Set ctTextBox = New clsControl
Dim index As Long
For index = 1 To 10
Add_A_Control
Next
End Sub
Private Sub Add_A_Control()
Dim TP As Long
TP = 10
For Each ctrl In Controls
If ctrl.Name Like "Text*" Then
TP = TP + ctrl.Height
ctrl.Top = TP
End If
Next
Set ctrl = Me.Controls.Add("Forms.Textbox.1")
With ctrl
.Top = TP + ctrl.Height
.Left = 25
.text = "TextBox " & colTextBoxes.Count
End With
Set ctTextBox = New clsControl
Set ctTextBox.m_txtMyTextkBox = ctrl
colTextBoxes.Add ctTextBox
End Sub
Private Sub cmdClose_Click()
Dim ctrl As Control
Dim OKtoClose As Boolean
OKtoClose = True
For Each ctrl In Me.Controls
If ctrl.Tag = "True" Then
OKtoClose = False
Exit For
End If
Next
If Not OKtoClose Then
OKtoClose = _
MsgBox("Data has changed. Close Anyway?", _
vbYesNo + vbDefaultButton2) = vbYes
End If
If OKtoClose Then
Unload Me
End If
End Sub

5) add a standard code moduke withthis code:
Option Explicit
Sub PastReply(msg As String, cbx As Control)
With UserForm1
.Label1.Caption = msg
Set .ctrl = cbx
End With
End Sub


The method is to add each control to a class that handles the event firing.
The userform begins by adding ten editboxes
You could iterate through existing controls, checking say the name, and the
adding the controls thus selected to the class. The class is then added to
the collection.
When an event fires - in this example the text change event, there's only
one handler to worry about, and thats called by the class.

Much of the code - especially the class module and collection is not mine.
If I could remember who I took it from I'd give full credit.



"Paul D." wrote:

I have 15 or so controls on a userform. I want to display a reminder message
to save changes if a change was made to any of the controls on the userform.
I was able to do this by placing a single boolean variable assignment in each
of 15 or so control_keypress events. I would like to get rid all the keypress
events and replace them with a single userform_keypress event. The variable
assignment in the userform_keypress event doesn't work. Any suggestions???
Excel 2002 w/ sp2
Thanks PD

Patrick Molloy wrote:
You can do this using a class module ...
1) add a new CLASS MODULE, and name it clsControl
<<snip
5) add a standard code moduke withthis code:
Option Explicit
Sub PastReply(msg As String, cbx As Control)
With UserForm1
.Label1.Caption = msg
Set .ctrl = cbx
End With
End Sub

I may have a reputation for being a standard module and public variable
sceptic but IMO hard coding a public reference to Userform1 and having
the class call a method in a standard module doesn't fit this scenario.
What if I wanted to use the same class for more than one userform in
the project? What if I used more than one instance of Userform1 in the
project?

I think a better approach would be for the class to raise an event and
for the implementation code (Label1.Caption = msg etc) to appear in the
userform's code module, making the code easier to reuse with the same
and/or other projects Why the standard module approach?

Jamie.

--