Results 1 to 8 of 8

Different results for ComboBox.RowSource property in BeforeUpdate Event

Threaded View

asha3010 Different results for... 09-28-2010, 07:26 AM
romperstomper Re: Different results for... 09-28-2010, 08:44 AM
foxguy Re: Different results for... 09-28-2010, 08:49 AM
asha3010 Re: Different results for... 09-28-2010, 10:16 AM
foxguy Re: Different results for... 09-28-2010, 12:03 PM
romperstomper Re: Different results for... 09-28-2010, 10:32 AM
asha3010 Re: Different results for... 09-28-2010, 10:40 AM
asha3010 Re: Different results for... 09-28-2010, 01:07 PM
  1. #1
    Forum Contributor
    Join Date
    02-09-2010
    Location
    Constanta
    MS-Off Ver
    Excel 2007
    Posts
    128

    Different results for ComboBox.RowSource property in BeforeUpdate Event

    Greetings,

    I have the following code in the beforeupdate event of combobox.

    Private Sub ComboBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    
    '-----------------------------------------------------------------------------------
    'Adapted: http://www.excelforum.com/excel-programming/661182
              '-update-a-combo-box-list-which-list-is-on-a-different-worksheet.html
    '-----------------------------------------------------------------------------------
    
    'If new sector is entered, add to combobox drop-down list
    If WorksheetFunction.CountIf(Range("test"), ComboBox1.Value) = 0 Then
        With Range("test")
            If .Rows.Count = 1 Then
                .Offset(.Row) = ComboBox1.Value
            Else
                .End(xlDown).Offset(1) = ComboBox1.Value
            End If
            .Sort .Cells(1)
            ComboBox1.RowSource = .Parent.Name & "!" & .Address
        End With
    End If
    
    End Sub

    While debugging, I noticed that the line result for

    ComboBox1.RowSource = .Parent.Name & "!" & .Address
    is different from

    ComboBox1.RowSource = .Parent.Name & "!" & Range("test").Address
    The former is always one row less than the latter. Can someone please explain what could be the cause even though I have a WITH statement to identify the range?

    Many thanks
    Asha

    Last edited by asha3010; 09-28-2010 at 01:08 PM.

Thread Information

Users Browsing this Thread

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

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