How to Update Userform Objects with the Data from an Array, VBA

Question

This is a follow up question to a previous one that I posted (see here): https://stackoverflow.com/q/64866771/14651009

Essentially, I have a VBA powered userform in excel where folks will enter data for railcars as they get loaded at a certain location (at 5 "spots")

Sometimes they'll have to move that railcar to a different spot. In which case I want them to be able to keep all the information on the railcar from the first/original entry. On the userform, the new spot should have all the data from the original spot, and the original spot should be empty once the code is executed.

As an aside, I'm working with 5 load spots, and 14 pieces of information for each load spot. I only want to carry over the first 7 pieces of information when a re-spot happens (index 0-6)

I've done two things to accomplish this, first I've established a 2D array of all data for all 5 spots (per recommendation of another Stack Overflow user, thanks Tim Williams!):

Dim spots(1 To 5, 0 To 14), n As Long, i As Long
'Fill spot arrays from the userform:
For n = 1 To 5
  spots(n, 0) = UserForm.Controls("ProductSelectionBox_Spot" & n).Value
  spots(n, 1) = UserForm.Controls("Spot" & n & "_ProductID").Value
  spots(n, 2) = UserForm.Controls("BatchNumber_Spot" & n).Value
  spots(n, 3) = UserForm.Controls("CarNumber_Spot" & n).Value
  spots(n, 4) = UserForm.Controls("Pounds_Spot" & n).Value
    ...
  spots(n, 14) = UserForm.Controls("TotalLoadTime_Spot" & n).Value
Next n

Then, I use a for loop to replace indices 0 - 6, and empty all entries from the original spot:

Dim fromSpot As Long
Dim toSpot As Long
fromSpot = Userform.OriginalSpotSelector.Value
toSpot= UserForm.DestinationSpotSelector.Value
'Replace function
For n = 0 to 6
    spots(toSpot, n) = spots(fromSpot, n)
Next n
'Empty original spot function
For n = 0 to 14
    spots(fromSpot, n) = Empty
Next n

This absolutely works like a charm as far as changing the array values goes. Using the following check:

a = MsgBox(spots(toSpot, 1), vbOKOnly, "Test")

I can print a message box that shows me that the destination spot (toSpot) array value is now the former original spot (fromSpot) value. The only problem is, the Userform itsself doesn't have any updated values once the code is executed.

In my original question, one commenter summarized the problem:

"You need to set a procedure to update userform objects with the data from the array"

I spoke with a colleague of mine and he said that the array is essentially just a copy of the userform values, and I'd have to utilize pointers in order to update the values on the UserForm objects themselves. I've done some research and am still at a loss. Do I need to use pointers to update these userform objects? Is there a more simple method? If not, how do I apply pointers in this particular instance?

It's just the reverse of the first bit of code, assigning the array elements to the controls. — SJR
– SJR, Commented Nov 23, 2020 at 19:07
Oh dang is it really that easy? Just essentially putting what's on the left side of the equals sign on the right side and vice versa? — Cody Hasselbring
– Cody Hasselbring, Commented Nov 23, 2020 at 19:07
Yes that's pretty much it. You will end up with a "GetSpots" function which returns the 2D array, and a "SetSpots" sub which accepts the 2D array and populates the userform from the array. — Tim Williams
– Tim Williams, Commented Nov 23, 2020 at 19:10
Oh sweet! Sounds like I just made the solution overly complicated in my mind. I gave it a shot and it worked! will post that as the answer. Thanks all! — Cody Hasselbring
– Cody Hasselbring, Commented Nov 23, 2020 at 20:59

Cody Hasselbring · Accepted Answer · 2020-11-23 21:18:05Z

1

Okay turns out the solution was pretty easy! All I had to do was essentially reverse the first block of code to re-assign the userform object values to the array values, like this:

For n = 1 To 5
    Userform.Controls("ProductSelectionBox_Spot" & n).Value = spots(n, 0)
    Userform.Controls("Spot" & n & "_GMID").Value = spots(n, 1)
    Userform.Controls("BatchNumber_Spot" & n).Value = spots(n, 2)
    Userform.Controls("CarNumber_Spot" & n).Value = spots(n, 3)
          ...
    Userform.Controls("TotalLoadTime_Spot" & n).Value = spots(n, 14)
Next n

answered Nov 23, 2020 at 21:18

Cody Hasselbring

134 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to Update Userform Objects with the Data from an Array, VBA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related