1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Forms in Excel

Discussion in 'Business Applications' started by antveal, Jan 2, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. antveal

    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.
     
  2. CDHarm

    CDHarm

    Joined:
    May 26, 2011
    Messages:
    255
    Hi,

    Can you attach a workbook example?
     
  3. antveal

    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.
     

    Attached Files:

  4. antveal

    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
     
  5. antveal

    antveal Thread Starter

    Joined:
    Jul 15, 2012
    Messages:
    29
    Solved it
     
  6. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1083396

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice