A problem with user defined data type

What to say but: FANTASTIC !!
:clap: :clap: :clap: :clap: :clap: :clap: :clap: :clap: :clap:

tstom, i really cannot thank you enough for your help.

Now i must study this redefined code out, and maybe come back with some questions.


Greetings :friends:

hi tstom,
i can't see where is DataX class declared.
In the class module i see DataX properties with Get & Let methods defined, but there's no DataX class name ?? :wot

As i mentioned before i forgot the most of OO syntax, but still i'm a bit surprised that i don't have to explicitely call Get & Let methods within the OrderForm module. Somehow they are called implicitely - preety nifty (but how?). :think:

Thanks

How to make chosen DataXCollection(Product.text) /from Product_Change() Sub/ public ?

because i need:
dx.ColorX
dx.SizeX
arrays available outside of 'Private Sub Product_Change()' to print
some excel fields based on these 2 arrays.

I tried to simply change 'Private' to 'Public', and 'Dim' to 'Public' dx As DataX, but that doesn't work - undefined variable or so.

Thanks in advance

i've resolved this issue with:

Public DataXCollection As Collection
Public dx As DataX
(and removed Dims dx)

I ran straight into the twilight zone!

I have Private Sub 'Product_Change' (by tstom) that isn't called by any other procedure, but it's working ??
[vba]Option Explicit

Private DataXCollection As Collection

Private Sub Frame1_Click()

End Sub

Private Sub Product_Change()
Dim dx As DataX
'get the correct DataX object reference from your collection
Set dx = DataXCollection(Product.Text)
PriceInput.Text = dx.PriceX
Color.List = dx.ColorX
Color.ListIndex = 0
Size.List = dx.SizeX
Size.ListIndex = 0
End Sub

Private Sub UserForm_Initialize()
Call ArrayFill
End Sub

Private Sub ArrayFill()
Dim r As Range, dx As DataX

Set DataXCollection = New Collection
Set r = Worksheets("SheetX").Range("A1")

Do Until r = ""
'make sure we create a new instance of your custom type on every iteration
Set dx = New DataX
dx.CodeX = r.Text
dx.PriceX = r.Offset(, 1).Value
dx.ColorX = Application.Transpose(Range(r.Offset(1), r.Offset(1).End(xlDown)))
dx.SizeX = Application.Transpose(Range(r.Offset(1, 1), r.Offset(1, 1).End(xlDown)))
Product.AddItem dx.CodeX
'add this reference of an instance of your custom type to
'to the DataXCollection collection for future use
'use the product code as the key
DataXCollection.Add dx, dx.CodeX
Set r = r.Offset(, 2)
Loop

Product.Text = "Select Product Code"
End Sub[/vba] How comes?

Apparently, there are more things in heaven and earth, than i dreamt of..

Product is a control on the form (a textbox?), and that procedure is invoked whenever a change is made to the textbox.

Thanks xld.

And here is another (really big) one.
Whatever i do, i can?t make this code to put a value in cell (7, 1).

If i change it to (8, 1) or (7, 10) or whatever else, no problem at all, but it is absolutely impossible to put anything in (7, 1) ???
[vba]Private Sub FillTotal()

Dim j As Integer
Dim f As Integer
? siz is Public defined string variable, set something in the code

j = Worksheets("SheetY").Cells(2, 7).Value 'number of previous inputs in the ?TOTAL? sheet

If j = 0 Then ' 1st input
Worksheets("TOTAL").Cells(7, 1).Value = ?whatever!!!!!!? ?! ! ! ! ! ! !
Worksheets("TOTAL").Cells(7, 2).Value = Amount.Text

Worksheets("TOTAL").Cells(7, 2 + kk * 2 - 1).Value = siz
Worksheets("TOTAL").Cells(7, 2 + kk * 2).Value = Amount.Text

Exit Sub
End If

For f = 1 To j

If (Worksheets("TOTAL").Cells(6 + f, 1).Value = Product.Text & Color.Text) Then
Worksheets("TOTAL").Cells(6 + f, 2).Value = Worksheets("TOTAL").Cells(6 + f, 2).Value + Amount.Text

