none
on selection of numbers in combo box triggers another event in useform RRS feed

  • Question

  • Hi,

    I have user form with many combo boxes and text boxes controls. They input data in to sheet named "DATA". If I select numbers (which I already put in to sheet "DATA" with the same user form earlier ) in combo box (named as repeat cases) from a column D4:D on sheet "DATA",

    1.Is it possible that this combo box populate drop down values of last 10 days only, corresponding dates in column B4:B.

    2.Is it possible that rest of data in the same row on sheet DATA comes down automatically down to row in which Combo box repeat cases put its selected value ? Suppose I select value 3-5, which is in cell D2, now combo box repeat cases in put it in to F6. I want values from G2,H2,I2......AA2 to come down G6,H6,I6......AA6.

    regards,

    dockhem


    • Edited by dockhem2 Saturday, August 30, 2014 12:46 AM
    Saturday, August 30, 2014 12:36 AM

Answers

  • Hi

    Make change to the obvious.

     lrow = Cells(Rows.Count, 2).End(xlUp).Row
    For d = 4 To lrow
    If Cells(d, 2).Value > Cells(lrow, 2).Value - 10 And Cells(d, 2).Offset(0, 2).Value <> "0" And Cells(d, 2).Offset(0, 2).Value <> "-" Then
    RepeatCases.AddItem Cells(d, 2).Offset(0, 2).Value
    End If
    Next d

    If this answered your question, please make the post "ANSWERED"


    Cimjet

    • Marked as answer by dockhem2 Sunday, September 7, 2014 5:15 PM
    Sunday, September 7, 2014 3:25 PM

All replies

  • 1.Is it possible that this combo box populate drop down values of last 10 days only, corresponding dates in column B4:B.

    Sub Test()
      Dim B As Range, Top As Range, All As Range
      
      'Get the last used cell in column B
      Set B = Range("B" & Rows.Count).End(xlUp)
      'Go 10 rows up, but stop at row 4
      If B.Row - 10 < 4 Then
        Set Top = Range("B4")
      Else
        Set Top = B.Offset(-10)
      End If
      'Here are the last dates
      Set All = Range(Top, B)
      
      'Populate the combo box like this:
      For Each B In All
        Debug.Print B.Value
      Next
    End Sub

    For the second point, I can not say anything, I do not understand.

    Andreas.

    Saturday, August 30, 2014 4:26 PM
  • Hi Andreas Killer,

    I mean combo box repeat cases has to populate only cases of last 10 days for repetitions and do not populate cases older than 10 days.

    Thank you,

     Here it should not populate cases before 13-9-14 in drop down.

    regards,

    dockhem

    Sunday, August 31, 2014 8:38 AM
  • I mean combo box repeat cases has to populate only cases of last 10 days for repetitions and do not populate cases older than 10 days.

    My sample code shows how to get these values.

    Andreas.

    Monday, September 1, 2014 7:06 AM
  • Hi Andreas Killer,

    Actually combo box repeat cases takes value from D4:D, I want it to take value only for last 10 days. And not before 11, or 12 day. B4:B has date for the row. I changed the column in code. But it did not work in any way. Please tell me how to use it. I tried the code on module as well as on user form code. It did not give result.

    regards,

    dockhem

    Monday, September 1, 2014 8:59 AM
  • I tried the code on module as well as on user form code. It did not give result.

    Excuse me, but this is a developer forum, means developers help developers.

    Of course, also developers with small experience can ask questions, but I assume a minimum of personal initiative and knowledge.

    If you really don't know that a RANGE has a row property and that you can use it to get values from a different column

    for each R in Range("A1:A10")
      Debug.Print Range("B" & R.Row)
    next

    then I fear that you will never complete your project, you are far overburdened. I recommend you to hire a developer from your local area.

    Andreas.

    Monday, September 1, 2014 10:21 AM
  • Hi Adreas Killer,

    Thank you for advice and help. Actually I knew nothing of MS OFFICE. I never had formal education in MS OFFICE. Few months back, I even could not type in excel though. I learned here only. My project is without any fund. So it is not possible to hire any body. I try it and got it working. I am making it better. As the drop down in combo box become very big so every time scrolling is needed. But if only last 10 days are populated then no scrolling and time saving.

    Well it is not burden for me. I enjoy excel. 

    Though it did not work for me even now thank you

    regards,

    dockhem 

    Monday, September 1, 2014 11:37 AM
  • Hi

    I incorporated Andreas macro with yours. Replace the "Initialize" macro with this one and test it.

    Private Sub UserForm_Initialize()
    i = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious).Row
    Set ws = ActiveWorkbook.Worksheets("DATA")
            If Cells(4, 1) = "" Then
                 TxtCount.Text = 1
                    SpinButton1.Max = 0
                    Else
              TxtCount.Text = ws.Cells(i, 1) + 1
                 End If
                    Me.TxtDate = Format(Date, "mm/dd/yyyy")
                    Me.ComboBox1.List = WorksheetFunction.Transpose(ws.Range("K3:P3"))

    'MEDICINE GROUP 1
                    Me.ComboBox7.List = WorksheetFunction.Transpose(ws.Range("AP3:FK3"))
                    Me.ComboBox8.List = WorksheetFunction.Transpose(ws.Range("AP3:FK3"))
                    Me.ComboBox9.List = WorksheetFunction.Transpose(ws.Range("AP3:FK3"))
                    Me.ComboBox10.List = WorksheetFunction.Transpose(ws.Range("AP3:FK3"))
                    Me.ComboBox11.List = WorksheetFunction.Transpose(ws.Range("AP3:FK3"))
                    Me.ComboBox12.List = WorksheetFunction.Transpose(ws.Range("AP3:FK3"))
                    Me.ComboBox13.List = WorksheetFunction.Transpose(ws.Range("AP3:FK3"))
                    Me.ComboBox14.List = WorksheetFunction.Transpose(ws.Range("AP3:FK3"))
                    Me.ComboBox15.List = WorksheetFunction.Transpose(ws.Range("AP3:FK3"))
                    Me.ComboBox16.List = WorksheetFunction.Transpose(ws.Range("AP3:FK3"))
                    Me.ComboBox17.List = WorksheetFunction.Transpose(ws.Range("AP3:FK3"))
                    Me.ComboBox18.List = WorksheetFunction.Transpose(ws.Range("AP3:FK3"))
                   
                    Dim rng As Range, B As Range, Top As Range, All As Range
     
        Set B = Range("B" & Rows.Count).End(xlUp)
        If B.Row - 10 < 4 Then
        Set Top = Range("B4")
      Else
        Set Top = B.Offset(-10)
      End If
     
      Set All = Range(Top, B).Offset(0, 2)
       For Each B In All
    If Trim(B.Value) <> "0" And Trim(B.Value) <> "-" Then
        RepeatCases.AddItem B.Value
    End If
      Next
    End Sub

    Dockhem, we all want to help, I'm sure but for me it's very difficult to understand what you want.

    Try this and let us know if that works for you.


    Cimjet

    Monday, September 1, 2014 7:53 PM
  • See previous post

    Cimjet

    Tuesday, September 2, 2014 12:46 AM
  • Hi Cimjet,

    It is excellent and do what I wanted for repeat cases combo box . Can it do on the basis of date in column B ? It is doing for last 11 entries.

    Thank you very much

    regards

    dockhem



    • Edited by dockhem2 Tuesday, September 2, 2014 8:33 AM
    Tuesday, September 2, 2014 8:09 AM
  • If you want column B, just remove the  .Offset(0,2) on this line.

     Set All = Range(Top, B).Offset(0, 2)

    Don't forget the period "dot" before Offset


    Cimjet


    • Edited by Cimjet Wednesday, September 3, 2014 12:28 AM
    Wednesday, September 3, 2014 12:27 AM
  • Thank you,

    Actually I want here "Set Top = B.Offset(-10)", Something like "Set Top = B.Offset(Today-10 DATES(IT SHOULD BE 10 DATES NOT THE ROWS IN B)", say it 3-9-14 date in B where enter data currently. It should show all data in D4:D up to rows, that have date 25-8-14 in B4:B. It should be dynamic up ward according to dates that is 10 dates back today or the date in present row in B4:B. I think Now I am clear.

    If it is possible it will be very nice. If not please tell me.

    regards,

    dockhem


    • Edited by dockhem2 Wednesday, September 3, 2014 4:00 AM
    Wednesday, September 3, 2014 3:56 AM
  • Hi

    This goes in your UserForm_Initialize macro

    Replace your script for the RepeatCases combobox to this:

    Dim lrow As Long, d As Long
    lrow = Cells(Rows.Count, 2).End(xlUp).Row
    For d = 4 To lrow
    If Cells(d, 2).Value > Date - 10 Then
    RepeatCases.AddItem Cells(d, 2).Value
    End If
    Next d


    Cimjet

    Saturday, September 6, 2014 2:57 AM
  • This one will remove also the cells with "0"s and "-".

    Dim lrow As Long, d As Long
    lrow = Cells(Rows.Count, 2).End(xlUp).Row
    For d = 4 To lrow
    If Cells(d, 2).Value > Date - 10 And Cells(d, 2).Offset(0, 2).Value <> "0" And Cells(d, 2).Offset(0, 2).Value <> "-" Then
    RepeatCases.AddItem Cells(d, 2).Offset(0, 2).Value
    End If
    Next d


    Cimjet


    • Edited by Cimjet Saturday, September 6, 2014 1:40 PM
    • Proposed as answer by Cimjet Sunday, September 7, 2014 10:38 PM
    • Unproposed as answer by Cimjet Sunday, September 7, 2014 10:38 PM
    Saturday, September 6, 2014 1:39 PM
  • Thanks you Cimjet,

    It is excellent and what I wanted. A little modification if possible any. It works with consideration of date of our operating system. If we are working data of back date then it do not work. Can It have consideration of last entered date in B:B ? It also work on future date though it is not a big problem.

    Again thank you for your kind help,

    regards.

    dockhem

    Sunday, September 7, 2014 12:45 PM
  • Hi

    Make change to the obvious.

     lrow = Cells(Rows.Count, 2).End(xlUp).Row
    For d = 4 To lrow
    If Cells(d, 2).Value > Cells(lrow, 2).Value - 10 And Cells(d, 2).Offset(0, 2).Value <> "0" And Cells(d, 2).Offset(0, 2).Value <> "-" Then
    RepeatCases.AddItem Cells(d, 2).Offset(0, 2).Value
    End If
    Next d

    If this answered your question, please make the post "ANSWERED"


    Cimjet

    • Marked as answer by dockhem2 Sunday, September 7, 2014 5:15 PM
    Sunday, September 7, 2014 3:25 PM
  • Thank you Cimjet,

    perfectly same,

    regards

    dockhem

    Sunday, September 7, 2014 5:15 PM