VBA Error: Intermittent Run-time error '91'

[JamesW257]

Hi,

I have a spreadsheet that contains a macro which has been used for over a year with no issues. The spreadsheet is used by employees in my company to book or cancel desk bookings in our office for chosen days.

However, in the past month some users have started to get a Run-time error '91' (Object variable or With block variable not set). Some users aren't getting it at all, some users get it all the time and for others it can be intermittent. For me personally, I had a couple of weeks just passed where I didn't get the issue at all but suddenly its started happening for me again.

I have a button called 'Book a Desk' that calls a macro to simply opens and initializes a UserForm. I've underlined the part that its failing on (NameTextBoxBook.Value). The TextBox on the UserForm is correctly named (NameTextBoxBook)- see attached. The point its failing on is very simple, its just setting a TextBox Value to be blank when the form loads.


Private Sub UserForm_Initialize()

Dim cDesk As Range
Dim ws As Worksheet
Set ws = Worksheets("Data")

'Opens the booking form in the middle of the screen
Me.StartUpPosition = 0
Me.Left = Application.Left + (0.5 * Application.Width) - (0.5 * Me.Width)
Me.Top = Application.Top + (0.5 * Application.Height) - (0.5 * Me.Height)

'Sets the default values for Name (Blank), Desk Number (Blank) and Date (Cell C6)
NameTextBoxBook.Value = ""
DeskNumberTextBox.Value = ""
DateTextBox.Value = Format(Range("C6").Value, "dd/mm/yyyy")

'Pre-populates the Desk Number list from the tab called 'Data' which is hidden
For Each cDesk In ws.Range("DeskNumber")
With Me.DeskNumberTextBox
.AddItem cDesk.Value
End With
Next cDesk

End Sub



I don't think the issue I'm seeing is specific to this workbook. I tried building a completely new workbook that just loaded a UserForm with a blank TextBox and had the same issue.

Could it be linked to Windows Update or missing Library/Reference or something? Its strange how it has only recently started happening and there have not been any changes to the code in about year...and also strange how it works fine for some user and others its just started happening for.

Help!

Thank you

[sintek]

Run-time error '91' (Object variable or With block variable not set)

Are you sure that this is the error for that specific line of code?

Only thing that comes to mind:
Have you perhaps declared a variable named NameTextBoxBook As Object...somewhere else in the workbook...

[JamesW257]

Thanks for your response. I've attached two screenshots. One shows the form being loaded correctly with no issue and the other shows the error Runtime 91 error message along with the error that is displayed when debugging.

Its the fact its intermittent that's getting to me! For some people it just never happens, for others its started in the last month (and happens every time) and for some its intermittent.

I've had a look and cant find anywhere else that NameTextBoxBook is defined as an object. NameTextBoxBook.Value is used again in a separate sub that is triggered when 'Confirm Booking' is clicked...however, when the runtime error is thrown, it would never get to that point.

Thanks

[sintek]

Unfortunately screenshots cannot be used for testing...Are you able to supply a sample file...

I notice you have a Personal workbook open too...Nothing declared there?

[JamesW257]

I've mocked up a very simple example that replicates what my other spreadsheet does - see attached.

When I click on 'Load UserForm' I get the runtime 91 error. I've just sent it to two people in my company and 1 of them also gets the runtime 91 error but for the other person, the UserForm loads correctly.

[PrizeGotti]

I was not able to replicate this issue with the attached file. Everything loaded fine.

[Marc L]

Hi,

that looks like more to an OS issue, see with your IT the Microsoft updates, MsForms to be reinstalled, …
Or maybe you use a beta Excel 365 version ?

[romperstomper]

Are the people with the issues using a different office build to the others? One of the recent office builds was prone to all sorts of nonsensical errors with user forms.

[JamesW257]

Thanks Rory, that's good to know! I'm on a recent build of MS 365 and for me, sometimes it happens and sometimes it doesn't. I have one user who is on exactly the same version (2302) and build as me and he has never had the runtime 91 error display.

[ByteMarks]

It might push the error somewhere else, but do you need to run that line of code? I imagine the field will be blank anyway when the form is initialised?

[JamesW257]

The error seems to happen anywhere the code tries to do something with a TextBox or ComboBox on the UserForm (but not all the time). For example, assigning a .Value to a TextBox or .AddItem to the ComboBox.

So even if I comment out the part where it errors when trying to blank out .Value on a TextBox when its initalised, it fails when .AddItem is used.

[romperstomper]

2212 was the build I know of that had problems, so you should be well past that, unless they've reintroduced them!

PS Why would you name a combobox DeskNumberTextBox??

[JamesW257]

I cant remember - I think it was originally a text box and I didn't change it after.

Thanks for that info on 2212. I've found a couple of links on the Microsoft forum with some more info from people that have had the same issue which is helpful (it wont let me post them on there). I just need to see if / how I can fix it as I'm on 2302 and its still happening intermittently.