Array Type Mismatch

[brorick]

I have tried to resolve the issue with no luck. Any ideas why I would get a type mismatch. Here are the facts.

UserForm TextBox Controls and Values

txtID = 225
txtDate = 09/09/2001
txtName = John Doe


Dim MyData as Range
Dim VaInfo as Variant


Public Sub ChangeInfo_Click()

Call MyChanges

With Range("MyInfo")
Set MyData = .Rows(2)
End With

End Sub


Public Sub MyChanges()

VaInfo(1, 1) = txtID.Value
VaInfo(1, 2) = txtDate.Value
VaInfo(1, 3) = txtName.Value

MyData.Value = VaInfo
End Sub

When step through the code I notice the data is correct after the equal sign under the Public Sub MyChanges(). But I get the following error when I point on the left side of the equal sign.

VaInfo(1,1) = <Type Mismatch>


Does anyone have any ideas? :doh:

[GTO]

Greetings Brorick,

As to the jam-up, I believe it is because you did not dimension the array. Something like: Dim VaInfo(1 to 1, 1 to 3)

MyData.Value = VaInfo will fail here, as it is not yet Set.

I didn't see the need (as of the supplied info leastwise) for a two-dimensional array, so this example just uses a single-dimension array. Assumes the named range is three columns by xx rows.

Option Explicit
Dim MyData As Range
Dim VaInfo(1 To 3) As Variant

Public Sub cmdChangeInfo_Click()

Call MyChanges

With Range("MyInfo")
Set MyData = .Rows(2)
MyData.Value = VaInfo
End With

Unload Me
End Sub

Public Sub MyChanges()
VaInfo(1) = txtID.Value
VaInfo(2) = txtDate.Value
VaInfo(3) = txtName.Value
End Sub


Hope this helps,

Mark

PS - Is there a reason for the subs to be public? I may well be guessing wrong, but these are in the UFs module I presume.

[brorick]

GTO, thank you very much for your reply. The example I provided was a slim down example of the real thing, but it did produce the same results. I am looking forward to applying your example to see if I can get it to work. I will let you know. Thanks again.

[GTO]

GTO, thank you very much for your reply. The example I provided was a slim down example of the real thing, but it did produce the same results. I am looking forward to applying your example to see if I can get it to work. I will let you know. Thanks again.

Very happy to help.

Happy coding:thumb

Mark

[brorick]

GTO. Perfection. It worked. Thank you. The layout you provided makes sense. I do have a quick question. I haven't worked with Unload Me before. How does it work within this code?

[GTO]

'Me' in this case is the userform. Check the help topic, but in short, Unload literally unloads the userform, which means not only is it hidden/dismissed, but any variable local to the userform will be dropped from memory. Thus, if you did another Userform1.Show, any textboxes that you had filled in would be empty, etc.

If Userform1.Hide was used, any values stored under the userform remain in memory.

Not my best explanation, but hopefully of some help...

Mark

[brorick]

Great. I get it. Thanks once again for helping me with my issue. :beerchug: