0
Private Sub ComboBox8_Change()
Dim vRow As Double
Dim rPICRange As Range
Dim rComRange As Range

Set rComRange = dbComWB.Worksheets("CustomerList").Range("B2")
Set rComRange = Range(rComRange, rComRange.End(xlDown))
vRow = Application.WorksheetFunction.Match(Me.ComboBox8.value, rComRange, 0)
Set rPICRange = dbComWB.Worksheets("CustomerList").Range(Cells(vRow + 1, 14).Address)
Set rPICRange = Range(rPICRange, rPICRange.End(xlToRIght))
Me.ComboBox9.RowSource = rPICRange.Address(external:=True)
End Sub

Above are my code that want to fill a combobox but the "rPICRange" set to Rowsource as a single range instead of a list.

I do tried printout individual value of "rPICRange" & "rPICRange.end(xlToRight)" before assign to RowSource, it is correct value i want.

I also debug by changing .End(xlToRight) to other direction. Seen to me .End(xlUp) & .End(xlDown) work fine but Right & left is mess up.

Edit:

Is that because of ComboBox.RowSource only accept range in row (xlIp/xlDown), but not range in column (xlToRight/xlToLeft). If yes, how can i "Transpose" the range?

Set rPICRange = Application.WorksheetFunction.Transpose(Range(Cells(vRow + 1, 14).Address, rPICRange.End(xlToRight)))

Code above not working for me.

Community
  • 1
  • 1
KokJoo89
  • 15
  • 1
  • 9
  • As far as I can see rPICRange should be being calculated as everything from column N to the last non-blank cell on the row. (Or, if column N itself is the last non-blank cell on the row, everything from column N to the right-hand side of Excel.) How many columns do you have in your data? – YowE3K Jul 21 '16 at 03:38
  • "everything from column N to the last non-blank cell on the row." is exactly what i need. Sorry for my poor english. I get around 9 column. – KokJoo89 Jul 21 '16 at 03:49
  • So, just to confirm, you have data in columns N to V? – YowE3K Jul 21 '16 at 03:52
  • What is the value of `rPICRange.Address(external:=True)` that your code is calculating? – YowE3K Jul 21 '16 at 03:55
  • Yes, $N to $V. those cells contain string. – KokJoo89 Jul 21 '16 at 03:57
  • Your edit is probably the clue - it probably doesn't handle values in rows. Instead of the .RowSource, you could try For each rng in rPicRange | Me.ComboBox9.AddItem rng.value | Next (where each of the "|"s means a new line of code) – YowE3K Jul 21 '16 at 04:08

2 Answers2

0

You cannot use Range without a parent worksheet reference even if you are defining it with range objects that have parent worksheet objects in a private sub or any sub in a worksheet code page. See Is the . in .Range necessary when defined by .Cells? for an extended discussion on this.

Option Explicit

Private Sub ComboBox8_Change()
    Dim vRow As Double
    Dim rPICRange As Range
    Dim rComRange As Range

    With dbComWB.Worksheets("CustomerList")
        Set rComRange = .Range("B2")
        Set rComRange = .Range(rComRange, rComRange.End(xlDown))
    End With

    vRow = Application.WorksheetFunction.Match(Me.ComboBox8.Value, rComRange, 0)

    With dbComWB.Worksheets("CustomerList")
        Set rPICRange = .Cells(vRow + 1, 14)
        Set rPICRange = .Range(rPICRange, rPICRange.End(xlToRight))
    End With

    Me.ComboBox9.RowSource = rPICRange.Address(external:=True)
End Sub

I'm not entirely sure what you were trying to accomplish with the Range.Address property but I believe I've rectified it.

Community
  • 1
  • 1
  • I tried your suggestion but not working for me. My goal is get certain column list value and set to ComboBox9.RowSource depending on ComboBox8 selection. I tried using .End(xlUp)/.End(xlDown) it work but not the list i want. I want from the the cell to last right cell. – KokJoo89 Jul 21 '16 at 03:21
0
Private Sub ComboBox8_Change()
Dim vRow As Double
Dim Rng As Range
Dim rPICRange As Range
Dim rComRange As Range

Set rComRange = dbComWB.Worksheets("CustomerList").Range("B2")
Set rComRange = Range(rComRange, rComRange.End(xlDown))
Me.ComboBox9.Clear
vRow = Application.WorksheetFunction.Match(Me.ComboBox8.value, rComRange, 0)
Set rPICRange = dbComWB.Worksheets("CustomerList").Range(Cells(vRow + 1, 14).Address)
Set rPICRange = Range(rPICRange, rPICRange.End(xlToRight))

'code below add each range value into the list
For Each Rng In rPICRange
Me.ComboBox9.AddItem Rng.value
Next Rng

End Sub

Thank to YowE3K. I finally manage to get it working.

Lesson Learned:

RowSource indeed for Row range only, when input Column range will only get the first data.

KokJoo89
  • 15
  • 1
  • 9