0

I have a userform which is supposed to update different sheets according to combobox "(Select layer to update)" at a time, see attached image, as soon as I press on SAVA DATA button it return error. "Run time error 380" here are my codes. The listbox is supposed to update according the sheet selected under combobox "(Select layer to update)"

enter image description here

.ListBox1.ColumnCount = 28
.ListBox1.ColumnHeads = True
.ListBox1.ColumnWidths = "30,50,40,40,35,43,43,28,25,25,25,25,37,50,45,55,70,60,47,35,35,40,40,40,40,50,160,40"


If iRow > 1 Then
   .ListBox1.RowSource = "ActiveSheet!A9:AB" & iRow
Else
   .ListBox1.RowSource = "ActiveSheet!A9:AB9"
   
End If
End With
Maciej Los
  • 8,468
  • 1
  • 20
  • 35
Eikky da Shidiwe
  • 5
  • 4

1 Answers1

1

The RowSource property requires to assign a string reference prefixed by the actual literal worksheetname plus an exclamation mark (e.g. Sheet1!A9:AB9), not by characters starting with "ActiveSheet" as presumably there won't be a sheet named "ActiveSheet".

If you want to use VBA's ActiveSheet property as first part you could use the following way getting the sheet's address passing the additional External:=True argument (attention to :=) which returns the fully qualified sheet name as string:

    .RowSource = ActiveSheet.Range("A9:AB" & iRow).Address (External:=True)

Another way would be to simply join the string parts via the ampersand connector &, e.g.

  • ActiveSheet.Name & "!A9:AB" & iRow (using the name of ANY active sheet) or
  • ThisWorkbook.Worksheets("Sheet1").Name & "!A9:AB" & iRow (using the tabular sheet name) or
  • Sheet1.Name & "!A9:AB" & iRow (using the CodeName)
T.M.
  • 9,436
  • 3
  • 33
  • 57
  • @EikkydaShidiwe You are welcome. If you found my post helpful, feel free to accept it by ticking the green checkmark - c.f. ["Someone answers"](https://stackoverflow.com/help/someone-answers) – T.M. Jul 30 '21 at 15:49
  • @ T.M Thank you for response. i changed the first row which is ```vba .ListBox1.RowSource = ActiveSheet.Name & "!A9:AB" & iRow. then I am still getting an erro on ```vba .ListBox1.RowSource = "ActiveSheet!A9:AB9" or should i also include name on the last row? – Eikky da Shidiwe Jul 30 '21 at 18:12
  • @EikkydaShidiwe Of course you have to change both assignments, "ActiveSheet!A9:AB9" can't be interpreted correctly as "ActiveSheet" is no actual sheetname, so start here, too with the VBA code: `.RowSource = ActiveSheet.Name & "!A9:AB9"` – T.M. Jul 30 '21 at 18:35
  • thank you its seems like there's one sheet which is returning an error. " Run time error 380. But other sheet are okay. Sheet named SSG(USSG) as under combobox "select layer to update" – Eikky da Shidiwe Jul 30 '21 at 19:27
  • @EikkydaShidiwe How do you calculate `iRow` and what's the resulting value for the mentioned sheet? – T.M. Jul 31 '21 at 19:56
  • ``` vba iRow = [Counta(ActiveSheet!A:A)]. if i change it to. ```vba iRow = [Counta(ActiveSheet.name"!A:A")] 'to identifying the last row. then it gives an error. – Eikky da Shidiwe Aug 02 '21 at 17:01
  • Once again the same misunderstanding: there is no sheet named "ActiveSheet". If you stick to *VBA's* `ActiveSheet` and are using *Excel's* `CountA` in a **VBA evaluation** you would have to join the formula parts as string within `Evaluate` instead of brackets `[]`: `iRow = Evaluate("=Counta(" & ActiveSheet.Name & "!A:A)")` and be aware that `CountA` omits empty cells(!). Slightly better instead: `iRow = ActiveSheet.Range("A" & ActiveSheet.Rows.count).End(xlUp).Row`. - *Generally I'd avoid `ActiveSheet` references wherever possible.* @EikkydaShidiwe – T.M. Aug 02 '21 at 18:08
  • thank you, it works. but i still have one sheet which is not picking up the correct row source, its goes to a wrong sheet. – Eikky da Shidiwe Aug 03 '21 at 17:53
  • You are welcome. - Of course `ActiveSheet` can be any sheet currently active and not the one you have in mind, especially if you *selected* another one somewhere else in your code (c.f. [how to avoid using `Select` in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba/23913882?r=SearchResults&s=3|25.6194#23913882)) – T.M. Aug 03 '21 at 18:02
  • i will be back with more question. – Eikky da Shidiwe Aug 03 '21 at 18:28