User-Defined Data TYPE

L

Ludo

Hi,

I have a problem with my User-defined data type (UDDT).

I created a userform with some textboxes and would place this data
into my UDDT.
Once all the entry textboxes are filled with data, would i like to
transfer the data contained into the UDDT into a worksheet.
And here occurs my problem, the UDDT is empty.
What i'm i doing wrong?
the code i use is :

the ' Thisworkbook' module

Sub Auto_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
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
 
D

Don Guillett

Auto_open must reside in a REGULAR module and is not always reliable.
Instead use the Workbook_open in the ThisWorkbook module.
 
L

Ludo

Changed as mentioned in the message, but this didn't solve the
problem.

I think that the problem lies in passing the arguments from the
userform to the sub in a regular module, but i can't find the right
syntax for it, i keep on getting error messages.

Ludo
 
D

David Heaton

Changed as mentioned in the message, but this didn't solve the
problem.

I think that the problem lies in passing the arguments from the
userform to the sub in a regular module, but i can't find the right
syntax for it, i keep on getting error messages.

Ludo





- Show quoted text -


Ludo,


You UDDT is only declared in the SaveData sub. Which keeps it local
to that sub .

Put this in your Module declarations instead of the SaveData sub.

Global Member as Members



Personally I alway use Option Explicit in the General section to catch
these things.


Regards

David
 

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

Top