What is the syntax to read rows in to a list box?

<listbox name>.AddItem Sheets("<sheet name>").Cells(<row>, <column num>).Value & ...etc
#example - reads a list of food entries from a sheet
Private Sub loadListbox()
     lstFoods.Clear
     lastEntryRow = Sheets("Foods").Cells(Sheets("Foods").Rows.Count, "A").End(xlUp).Row 'how many foods are there?

     For i = 2 To lastEntryRow 'adds each food entry row into the listbox
        lineDisplay = Sheets("Foods").Cells(i, 1).Value & Chr(9) & Sheets("Foods").Cells(i, 2).Value
        lstFoods.AddItem lineDisplay 
     Next
End Sub

 

 

Advertisements

What is the syntax to access the right-most sheet?

Worksheets(Worksheets.Count)
'example
Worksheets(Worksheets.Count).Range("A1:A4") = "hello"
Worksheets(Worksheets.Count).Range("D1") = "world"
Worksheets(Worksheets.Count).Cells(2, 4).Value = "goodbye"

What is the syntax to read a sheet into a listbox?

lastEntryRow = Sheets("<sheet name>").Cells(Sheets("<sheet name>").Rows.Count, "<column letter>").End(xlUp).Row 
For i = 2 To lastEntryRow 'adds each row to listbox
       lineDisplay = Sheets("<sheet name>").Cells(i, <column you want to show in listbox>).Value & ...etc 
       <listbox name>.AddItem lineDisplay
Next
#example - reads a list of food entries from a sheet
Private Sub loadListbox()
     lstFoods.Clear
     lastEntryRow = Sheets("Foods").Cells(Sheets("Foods").Rows.Count, "A").End(xlUp).Row 'how many foods are there?

     For i = 2 To lastEntryRow 'adds each food entry row into the listbox
        lineDisplay = Sheets("Foods").Cells(i, 1).Value & Chr(9) & Sheets("Foods").Cells(i, 2).Value
        lstFoods.AddItem lineDisplay 
     Next
End Sub

What is the syntax to loop through worksheets?

'Method 1 - looping using a For Each loop
Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets
    <your code here>
Next sht

'Method 2 - looping using a For loop
For i=1 To ThisWorkbook.Worksheets.Count
     <your code here, referencing Worksheets(i)>
Next
'example using both methods
Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets
     sht.Range("B2") = "Hello"
Next sht

For i=1 To ThisWorkbook.Worksheets.Count
     Worksheets(i).Range("B2") = "Hello"
Next

 

Tips for debugging when working with worksheets

When working with worksheets, you may get Run-Time Error:9. That is, the following message box may pop up during execution:

Capture.PNG

Tips for debugging this error:

  • Is the name of the Worksheets spelled correctly?
  • Has the name of the sheet changed and not been updated in your code?
  • Has the worksheet been deleted?
  • Has the index you used to reference the sheet too large?
  • If working with multiple workbooks, did you reference the wrong workbook?