Runtime error 91 Object Variable or with block variable not set

Pookiemeister

Well-known Member
Joined
Jan 6, 2012
Messages
563
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Some times when I step through or run the code below it works but majority of the time, I will the error message stated in the title. When I make a selection with the combobox and watch the code, there is nothing set in the rEmpIniValue and rEmpIniValue.select turns yellow. But there is a value in the combobox. So why does this code some times work and other times it doesn't?

Thank You

Code:
Private Sub ComboBox1_Change()    
    Dim rEmpIniValue As Range
    
    Worksheets("SD_Employee_List").Activate
    
    With ActiveSheet.Range("A:A")
        Set rEmpIniValue = .Find(what:=Me.ComboBox1.Value, lookat:=xlWhole, LookIn:=xlValues)
        rEmpIniValue.Select
        Label1.Caption = ActiveCell.Offset(, 1)
    End With
End Sub
 
Last edited:
I notice that you have now amended the sheet name from SD to SD_Employee_List when populating the combobox
So the sheet names (for populating and searching) should be consistent
In which case @Norie please can you PM me next weeks lottery numbers :ROFLMAO:

Norie's method is therefore the simplest approach
- there no requirement to search
- you simply use the selected value's position in the list to determine the row number in the worksheet

Code below works for me and populates the label wth the correct value from column B
Code:
Private Sub UserForm_Initialize()
    Dim rngNames As Range
    With Sheets("SD_Employee_List")
        Set rngNames = .Range("A3", .Range("A" & .Rows.Count).End(xlUp))
    End With
    On Error Resume Next
    DTPicker1.Value = Date
    lblWotM.Caption = WeekOfMonth(CDate(DTPicker1))
    UserForm1.ComboBox1.List = rngNames.Value
    Me.Label1.Enabled = False
End Sub


Private Sub ComboBox1_Change()
    Dim idx As Long
    idx = Me.ComboBox1.ListIndex
    If idx <> -1 Then Label1 = Sheets("SD_Employee_List").Range("B" & idx + 3)
End Sub
 
Last edited:
Upvote 0

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
OR...

Load both columns into the Combobox (display either or both columns in userfom) and use the value in the 2nd column to populate the label
(zero based so Column(0) is the first column)

Code:
Private Sub UserForm_Initialize()
    Dim rngNames As Range
    With Sheets("SD_Employee_List")
        Set rngNames = .Range("A3", .Range("A" & .Rows.Count).End(xlUp))[COLOR=#ff0000].Resize(, 2)[/COLOR]
    End With
    On Error Resume Next
    DTPicker1.Value = Date
    lblWotM.Caption = WeekOfMonth(CDate(DTPicker1))
    UserForm1.ComboBox1.List = rngNames.Value
    Me.Label1.Enabled = False
End Sub


Private Sub ComboBox1_Change()
    If Me.ComboBox1.ListIndex - 1 Then Label1 = ComboBox1.Column(1)
End Sub
 
Last edited:
Upvote 0
Thank You all for your wonderful help. I greatly appreciate it. Just a quick question, how did you get to this VBA knowledge level? Was it books read, trial an error. Any advice would be greatly appreciated. Thank you again.
 
Upvote 0
how did you get to this VBA knowledge level?

In my case

- a lot of trial and error

- google can help as a good starting point - but NEVER simply copy and use code found (after initial testing to confirm it works) without deconstructing it and reconstructing it from the ground up

- to ensure personal development extends beyond the immediate issue, when solving a problem look at other attributes available within any functions used etc - anything that may be useful in future - there is a Microsoft page for virtually everything (example : https://docs.microsoft.com/en-us/office/vba/api/excel.range.find )

- subscribe to various newsletters and READ them when they hit inbox!
https://powerspreadsheets.com/
https://excelribbon.tips.net/index.html
https://excelmacromastery.com/
https://www.myonlinetraininghub.com/
 
Last edited:
Upvote 0
how did you get to this VBA knowledge level?
For me it was primarily from helping people here.
You get to see all sorts of problems that you may not come across in you rline of work.
Also you get to see how other people resolve those problems.
 
Upvote 0
Thank You so very much for your guidance on becoming better at VBA. I will definitely try to all your ideas. Thank You again.
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top