Update a worksheet from a UserForm (97/2k)

Topic

New Reply

I have a userform containing seven textboxes into which customer details are input. I wish for these details to add to the bottom of a list on a worksheet on clicking a command button. The userform would now show with the text boxes empty, ready for the next customer details and so on……
I thought an efficient way would be to create a collection containing the text box values as items which could then be referenced through code. The worksheet could then be updated with these values.
Would this be an efficient method or can anyone advise further.
BTW. The help section for Excel VBA is impenetrable when it comes to Collections.

Cheers

Rob

Reply | Quote

Replies

In this case, I don’t think a collection has advantages. Perhaps an array will be easier:

Dim arrValues(1 To 7)
Dim rng As Range
Dim i As Integer
For i = 1 To 7
arrValues(i) = Me.Controls(“Text” & i)
Next i
Set rng = Range(“A1:G1”)
rng = arrValues
Set rng = Nothing

Note: do you know about Data/Form…? It seems to be a built-in way of doing what you want…

Reply | Quote

Hi Hans,
I had a premonition that you would answer and thanks for doing so.
I am aware of Data/Form but the problem is that the details to be added are not contiguous. Then there is the formatting issue.
Would you kindly help me further?
I am determining where the details are to be added by using:

x = ActiveCell.CurrentRegion.Rows.Count as the Row number e.g. Range(“A”&x+1).Value= etc
What would be the syntax for this sort of range:
Range(“A1:G1”) using x as the row number?

Cheers

Rob

Reply | Quote

Say that your data start in A1, and there are no gaps in column A. You can use code like this:

Dim rng As Range
Set rng = ActiveSheet.Range(“A1”).End(xlDown).Offset(1, 0).Resize(1, 7)

End(xlDown) returns the last cell in A, Offset(1,0) returns the cell 1 down, so the first empty cell in A, and Resize(1, 7) returns an area 1 row high and 7 columns wide.

or

Dim x As Long
Dim rng As Range
x = ActiveSheet.Range(“A1”).End(xlDown).Row + 1
Set rng = ActiveSheet.Range(ActiveSheet.Cells(x, 1), ActiveSheet.Cells(x, 7))

If there are gaps in column A, you can find the last filled cell in that column by using Range(“A65535”).End(xlUp) instead of Range(“A1”).End(xlDown).

Reply | Quote

Perfect, Hans. Thanks for your time and expertise.

Cheers

Rob

Reply | Quote