Worksheets("TOTAL").Cells(6 + f, 2 + kk * 2 - 1).Value = siz
Worksheets("TOTAL").Cells(6 + f, 2 + kk * 2).Value = Worksheets("TOTAL").Cells(6 + f, 2 + kk * 2).Value + Amount.Text
Exit Sub
End If
Next f

'And if that combination (Product/Color) doesn?t exist, then populate first empty cell in the column

Worksheets("TOTAL").Cells(7 + j, 1).Value = Product.Text & Color.Text ?entry input for that product
Worksheets("TOTAL").Cells(7 + j, 2).Value = Amount.Text
Worksheets("TOTAL").Cells(7 + j, 2 + kk * 2 - 1).Value = siz
Worksheets("TOTAL").Cells(7 + j, 2 + kk * 2).Value = Amount.Text

kk = 0
siz = ""

End Sub[/vba]:banghead:

Hi SMC,
I can't offer a solution, but re your code, you are better to make use of With statements rather than repeating the sheet name.
[VBA]
Private Sub FillTotal()
Dim j As Integer
Dim f As Integer
'siz Is Public defined String variable, Set something In the code
j = Worksheets("SheetY").Cells(2, 7).Value 'number of previous inputs in the ‘TOTAL’ sheet
With Worksheets("TOTAL")
If j = 0 Then ' 1st input
.Cells(7, 1).Value = 10 '“whatever!!!!!!” ‘! ! ! ! ! ! !
.Cells(7, 2).Value = Amount.Text
.Cells(7, 2 + kk * 2 - 1).Value = siz
.Cells(7, 2 + kk * 2).Value = Amount.Text
Exit Sub
End If
For f = 1 To j
If (.Cells(6 + f, 1).Value = Product.Text & Color.Text) Then
.Cells(6 + f, 2).Value = .Cells(6 + f, 2).Value + Amount.Text
.Cells(6 + f, 2 + kk * 2 - 1).Value = siz
.Cells(6 + f, 2 + kk * 2).Value = .Cells(6 + f, 2 + kk * 2).Value + Amount.Text
Exit Sub
End If
Next f
'And if that combination (Product/Color) doesn’t exist, then populate first empty cell in the column
.Cells(7 + j, 1).Value = Product.Text & Color.Text 'entry Input For that product
.Cells(7 + j, 2).Value = Amount.Text
.Cells(7 + j, 2 + kk * 2 - 1).Value = siz
.Cells(7 + j, 2 + kk * 2).Value = Amount.Text
End With
kk = 0
siz = ""
End Sub

[/VBA]

Thanks mdmackillop for the useful suggestion.

I found (as usual stupid) mistake in my code.

I have no doubt that i'll come again soon, with a help request.

:giggle

Quote:

---

Originally Posted by xld

Ditch the UDT, they are severely flawed and n ot worth bothering with. Create a simple class instead.

---

Bob,

I'm just curious to know how severe is "severely flawed"? Do you have an article about this on your site? I didn't see one.

My only concern about this is a project I developed for my customer where I implemented a UDT. I'm going to switch it to a simple class as you suggested (as it's still at its tail end of the development stage), but I'm wondering what flaws it has? And if it's too many to mention, do you have a reference article of some kind?

Thanks

I need a command to copy an entire sheet into some variable, in order that variable to paste in the same sheet later, if necessary (classical undo functionality).

Something like:

[vba]Public w As Worksheet

Private Sub CopySheet()

w = Worksheets("test")
...
...
Worksheets("test") = w 'if necessary

End Sub[/vba]

Thanks

I decided to copy Range("A1:S200") instead of entire Sheet, because of memory consumption.
But the following code copies just data without the formatting.
[vba]Public vx as Variant

Private Sub CopySheet()
vx = Worksheets("test-1").Range("A1:200")
Worksheets("test-2").Range("A1:200") = vx
End Sub[/vba]
and i need the formatting.

Thanks