Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Shaun E on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Strange behaviour - Passing Values between Array & userform textboxes

Status
Not open for further replies.

Chats

Technical User
Mar 12, 2002
88
GB
Hi,

I'm relatively new to VBA and this is driving me potty - I hope somebody can help!

In Excel 2000, I have a VBA userform with a series of textboxes and an array.

The reason for doing this is that I want to have a userform which allows a user to enter up to 10 rows of data which will eventually be stored as 10 rows on a spreadsheet. On the userform itself I only want to show 4 rows, and have the user scroll up and down through the data which has been entered.

So I have a userform with 4 rows of textboxes and a scrollbar. The problem is that it is just not behaving as I would expect no matter what I try.

I have pasted my code to the end of this message to show what I am doing. What is happening is that when I call this procedure with the command UpdateAttendee(0) and with UpdateAttendee(1) I get the same answer back in my messagebox even though the contents of rows 1&2 on my form are different.

The problem seems to be caused by the calculations on RowNum where I have = RowNum(0) = 0 + MyRow etc. If I remove this calculation and type a fixed value instead it all works OK - trouble is that I need to be able to perform a calculation on RowNum to allow the user to scroll up and down the list.

Here's my code - any help will be really appreciated! I have only pasted the bit that doesn't work, the array is defined above the 1st procedure to make it available to all procedures in the module.
Thanks,
Ade

Private Sub UpdateAttendee(MyRow As Integer)
' Update Attendee array with current values from userform fields


Dim RowNum(0 To 3)
RowNum(0) = 0 + MyRow
RowNum(1) = 1 + MyRow
RowNum(2) = 2 + MyRow
RowNum(3) = 3 + MyRow

'VisitAttendee is the array AttendeeSurname0 etc are textbox fields
VisitAttendee(RowNum(0), 0) = AttendeeSurname0
VisitAttendee(RowNum(1), 0) = AttendeeSurname1
VisitAttendee(RowNum(2), 0) = AttendeeSurname2
VisitAttendee(RowNum(3), 0) = AttendeeSurname3


Dim Message As String
Message = VisitAttendee(MyRow, 0)

MsgBox Message



End Sub
 
Hi Chats,

Regardless of how you call your Function, you are sourcing your array elements from the same Textboxes on the Form. I'm not sure what it is that you are scrolling so I'm not entirely sure how to advise you to proceed, but do be aware that there isn't really any concept of control arrays in Userforms.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.
 
Chats said:
RowNum(0) = 0 + MyRow
...
VisitAttendee(RowNum(0), 0) = AttendeeSurname0
...
Message = VisitAttendee(MyRow, 0)
Message will always be equal to AttendeeSurname0, despit the value of MyRow, as RowNum(0) is equal to MyRow.


Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
PHV,

I'm not quite getting what you mean - why is RowNum(0) equal to MyRow?

Ade
 
Because that: RowNum(0) = 0 + MyRow

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top