Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > User defined data type (losing its data)
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 74
Default 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


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 389
Default 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




Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 74
Default 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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Wrong data type returned from user-defined function hooroy63 Excel Programming 9 January 13th 09 11:15 AM
Dictionary object: Error assigning user defined data type to item Paul Urbanus Excel Programming 2 December 1st 05 04:21 AM
Help: Compile error: type mismatch: array or user defined type expected lvcha.gouqizi Excel Programming 1 October 31st 05 08:20 PM
User-defined data type; Error: Only User-defined types... tiger_PRM Excel Programming 1 July 18th 04 03:32 PM
User defined data type augustus108 Excel Programming 1 April 10th 04 05:11 PM


All times are GMT +1. The time now is 07:58 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement