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
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