Hi all,
I am creating a form in excel to record two variables, the type and size of a bin, at a range of properties.For each property i visit I will need to record data on multiple bins on a separate row in excel. I am doing this using option buttons and visual basic as I feel this is the easiest way to collect the data while in the field.
My question is this. The form records the data perfectly for a single row, but i cannot work out how to make it start recording data on a different row, for a different property. Ideally I would like the form to start recording data on whatever row I click on, but if it just moves down a single row each time, that is fine as well.
This is the code I have so far come up with:
Private Sub CancelButton1_Click()
Unload Me
End Sub
Private Sub OKButton1_Click()
Sheets("Sheet5").Activate
nextcol = Application.WorksheetFunction.CountA(Range("1:1")) + 1
nextcol = nextcol + 1
'Application.WorksheetFunction.CountA (Range("1:1")) + 1
If OptionRefuse Then Cells(1, nextcol) = "Refuse"
If OptionRecycling1 Then Cells(1, nextcol) = "Mixed Recycling"
If OptionRecycling2 Then Cells(1, nextcol) = "Paper Recycling"
If OptionRecycling3 Then Cells(1, nextcol) = "Glass Recycling"
If OptionRecycling4 Then Cells(1, nextcol) = "Cans Recycling"
If Option80 Then Cells(2, nextcol) = "80L"
If Option160 Then Cells(2, nextcol) = "160L"
If Option280 Then Cells(2, nextcol) = "280L"
If Option360 Then Cells(2, nextcol) = "360L"
If Option660 Then Cells(2, nextcol) = "6600L"
If Option1100 Then Cells(2, nextcol) = "1100L"
If Option1280 Then Cells(2, nextcol) = "1280L"
If OptionChamberlin Then Cells(2, nextcol) = "Chamberlin"
If OptionOther Then Cells(2, nextcol) = "Other"
Unload Me
End Sub
Bear in mind this is my first go at visual basic so apologies for the ungainly code.
I am creating a form in excel to record two variables, the type and size of a bin, at a range of properties.For each property i visit I will need to record data on multiple bins on a separate row in excel. I am doing this using option buttons and visual basic as I feel this is the easiest way to collect the data while in the field.
My question is this. The form records the data perfectly for a single row, but i cannot work out how to make it start recording data on a different row, for a different property. Ideally I would like the form to start recording data on whatever row I click on, but if it just moves down a single row each time, that is fine as well.
This is the code I have so far come up with:
Private Sub CancelButton1_Click()
Unload Me
End Sub
Private Sub OKButton1_Click()
Sheets("Sheet5").Activate
nextcol = Application.WorksheetFunction.CountA(Range("1:1")) + 1
nextcol = nextcol + 1
'Application.WorksheetFunction.CountA (Range("1:1")) + 1
If OptionRefuse Then Cells(1, nextcol) = "Refuse"
If OptionRecycling1 Then Cells(1, nextcol) = "Mixed Recycling"
If OptionRecycling2 Then Cells(1, nextcol) = "Paper Recycling"
If OptionRecycling3 Then Cells(1, nextcol) = "Glass Recycling"
If OptionRecycling4 Then Cells(1, nextcol) = "Cans Recycling"
If Option80 Then Cells(2, nextcol) = "80L"
If Option160 Then Cells(2, nextcol) = "160L"
If Option280 Then Cells(2, nextcol) = "280L"
If Option360 Then Cells(2, nextcol) = "360L"
If Option660 Then Cells(2, nextcol) = "6600L"
If Option1100 Then Cells(2, nextcol) = "1100L"
If Option1280 Then Cells(2, nextcol) = "1280L"
If OptionChamberlin Then Cells(2, nextcol) = "Chamberlin"
If OptionOther Then Cells(2, nextcol) = "Other"
Unload Me
End Sub
Bear in mind this is my first go at visual basic so apologies for the ungainly code.