CODEWITHSUNDEEP
excelvbauserform
Eikky da Shidiwe
Eikky da Shidiwe

Reputation: 5

Activesheet with userform

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

Upvotes: 0

Views: 476

Answers (1)

T.M.
T.M.

Reputation: 9948

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)

Upvotes: 1

Related Questions