Solved: Forms in Excel

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

antveal

Thread Starter
Joined
Jul 15, 2012
Messages
29
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.
 

antveal

Thread Starter
Joined
Jul 15, 2012
Messages
29
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

antveal

Thread Starter
Joined
Jul 15, 2012
Messages
29
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
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top