Tech Support Guy banner
Status
Not open for further replies.

Solved: Forms in Excel

994 views 4 replies 2 participants last post by  antveal 
#1 ·
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.
 
See less See more
#3 ·
Hey, sorry for the long delay, I am doing this at work.

I've attached a macro-enabled workbook with an example spreadsheet. I need the bin data to fill in sequential columns until a new property is reached, where either it moves down to the next row to record data or records data from a cell I have clicked on.
 

Attachments

#4 ·
I managed to get the data to inputted in the format i wanted using the following code:

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"

nextrow = Application.WorksheetFunction.CountA(Range("1:1")) + 1
nextrow = nextcol + 1
'Application.WorksheetFunction.CountA (Range("1:1")) + 1
If Option80 Then Cells(1, nextrow) = "80L"
If Option160 Then Cells(1, nextrow) = "160L"
If Option280 Then Cells(1, nextrow) = "280L"
If Option360 Then Cells(1, nextrow) = "360L"
If Option660 Then Cells(1, nextrow) = "6600L"
If Option1100 Then Cells(1, nextrow) = "1100L"
If Option1280 Then Cells(1, nextrow) = "1280L"
If OptionChamberlin Then Cells(1, nextrow) = "Chamberlin"
If OptionOther Then Cells(1, nextrow) = "Other"

Unload Me

End Sub

Now I just need to know how to get it from a cell of my choosing
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top