Here's a modification which should help.
' In a module:
Public Type Members
Name As String
PreName As String
Street As String
ZIPcode As Long
City As String
Country As String
End Type
Sub SaveData(mbr As Members)
Range("a2").Select
ActiveCell.Value = mbr.Name
Selection.Offset(0, 1).Value = mbr.PreName
Selection.Offset(0, 2).Value = mbr.Street
Selection.Offset(0, 3).Value = mbr.ZIPcode
Selection.Offset(0, 4).Value = mbr.City
Selection.Offset(0, 5).Value = mbr.Country
End Sub
' In the userform:
Private mbr As Members
Private Sub CommandButton1_Click()
' Pass the local instance of Members (with values) to the procedure
Call Module1.SaveData(mbr)
End Sub
Private Sub TextBox1_Change()
Me.TextBox1.Value = UCase(Me.TextBox1.Value)
End Sub
Private Sub TextBox1_Enter()
Me.TextBox1.BackColor = RGB(255, 255, 0)
End Sub
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
mbr.Name = TextBox1.Value
Me.TextBox1.BackColor = RGB(255, 255, 255)
End Sub
Private Sub TextBox2_Change()
Me.TextBox2.Value = UCase(Me.TextBox2.Value)
End Sub
Private Sub TextBox2_Enter()
Me.TextBox2.BackColor = RGB(255, 255, 0)
End Sub
Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
mbr.PreName = TextBox2.Value
Me.TextBox2.BackColor = RGB(255, 255, 255)
End Sub
Private Sub TextBox3_Change()
Me.TextBox3.Value = UCase(Me.TextBox3.Value)
End Sub
Private Sub TextBox3_Enter()
Me.TextBox3.BackColor = RGB(255, 255, 0)
End Sub
Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
mbr.Street = TextBox3.Value
Me.TextBox3.BackColor = RGB(255, 255, 255)
End Sub
Private Sub TextBox4_Enter()
Me.TextBox4.BackColor = RGB(255, 255, 0)
End Sub
Private Sub TextBox4_Exit(ByVal Cancel As MSForms.ReturnBoolean)
mbr.ZIPcode = TextBox4.Value
Me.TextBox4.BackColor = RGB(255, 255, 255)
End Sub
Private Sub TextBox5_Change()
Me.TextBox5.Value = UCase(Me.TextBox5.Value)
End Sub
Private Sub TextBox5_Enter()
Me.TextBox5.BackColor = RGB(255, 255, 0)
End Sub
Private Sub TextBox5_Exit(ByVal Cancel As MSForms.ReturnBoolean)
mbr.City = TextBox5.Value
Me.TextBox5.BackColor = RGB(255, 255, 255)
End Sub
Private Sub TextBox6_Change()
Me.TextBox6.Value = UCase(Me.TextBox6.Value)
End Sub
Private Sub TextBox6_Enter()
Me.TextBox6.BackColor = RGB(255, 255, 0)
End Sub
Private Sub TextBox6_Exit(ByVal Cancel As MSForms.ReturnBoolean)
mbr.Country = TextBox6.Value
Me.TextBox6.BackColor = RGB(255, 255, 255)
End Sub
--
Regards,
Tim Zych
http://www.higherdata.com
Workbook Compare - Excel data comparison utility
"Ludo" wrote in message
...
Hi,
Sorry, but posted this first in the wrong newsgroup
(microsoft.public.excel)
I have a problem with my User-defined data type (UDDT).
I created a userform with some textboxes and place this data into my
UDDT on the TextBox_Exit event.
Once all the textboxes are filled with data, would i like to transfer
the data contained into the UDDT into a worksheet by a click on the
CommandButton1.
And here occurs my problem, the UDDT is empty when i place its
contents in the worksheet.
What i'm i doing wrong?
Do i need to pass the values as an argument, even i declared the TYPE
as PUBLIC?
If so, what's the right syntax?
Or is it impossible to use the UDDT in a Userform?
I'm using XL2000 SP3
the code i use is :
-------------------------------------------------
the ' Thisworkbook' module
Sub Workbook_open()
UserForm1.Show
End Sub
-------------------------
---------------------------------------------
the module1 code
Public Type Members
Name As String
PreName As String
Street As String
ZIPcode As Long
City As String
Country As String
End Type
-------------------------------------------------
Sub SaveData()
Dim Member As Members
Range("a2").Select
ActiveCell.Value = Member.Name <<< Member.Name = "", and the
following lines are "" too
Selection.Offset(0, 1).Value = Member.PreName
Selection.Offset(0, 2).Value = Member.Street
Selection.Offset(0, 3).Value = Member.ZIPcode
Selection.Offset(0, 4).Value = Member.City
Selection.Offset(0, 5).Value = Member.Country
End Sub
-----------------------------------------------
---------------------------------------------
the userform1 code
Private Sub CommandButton1_Click()
SaveData
Unload Me
End Sub
Private Sub TextBox1_Change()
Me.TextBox1.Value = UCase(Me.TextBox1.Value)
End Sub
Private Sub TextBox1_Enter()
Me.TextBox1.BackColor = RGB(255, 255, 0)
End Sub
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim Member As Members
Member.Name = TextBox1.Value
Me.TextBox1.BackColor = RGB(255, 255, 255)
End Sub
Private Sub TextBox2_Change()
Me.TextBox2.Value = UCase(Me.TextBox2.Value)
End Sub
Private Sub TextBox2_Enter()
Me.TextBox2.BackColor = RGB(255, 255, 0)
End Sub
Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim Member As Members
Member.PreName = TextBox2.Value
Me.TextBox2.BackColor = RGB(255, 255, 255)
End Sub
Private Sub TextBox3_Change()
Me.TextBox3.Value = UCase(Me.TextBox3.Value)
End Sub
Private Sub TextBox3_Enter()
Me.TextBox3.BackColor = RGB(255, 255, 0)
End Sub
Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim Member As Members
Member.Street = TextBox3.Value
Me.TextBox3.BackColor = RGB(255, 255, 255)
End Sub
Private Sub TextBox4_Change()
End Sub
Private Sub TextBox4_Enter()
Me.TextBox4.BackColor = RGB(255, 255, 0)
End Sub
Private Sub TextBox4_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim Member As Members
Member.ZIPcode = TextBox4.Value
Me.TextBox4.BackColor = RGB(255, 255, 255)
End Sub
Private Sub TextBox5_Change()
Me.TextBox5.Value = UCase(Me.TextBox5.Value)
End Sub
Private Sub TextBox5_Enter()
Me.TextBox5.BackColor = RGB(255, 255, 0)
End Sub
Private Sub TextBox5_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim Member As Members
Member.City = TextBox5.Value
Me.TextBox5.BackColor = RGB(255, 255, 255)
End Sub
Private Sub TextBox6_Change()
Me.TextBox6.Value = UCase(Me.TextBox6.Value)
End Sub
Private Sub TextBox6_Enter()
Me.TextBox6.BackColor = RGB(255, 255, 0)
End Sub
Private Sub TextBox6_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim Member As Members
Member.Country = TextBox6.Value
Me.TextBox6.BackColor = RGB(255, 255, 255)
End Sub
---------------------------------------
Regards,
Ludo