+ Reply to Thread
Results 1 to 2 of 2

Refresh Userform ComboBox Data Source

Hybrid View

lday75 Refresh Userform ComboBox... 08-23-2012, 01:05 PM
lday75 Re: Refresh Userform ComboBox... 08-23-2012, 01:23 PM
  1. #1
    Forum Contributor
    Join Date
    07-03-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    139

    Refresh Userform ComboBox Data Source

    I have the following code in the Change procedure of a checkbox on a userfrom. What I want is if the box is checked then the ComboBox (SummaryFiles) gets its list from Col G of the Summary Files sheet, and if it is NOT checked then get it's list from column H. This IS NOT working for me at all. I have the Column H section in the Initialize code at first, but then if a user checks the Show All box then the list does not get refreshed, it gets appended to. Basically this list will show more files than what is on the sheet. It is not refreshing, it is appending this list.

    Does anyone know how to make the source change from one column to another for a combobox?

    Private Sub ShowAll_Change()
    
    Dim Rng As Range
    Dim i As Long
    Sheets("Product Receipt").Select
        
        If ShowAll.Value = True Then
            Me.SummaryFiles.RowSource = ""
            Set Rng = Sheets("Summary Files").Range("G:G")
                For i = 1 To Rng.Rows.Count
                    If Rng(i) <> "" Then
                        Me.SummaryFiles.AddItem Rng(i)
                    End If
                Next i
        Else
            Me.SummaryFiles.RowSource = ""
            Set Rng = Sheets("Summary Files").Range("H:H")
                For i = 1 To Rng.Rows.Count
                    If Rng(i) <> "" Then
                    Me.SummaryFiles.AddItem Rng(i)
                End If
             Next i
             
        End If
    
    Me.SummaryFiles.SetFocus
    
    End Sub
    Private Sub UserForm_Initialize()
    
    Me.SummaryFiles.SetFocus
    
    End Sub

  2. #2
    Forum Contributor
    Join Date
    07-03-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    139

    Re: Refresh Userform ComboBox Data Source

    After some trials and error, I came up with the following that does exactly what I was looking for. basically I swapped the code from the CheckBox Change and Initialize and also added a ComboBox.Clear line to the checkbox change code, the following works perfectly for me.

    Private Sub ShowAll_Change()
    
    Me.SummaryFiles.Clear
    UserForm_Initialize
    
    End Sub
    Private Sub UserForm_Initialize()
    
    Dim Rng As Range
    Dim i As Long
    Sheets("Product Receipt").Select
        
        If ShowAll.Value = True Then
            Me.SummaryFiles.RowSource = ""
            Set Rng = Sheets("Summary Files").Range("G:G")
                For i = 1 To Rng.Rows.Count
                    If Rng(i) <> "" Then
                        Me.SummaryFiles.AddItem Rng(i)
                    End If
                Next i
        Else
            Me.SummaryFiles.RowSource = ""
            Set Rng = Sheets("Summary Files").Range("H:H")
                For i = 1 To Rng.Rows.Count
                    If Rng(i) <> "" Then
                    Me.SummaryFiles.AddItem Rng(i)
                End If
             Next i
             
        End If
    
    Me.SummaryFiles.SetFocus
    
    End Sub

+ Reply to Thread

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