Userform - referencing each entry

[sai541]

Hi guys and gals I have been a silent user of this site for sometime usually finding something close enough to my means that I havent had to post so far but I have now hit a (hopefully simple) snag.

I work for a community organisation, one function of which is we distribute vouchers to those in hard times. We need to keep records of this data and do various other reporting. I have created a userform to capture this data which saves the collected data to a hidden worksheet. Each time a person recieves assistance we have a reference number, but an individual may receive more than one type of assistance so on the data saved to the hidden worksheet needs to have the reference number in column A... otherwise when data is entered in the following instance it searchs for the first blank cell in column A and writes over the other data in columns b, c and d.

Currently it looks like...

*** No Voucher type Voucher No. Amount
B001 "Food" 1234 80
"Clothing" A1111 35
"Pharmacy" Pham11 30

Where it should look like...

*** No Voucher type Voucher No. Amount
B001 "Food" 1234 80
B001 "Clothing" A1111 35
B001 "Pharmacy" Pham11 30

The code am currently playing with is...
Textbox28 is the reference number and I have tried to use an If expression to have it do the following (see comments)
Private Sub cboSave_Click()
Dim LastRow As Object
Set LastRow = Sheets("Voucher Data").Range("a65536").End(xlUp)
'Enters saved values into mastersheet
LastRow.Offset(1, 0).Value = TextBox28.Text 'this is the reference no
LastRow.Offset(1, 1).Value = cbovoucher1.Text 'voucher type
LastRow.Offset(1, 2).Value = TextBox1.Text 'voucher number
LastRow.Offset(1, 3).Value = TextBox14.Text 'amount

LastRow.Offset(2, 1).Value = ComboBox1.Text 'voucher type
LastRow.Offset(2, 2).Value = TextBox2.Text 'voucher number
LastRow.Offset(2, 3).Value = TextBox15.Text 'voucher amount
If UserForm1.TextBox2.Value = True Then 'if a value is present then
LastRow.Offset(1, 0).Value = TextBox28.Text 'display the value in textbox28 in the corresponding row
Else

End If

LastRow.Offset(3, 1).Value = ComboBox2.Text 'voucher type
LastRow.Offset(3, 2).Value = TextBox3.Text 'voucher number
LastRow.Offset(3, 3).Value = TextBox14.Text 'voucher amount
If UserForm1.ComboBox2.Value = True Then
LastRow.Offset(1, 0).Value = TextBox28.Text 'reference number
Else

End If

LastRow.Offset(4, 1).Value = ComboBox3.Text
LastRow.Offset(4, 2).Value = TextBox4.Text
LastRow.Offset(4, 3).Value = TextBox17.Text
If UserForm1.ComboBox3.Value = True Then
LastRow.Offset(1, 0).Value = TextBox28.Text
Else

End If


MsgBox "Data Saved"
Unload Me

End Sub

Also a I have a number of combobox's that reference a certain cell range, however I have had to currently write them out as (for eg.)

Me.ComboBox1.RowSource = "Source!A1:A10"
Me.ComboBox2.RowSource = "Source!A1:A10"
Me.ComboBox3.RowSource = "Source!A1:A10"

I have tried doing something like... but obviously it doesnt work.

Private Sub Combolist()
Dim X As Long
Application.ScreenUpdating = False
For W = 0 To 10
Me.ComboBox(W).RowSource = "Source!A1:A10"

Any assistance would be greatly appreciated.

Regards

 

[djreiswig]

Instead of

If UserForm1.TextBox2.Value = True Then 'if a value is present then

Try

If UserForm1.TextBox2.Value <> ""

As for your combo boxes, you can reference them in a loop by their names. Try this

Private Sub Combolist()
    Dim W As Long
    Application.ScreenUpdating = False
    For W = 0 To 10
        Me.Controls("ComboBox" & W).RowSource = "Source!A1:A10"
    Next W
    Application.ScreenUpdating = True
End Sub

 

[sai541]

Thanks!

For some reason ...

Private Sub Combolist()
Dim W As Long
Application.ScreenUpdating = False
For W = 0 To 10
Me.Controls("ComboBox" & W).RowSource = "Source!A1:A10"
Next W
Application.ScreenUpdating = True
End Sub

Crashes my userform... ie when I click on the cell range that opens that particular userform it wants to debug, but thats not a big concern at the moment. Many thanks for your help!

 

[djreiswig]

You'll have to step through the code and see where it crashes.

Just a thought. The loop starts at 0. Do you have a combo named ComboBox0? Your listed ones started at 1.

 

[sai541]

I do although thats where the problem lays (typo). Its strange though, there was nothing to step through as I was using this to activate the form

If Not Application.Intersect(Target, Range("AJ:AJ")) Is Nothing Then
UserForm1.Show

and it was catching on the second line. This might be a bit hopeful but... as I can select any cell in AJ to activate the useform is it possible to retrieve the data from the cell I entered through in the first place? Each row represents a different case. Im thinking a basic lookup on the reference no but I'm unable to figure out how to do a relative lookup (dependent on the cell selected to activate the userform). This would be a sinch in access, however its unfortunately not an option.

 

[djreiswig]

If it stops on the UserForm1.Show line then the problem probably lies in the code in the userform. Do you have code in the userform Activate sub or the Initialize sub?

What calls the sub ComboList?

Maybe start commenting out lines until you find the culprit. Just put a single ' in front of the line and it will turn into a comment. Sometimes variable declarations can cause this.

You could also try compiling the code and see if your error shows up. I have had luck with this before as well. Click Debug, Compile VBAProject.