User defined data type (losing its data)

L

Ludo

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
 
T

Tim Zych

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
 
L

Ludo

Here's a modification which should help.

' In a module:

PublicTypeMembers
    Name As String
    PreName As String
    Street As String
    ZIPcode As Long
    City As String
    Country As String
EndType

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 Zychhttp://www.higherdata.com
Workbook Compare - Excel data comparison utility


































- Tekst uit oorspronkelijk bericht weergeven -
Click to expand...

Hi Tim,

Thanks a lot, works great.

I played also a bit with the code and found also a way to get around
the problem.
Using the original posted code, i added in the Module1 following below
the TYPE declaration :

Public Member as Members

This seems to work too, even i don't know if this is the right way to
do.
Note that i need the Type members across different Userforms and sub
routines (in the same module)

Regards,
Ludo
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

User-Defined Data TYPE 3
QueryClose event of a form triggering Exit event of a textbox on that form 1
Changing Cells depending on data entered in other cells 1
How do add another code to a current one? 4
change several cells based upon the value of one cell 2
Toggle Buttons 1
Continuous Form Control Setting (Select Case) 0
Option Buttons 0

Top