Results 1 to 17 of 17

Capture data from OptionBox into datasheet

Threaded View

  1. #1
    Registered User
    Join Date
    07-30-2013
    Location
    England
    MS-Off Ver
    Excel 2003, Excel 2010
    Posts
    13

    Capture data from OptionBox into datasheet

    Hello again!

    Could anyone please help me with the below?

    I have a userForm to capture various data into excel sheet. everything works fine but i have two things to add and zero idea how to do it.

    1. add data entry from a option boxes...

    There are 2 groups of optionBoxes - "Unit" (3 to choose - UNIT A, UNIT B, UNIT C) and "type" - 9 to choose - red1 red2 etc....
    I would like the user to be able to select ONE unit and ONE Type and after the form is submitted it would add the exact values into specific row as in the code below. (Grouping is done)
    Here's thecode I already have:

    Private Sub cmdSubmit_Click()
    Dim ssheet As Worksheet
    
    Set ssheet = ThisWorkbook.Sheets("Workers")
    
    
    nr = ssheet.Cells(Rows.Count, 3).End(xlUp).Row + 1
    
    
    Cells(nr, 3) = Me.boxSurname.Text
    Cells(nr, 4) = Me.boxForename.Text
    Cells(nr, 6) = Me.boxPhone.Value
    Cells(nr, 9) = Me.boxDob.Value
    Cells(nr, 16) = Me.boxoptout.Text
    MsgBox "Employee Added"
    
    
    UserForm1.Hide
    End Sub
    
    Private Sub boxDob_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    On Error Resume Next
    Me.boxDob = Format(Me.boxDob, "dd/mm/yyyy")
    End Sub
    
    
    Private Sub boxIdexp_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    On Error Resume Next
    Me.boxIdexp = Format(Me.boxIdexp, "dd/mm/yyyy")
    End Sub
    Also i would like it to convert the input of "y" in
    Cells(nr, 16) = Me.boxoptout.Text
    into UPPERCASE automaticly before submitting data.
    And Finally at the very end I would like the form to clear before it closes.

    Any help would be amazing!
    Last edited by Daniel B; 08-21-2013 at 04:14 PM. Reason: added extra request

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Return data to datasheet when checkbox in the form is checked
    By Apple Ling in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-14-2013, 02:09 AM
  2. need help to get data from datasheet on to the userform
    By shankar.nstl in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-04-2011, 06:12 AM
  3. how to update data from one datasheet to a master sheet, automatically
    By mikej2009 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 03-31-2010, 11:09 PM
  4. Replies: 2
    Last Post: 02-19-2010, 02:59 AM
  5. Saving data to a form datasheet
    By University of Maine student in forum Excel General
    Replies: 0
    Last Post: 03-14-2006, 04:50 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1