Results 1 to 2 of 2

Thread: Unsure how to change vba code to work properly select case and me.controls

  1. #1

    Unsure how to change vba code to work properly select case and me.controls



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have this code: It works as displayed in the first table.
    Set wbk = Workbooks("News.xlsm")
    For i = 2 To 11
    Select Case Me.Controls("Combobox" & i).Value
    Case "BBM155", "BBM108", "BBM516"
    strSheet = "BBM"
    Case "FITH155", "FITH108", "FITH516"
    strSheet = "FITH"
    Case "AGL155", "AGL108", "AGL516"
    strSheet = "Agls"
    Case "DMN155", "DMN108", "DMN516"
    strSheet = "Dmns"
    Case "BMC155", "BMC108", "BMC516"
    strSheet = "BMC"

    Case Else
    strSheet = ""
    End Select

    If strSheet <> "" Then
    Set wsh = wbk.Worksheets(strSheet)
    Set rngCell = wsh.Range("g" & wsh.Columns.Count).End(xlUp).Offset(0, 1)
    If rngCell Is Nothing Then
    lngRow = 9
    Else
    lngRow = rngCell.Row + 1

    End If
    ' Substitute the correct names of the controls
    wsh.Cells(lngRow, 2).Value = Me.TextBox1.Value 'Date
    wsh.Cells(lngRow, 3).Value = Me.TextBox3.Value 'Invoice #
    wsh.Cells(lngRow, 4).Value = Me.ComboBox12.Value 'Customer account #
    wsh.Cells(lngRow, 7).Value = Me.Controls("ComboBox" & i).Value 'Items 1-10
    End If
    Next i

    This is what the above code does right now:
    MM/DD/YYYY YYYYMMDD-555 CRA-00001 BBM155 BBM1
    MM/DD/YYYY YYYYMMDD-555 CRA-00001 BBM108 BBM2
    MM/DD/YYYY YYYYMMDD-555 CRA-00001 BBM516 BBM3


    I want to add the below to capture a numeric value associated with each entry. However when I just add the below code to the above it does not work as I expected it would.

    wsh.Cells(lngRow, 6).Value = Me.TextBox11.Value 'this one works, but applies to all items
    wsh.Cells(lngRow + 1, 6).Value = Me.TextBox12.Value 'Total number of this item ordered
    wsh.Cells(lngRow + 2, 6).Value = Me.TextBox13.Value 'Total number of this item ordered
    wsh.Cells(lngRow + 3, 6).Value = Me.TextBox14.Value 'Total number of this item ordered
    wsh.Cells(lngRow + 4, 6).Value = Me.TextBox15.Value 'Total number of this item ordered
    wsh.Cells(lngRow + 5, 6).Value = Me.TextBox16.Value 'Total number of this item ordered
    wsh.Cells(lngRow + 6, 6).Value = Me.TextBox17.Value 'Total number of this item ordered
    wsh.Cells(lngRow + 7, 6).Value = Me.TextBox18.Value 'Total number of this item ordered
    wsh.Cells(lngRow + 8, 6).Value = Me.TextBox19.Value 'Total number of this item ordered
    wsh.Cells(lngRow + 9, 6).Value = Me.TextBox20.Value 'Total number of this item ordered

    Instead of this: Wher I have a numeric value associated with that order(Column four here)
    MM/DD/YYYY YYYYMMDD-555 CRA-00001 3 BBM155 BBM1
    MM/DD/YYYY YYYYMMDD-555 CRA-00001 2 BBM108 BBM2
    MM/DD/YYYY YYYYMMDD-555 CRA-00001 7 BBM516 BBM3



    I get this:
    MM/DD/YYYY YYYYMMDD-555 CRA-00001 3 BBM155 BBM1
    MM/DD/YYYY YYYYMMDD-555 CRA-00001 3 BBM108 BBM2
    MM/DD/YYYY YYYYMMDD-555 CRA-00001 3 BBM516 BBM3

    2
    7


    The code repeats the first number for all entries then lists out the additional numbers in blank rows. I've tried to add an additional control, and more if then statements but I am very weak in VBA. Can anyone help?

    Thanks,
    Cane

  2. #2
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    183
    Articles
    0
    Excel Version
    2010
    Crossposted (and in progress) here.
    Circumference of a circle = 2πr²



    ²the circle's radius

Posting Permissions

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