rorobear
08-17-2021, 06:59 AM
Hello Everyone,
Looking for a little VBA help please. I have 2 listbox, one is populated with the workbook sheets and the other identifies the column headers on a particular sheet when selected, at least that’s what it’s supposed to do. The problem I have is that every time I select a sheet I get Run-time error ‘91’ Object variable or With block variable not set. Any assistance is appreciated. The code is below and I’ve included the workbook.
Rb
Option Explicit
Private Sub UserForm_Activate()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
Me.lbxSheets.AddItem ws.Name
Next ws
End Sub
Private Sub lbxSheets_Click()
Dim x As Variant
Dim lastCol As Long
Dim mySheet As Worksheet
Me.lbxHeaders.Clear
'Set lbxSheets = ActiveSheet
With mySheet
mySheet = Me.lbxSheets.Value
lastCol = ThisWorkbook.Sheets(mySheet).Cells(1, Columns.Count).End(xlToLeft).Column
For x = 1 To lastCol
Me.lbxHeaders.AddItem ThisWorkbook.Sheets(mySheet).Cells(1, x)
Next x
End With
End Sub
Looking for a little VBA help please. I have 2 listbox, one is populated with the workbook sheets and the other identifies the column headers on a particular sheet when selected, at least that’s what it’s supposed to do. The problem I have is that every time I select a sheet I get Run-time error ‘91’ Object variable or With block variable not set. Any assistance is appreciated. The code is below and I’ve included the workbook.
Rb
Option Explicit
Private Sub UserForm_Activate()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
Me.lbxSheets.AddItem ws.Name
Next ws
End Sub
Private Sub lbxSheets_Click()
Dim x As Variant
Dim lastCol As Long
Dim mySheet As Worksheet
Me.lbxHeaders.Clear
'Set lbxSheets = ActiveSheet
With mySheet
mySheet = Me.lbxSheets.Value
lastCol = ThisWorkbook.Sheets(mySheet).Cells(1, Columns.Count).End(xlToLeft).Column
For x = 1 To lastCol
Me.lbxHeaders.AddItem ThisWorkbook.Sheets(mySheet).Cells(1, x)
Next x
End With
End Sub