ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   User defined data type (losing its data) (https://www.excelbanter.com/excel-programming/428601-user-defined-data-type-losing-its-data.html)

Ludo
User defined data type (losing its data)
 

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

Tim Zych
User defined data type (losing its data)
 
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

Ludo
User defined data type (losing its data)
 
On 18 mei, 00:03, "Tim Zych" <tzych@nospam at earthlink dot net
wrote:
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

"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 datatype(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 theTYPE
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

PublicTypeMembers
* *Name As String
* *PreName As String
* *Street As String
* *ZIPcode As Long
* *City As String
* *Country As String
EndType
-------------------------------------------------
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- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

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


All times are GMT +1. The time now is 09:12 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com