Results 1 to 8 of 8

VBA for Option Button on Userform to send data to Multiple Spreadsheets

Threaded View

  1. #1
    Registered User
    Join Date
    01-28-2013
    Location
    St. Charles, Ilinois, USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    VBA for Option Button on Userform to send data to Multiple Spreadsheets

    Hello all-

    I have a rather large userform where I first select a worksheet for data through a list of option buttons and then enter the data on a different page in the same userform. I have been successful in getting data from the data entry fields to populate on the selected worksheet from the first set of radio buttons, but in my data entry section, my radio button (OptionButton1 -- at the bottom of the code) only populates on my master rather on the selected worksheet. It seems to ignore (or not account for) the previous radio button's selection for which worksheet to use for the userform's entry.

    Thank for any help in advance! My code:


    Option Explicit
    
    Private Sub cmdAdd_Click()
    Dim iRow As Long
    Dim ws As Worksheet
    
    If OptionButton9 Then Set ws = Worksheets("Shampoo")
    If OptionButton10 Then Set ws = Worksheets("Lotion")
    If OptionButton11 Then Set ws = Worksheets("Maillot")
    If OptionButton12 Then Set ws = Worksheets("Corn")
    If OptionButton13 Then Set ws = Worksheets("Speghetti Meat Sauce")
    If OptionButton14 Then Set ws = Worksheets("Pasta")
    If OptionButton15 Then Set ws = Worksheets("Chicken Star")
    If OptionButton16 Then Set ws = Worksheets("White Beans")
    If OptionButton17 Then Set ws = Worksheets("Coconut Milk")
    If OptionButton18 Then Set ws = Worksheets("Dried Fruit")
    If OptionButton19 Then Set ws = Worksheets("Rice and Beans")
    If OptionButton20 Then Set ws = Worksheets("Sweet Peas")
    If OptionButton21 Then Set ws = Worksheets("Sweet Corn")
    If OptionButton22 Then Set ws = Worksheets("Dried Roasted Onion")
    If OptionButton23 Then Set ws = Worksheets("Candy")
    If OptionButton24 Then Set ws = Worksheets("Black Beans Local")
    If OptionButton25 Then Set ws = Worksheets("Cereal Bulk")
    If OptionButton26 Then Set ws = Worksheets("Vitalmeal Rice")
    If OptionButton27 Then Set ws = Worksheets("Mannapack Rice")
    If OptionButton28 Then Set ws = Worksheets("Mannapack Potato")
    If OptionButton29 Then Set ws = Worksheets("Drinking Water")
    If OptionButton30 Then Set ws = Worksheets("Reliv Now")
    If OptionButton31 Then Set ws = Worksheets("Stop Hunger Now")
    If OptionButton32 Then Set ws = Worksheets("Herbalife")
    If OptionButton33 Then Set ws = Worksheets("Pinto Beans")
    If OptionButton34 Then Set ws = Worksheets("Dried Potato")
    If OptionButton35 Then Set ws = Worksheets("Kids Against Hunger")
    If OptionButton36 Then Set ws = Worksheets("Canned Baked Beans")
    If OptionButton37 Then Set ws = Worksheets("Soup Mia")
    If OptionButton38 Then Set ws = Worksheets("Meals of the Heartland")
    If OptionButton39 Then Set ws = Worksheets("Sweet Potato")
    If OptionButton40 Then Set ws = Worksheets("Baby Food")
    If OptionButton41 Then Set ws = Worksheets("Tent")
    If OptionButton42 Then Set ws = Worksheets("Clothes")
    If OptionButton43 Then Set ws = Worksheets("Bicycle")
    If OptionButton44 Then Set ws = Worksheets("Toys")
    If OptionButton45 Then Set ws = Worksheets("Plastic Containers")
    If OptionButton46 Then Set ws = Worksheets("Cleaning Kit")
    If OptionButton47 Then Set ws = Worksheets("Blankets")
    If OptionButton48 Then Set ws = Worksheets("Haitian Rice")
    If OptionButton49 Then Set ws = Worksheets("Seed")
    If OptionButton50 Then Set ws = Worksheets("Bucket")
    If OptionButton52 Then Set ws = Worksheets("Wipes")
    If OptionButton53 Then Set ws = Worksheets("Gallon")
    If OptionButton54 Then Set ws = Worksheets("Commode")
    If OptionButton55 Then Set ws = Worksheets("Crocs")
    If OptionButton56 Then Set ws = Worksheets("Power Stove Big")
    If OptionButton57 Then Set ws = Worksheets("Power Stove Small")
    If OptionButton58 Then Set ws = Worksheets("Diapers")
    If OptionButton59 Then Set ws = Worksheets("Family Cook Set")
    If OptionButton61 Then Set ws = Worksheets("Socks")
    If OptionButton62 Then Set ws = Worksheets("Bowls")
    If OptionButton63 Then Set ws = Worksheets("Small Paper Plate")
    If OptionButton65 Then Set ws = Worksheets("Cup")
    If OptionButton66 Then Set ws = Worksheets("Dessert Plate")
    If OptionButton67 Then Set ws = Worksheets("Cloth Towel")
    If OptionButton68 Then Set ws = Worksheets("Comb")
    If OptionButton69 Then Set ws = Worksheets("Paper Towel")
    If OptionButton70 Then Set ws = Worksheets("Lotion")
    If OptionButton71 Then Set ws = Worksheets("Notebooks")
    If OptionButton72 Then Set ws = Worksheets("Toilet Paper")
    If OptionButton73 Then Set ws = Worksheets("Napkins")
    If OptionButton74 Then Set ws = Worksheets("Soup")
    If OptionButton75 Then Set ws = Worksheets("Toothbrush")
    If OptionButton76 Then Set ws = Worksheets("Toothpaste")
    If OptionButton77 Then Set ws = Worksheets("Bag")
    If OptionButton78 Then Set ws = Worksheets("School Kit")
    If OptionButton79 Then Set ws = Worksheets("Propane Stove Small")
    If OptionButton80 Then Set ws = Worksheets("Propane Toilet")
    If OptionButton81 Then Set ws = Worksheets("HP Color Laser Jet")
    If OptionButton82 Then Set ws = Worksheets("Snogio Bleach")
    If OptionButton83 Then Set ws = Worksheets("Hygienic Kit Pack")
    If OptionButton84 Then Set ws = Worksheets("Kit Hygienic Feminin")
    If OptionButton85 Then Set ws = Worksheets("Serviette Hygienic")
    If OptionButton86 Then Set ws = Worksheets("Portable Dry Toilet System")
    If OptionButton87 Then Set ws = Worksheets("Tapis")
    If OptionButton88 Then Set ws = Worksheets("Medical Supply")
    If OptionButton89 Then Set ws = Worksheets("Paper Plate")
    
    
    'find first empty row in database
    iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    
    
    'copy the data to the database
    ws.Cells(iRow, 22).Value = ContainerNumberTextBox.Value
    ws.Cells(iRow, 3).Value = DateofEntryTextBox.Value
    ws.Cells(iRow, 4).Value = DateRecievedSentTextBox.Value
    ws.Cells(iRow, 10).Value = InvoiceNumberTextBox.Value
    ws.Cells(iRow, 13).Value = DesignationTextBox.Value
    ws.Cells(iRow, 14).Value = ProgramTextBox.Value
    ws.Cells(iRow, 17).Value = VilleofDistributionTextBox.Value
    ws.Cells(iRow, 18).Value = DepartmentTextBox.Value
    ws.Cells(iRow, 19).Value = LocationTextBox.Value
    ws.Cells(iRow, 20).Value = BuildingTextBox.Value
    ws.Cells(iRow, 21).Value = SpaceTextBox.Value
    ws.Cells(iRow, 16).Value = RestrictionTextBox.Value
    ws.Cells(iRow, 8).Value = ExpirationDateTextBox.Value
    ws.Cells(iRow, 23).Value = NumberofCasesTextBox.Value
    ws.Cells(iRow, 24).Value = UnitsPerCaseTextBox.Value
    ws.Cells(iRow, 25).Value = TotalQuantityTextBox.Value
    ws.Cells(iRow, 27).Value = ManufacturerTextBox.Value
    ws.Cells(iRow, 32).Value = ApprovedByTextBox.Value
    ws.Cells(iRow, 33).Value = RepresentativeTextBox.Value
    ws.Cells(iRow, 34).Value = ReceivedByTextBox.Value
    ws.Cells(iRow, 35).Value = StockingSupervisorTextBox.Value
    ws.Cells(iRow, 36).Value = DriverTextBox.Value
    ws.Cells(iRow, 11).Value = VehicleTextBox.Value
    ws.Cells(iRow, 28).Value = PriceorCostTextBox.Value
    ws.Cells(iRow, 29).Value = PerUnitTextBox.Value
    ws.Cells(iRow, 30).Value = ValueTextBox.Value
    ws.Cells(iRow, 31).Value = CurrentTextBox.Value
    ws.Cells(iRow, 38).Value = RequestforDeliveryTextBox.Value
    ws.Cells(iRow, 39).Value = CaseforDeliveryTextBox.Value
    ws.Cells(iRow, 41).Value = QuanityofForkliftsAvailableTextBox.Value
    ws.Cells(iRow, 44).Value = DateEnteredinMaintenanceTextBox.Value
    ws.Cells(iRow, 45).Value = ExpectedDateofRecoveryTextBox.Value
    ws.Cells(iRow, 46).Value = DateofRecoveryTextBox.Value
    
    'clear the data
    ContainerNumberTextBox.Value = ""
    DateofEntryTextBox.Value = ""
    DateRecievedSentTextBox.Value = ""
    InvoiceNumberTextBox.Value = ""
    DesignationTextBox.Value = ""
    ProgramTextBox.Value = ""
    VilleofDistributionTextBox.Value = ""
    DepartmentTextBox.Value = ""
    LocationTextBox.Value = ""
    BuildingTextBox.Value = ""
    SpaceTextBox.Value = ""
    RestrictionTextBox.Value = ""
    ExpirationDateTextBox.Value = ""
    NumberofCasesTextBox.Value = ""
    UnitsPerCaseTextBox.Value = ""
    TotalQuantityTextBox.Value = ""
    ManufacturerTextBox.Value = ""
    ApprovedByTextBox.Value = ""
    RepresentativeTextBox.Value = ""
    StockingSupervisorTextBox.Value = ""
    DriverTextBox.Value = ""
    VehicleTextBox.Value = ""
    PriceorCostTextBox.Value = ""
    PerUnitTextBox.Value = ""
    ValueTextBox.Value = ""
    CurrentTextBox.Value = ""
    RequestforDeliveryTextBox.Value = ""
    CaseforDeliveryTextBox.Value = ""
    QuanityofForkliftsAvailableTextBox.Value = ""
    DateEnteredinMaintenanceTextBox.Value = ""
    ExpectedDateofRecoveryTextBox.Value = ""
    DateofRecoveryTextBox.Value = ""
    
    End Sub
    
    
    Private Sub cmdClose_Click()
        Unload Me
    End Sub
    
    
    Private Sub UserForm1(Cancel As Integer, CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then
        Cancel = True
        MsgBox "Please Use the Close Button!"
    End If
    End Sub
    
    
    Private Sub OptionButton1_Click()
        If OptionButton1.Value = True Then Range("E6").Value = "Yes"
    End Sub
    Last edited by rcfrench; 03-15-2013 at 03:58 PM. Reason: Added code tags, as per forum rules. Don't forget!

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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