Updating a Userform Real Time

[TheScott]

What I am trying to discover is how to load a userform with worksheet data, update the worksheet via the userform and then have the new updated information show up in the userform for the user to see. The updating of the userform is the problem

Attached is an example of what I am doing.

Example AddSub RealTime Update.xlsm

As Always Thanks and Regards

Scott

[mehmetcik]

You should load your Userform Like This:

Private Sub UserForm_Initialize()

Myarray = Range("A6:D8").Value
Myarray2 = Range("C1:C3").Value

For Count = 1 To 3
Me.Controls("Textbox" & Count).Value = Myarray(Count, 4)
Me.Controls("Label" & Count).Caption = Myarray(Count, 1)
Me.Controls("Label" & Count + 10).Caption = Myarray2(Count, 1)
Next

End Sub

Saving It Should be the reverse more or less. Bear with me.

You will Need a command button on the userform.

Private Sub CommandButton1_Click()

For Count = 1 To 3
MyArray(Count, 4) = Me.Controls("Textbox" & Count).Value + Me.Controls("Textbox" & Count + 3).Value - Me.Controls("Textbox" & Count + 6).Value
Me.Controls("Textbox" & Count + 3).Value = ""
Me.Controls("Textbox" & Count + 6).Value = ""
Next

Range("A6:D8").Value = MyArray
End Sub

This may seem like a round about method but it is fast because there is only Three read / write operations to excel involved.

[TheScott]

Thank you for your response. I did as you suggested and changed the initialize sub to the arrays that you created. It appears to work well. Understanding what you did is a bit of a problem however. It would seem that arrays are going to be a particular nemesis of mine. I understand them… I can visualize them (at least to three dimensions) but for the life of me, I cannot determine when to use them nor do I seem to grasp the syntax. This could be a learning step forward but I request that you talk slowly and treat me like a child.

As for the second part; I created the command button and added your code.

I get a ‘sub or function not defined’ message and a halt on this line.

myarray(Count, 4) = Me.Controls("Textbox" & Count).Value + Me.Controls("Textbox" & Count + 3).Value - Me.Controls("Textbox" & Count + 6).Value

It struck me that ‘myarray’ should be defined at the top of the sub but I am not entirely certain how.
I added the line from the initialize sub:

myarray = Range("A6:D8").Value

And got a message of runtime error 13 – type mismatch
Debug halts on the line given above.

myarray(Count, 4) = Me.Controls("Textbox" & Count).Value + Me.Controls("Textbox" & Count + 3).Value - Me.Controls("Textbox" & Count + 6).Value

Regards
Scott

[mehmetcik]

try dim myarray as variant

then

Myarray = Range("A6:D8").Value

should still work.

For large Userforms Arrays are a godsend.

[mehmetcik]

try dim myarray as variant

then

Myarray = Range("A6:D8").Value

should still work.

For large Userforms Arrays are a godsend.

Did you renamecyour textboxes?

[TheScott]

I must be missing something... and no doubt am. If you say it should work I take your word for it.

I started fresh using a copy of the worksheet I uploaded to this site... the copy you would have seen
Commented out my original code in the initalize and added yours copy and paste.
Addee a command button leaving its name default

Results are as I indicated previously
this time I added the Dim statement and code as you indicated ( I think I put it in the right place ) and the results are the same


Capture.PNG

Regards

Scott

[mehmetcik]

I just Tried this:-

Sub Macro1()


Dim MyArray As Variant

For Count = 1 To 12
Cells(Count, 1).Value = Count * 2
Next

MyArray = Range("A1:A12").Value

Range("B1:B12").Value = MyArray
End Sub

It worked with no issues.