Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Sep 2008
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Refreshing data on an excel form (2002)

    I have created a form to enter data to a worksheet. I have a combo box on the form that lists names from one column on the worksheet.

    If I sort the order in the name field from a sort button on the form the order of the names in the combo box on the form does not change.

    Seems like I need some kind of refresh command to reorder the combo box.

    Any ideas on how to do this?

    Thanks.

    Paul

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Refreshing data on an excel form (2002)

    Did you use the listsource property to fill the combobox or do you add the items through VBA (recommended)?
    Are you sorting the names on the worksheet?
    If so, you need to clear the combobox and reread the items from the sheet
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    2 Star Lounger
    Join Date
    Apr 2008
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Refreshing data on an excel form (2002)

    If I understand you correctly I use code to fill the combo box. Here is the code:

    Dim c As Range
    Dim FirstHit As String
    Dim SecondHit As String
    Dim d As Range
    Dim msgstr As String

    FirstHit = ""
    SecondHit = ""

    Set d = ActiveSheet.Range("cbStudents") ' a list of all the student names in the wrksheet

    For Each c In d

    'MsgBox c.Value

    If Not c.Value = "" Then

    If c.Value = cbName1.Value Then

    If FirstHit = "" Then
    FirstHit = c.Address
    'msgstr = "FirstHit is in " & FirstHit
    'MsgBox msgstr, vbInformation + vbOKOnly
    Else
    If SecondHit = "" Then
    SecondHit = c.Address
    Else
    MsgBox "Warning: Student appears more than 2 times in worksheet", vbInformation + vbOKOnly
    End If
    End If

    Range(FirstHit).Select
    Grade.Value = ActiveCell.Offset(0, 1)
    Course.Value = ActiveCell.Offset(0, 2)
    CourseNum.Value = ActiveCell.Offset(0, 9)
    Teacher.Value = ActiveCell.Offset(0, 3)
    Comment.Value = ActiveCell.Offset(0, 8)
    FN.Value = ActiveCell.Offset(0, 10)
    StudID.Value = ActiveCell.Offset(0, 13)
    LN.Value = ActiveCell.Offset(0, 11)
    Load_Button_Values

    I have a button on the form that runs a macro that sorts that data on the worksheet. After the sort I need some way to redo the combo list. The list still reflects the order on the worksheet before the sort took place.

    Thanks.

    Paul

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Refreshing data on an excel form (2002)

    You'll have to use the Clear method of the combo box to remove the existing items, then run the code that fills the combo box again.

  5. #5
    2 Star Lounger
    Join Date
    Apr 2008
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Refreshing data on an excel form (2002)

    Can you give me an idea on what the clear code should be.

    Thanks.

    Paul

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Refreshing data on an excel form (2002)

    Is cbName1 the name of the combo box? If so, you'd clear its list using

    cbName1.Clear

  7. #7
    2 Star Lounger
    Join Date
    Apr 2008
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Refreshing data on an excel form (2002)

    Thanks Hans.

    That worked.

    Paul

Posting Permissions

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