I keep on having the same error on all my forms with the search button.


Run-time Error '91'
Object variable or With block variable not set

and it shows that the error is on ListBox ( see attached image )

Can anyone help me?

Thanks

Attached Images

2.PNG (42.6 KB, 40 views)

This is my code and you deleted/missed out what to do if LBRS is nothing, something along the lines of:
If LBRS Is Nothing Then Set LBRS = .Range("A2")

yes it is your code, I've been finding out what is the problem and I don't know what it is.
I already added the code if nothing but the same error shows

When you debug, and that line is highlighted as in your picture, what's showing in the Value column of the Locals pane of the VBE for LBRS?
If it's not showing Nothing or Empty, then in the Immediate pane, type:
?LBRS.address
and press Enter. What's the result?


It would be easier to help you if you could provide a file where this error occurs - sure, desensitize the file and remove unnecessary sheets/code, but do ensure that the error still occurs before posting it here.

Here is the file

Attached Files

Book1.xlsm (27.6 KB, 7 views)

See attached.
LBRS was Empty/Nothing at that stage in the code. I've reinstated that line.

Headers in Report B, if present, should exactly match those in Sheet1
Headers should only be on one row. There should be no duplicate headers in Sheet1.

I've copied the headers from Report B to Sheet1 but really I should have reduced the headers on Sheet1 to 1 row and re-written the code which puts the headers in Report B to match those in Sheet1. I was lazy.
You have to get the Control names exactly right in the code. Corrected.
At least now it works to a degree, over to you to add the ifs and buts, checks etc.
For instance if someone chooses None from your dropdown for the Adhesive Type, are there entries in that column with the literal string None in them?, or are you hoping to show results for when there is nothing in those cells? There's a putative line in there which you can remove if I've got this wrong.
There are a few commented-out lines of code which you might end up needing to use.
Try to avoid On Error Resume Next too much unless you know an error might be thrown and why, otherwise it hides errors while debugging.
The code:has the On Error Resume Next because the next line will always throw an error if the sheet doesn't exist, but the On Error Goto 0 returns error reporting to normal straight after.

Attached Files

msofficeforums39312.xlsm (30.2 KB, 10 views)

Thank you
if all the products show when its not supposed to, is it something wrong with the
SceSht.Range(A1)?

I have no idea.
Under what circumstances? Attach your current file.

See attached file.

The Sheet 2 is the one I am talking about, when I enter "1" on Tag Size Width, it shows all the products instead of only showing product B.

Attached Files

msofficeforums39312_revised.xlsm (32.6 KB, 9 views)

Again, They don't have to be in the same order.
An example of how yours differ:
On sheet Sheet2:

and on Report C:

I already checked, but still it shows all the products after clicking the search button. Also I keep on getting the same error (See attached image) on my other sheet. Excel Sheet is attached.

Attached Images

error.PNG (7.0 KB, 17 views)

Attached Files

Book1.xlsm (23.8 KB, 9 views)

The headers on the report sheet don't match the headers on the data sheet - e.g. misspellings of the word "without" on the data sheet, and extra spaces in some headers. I suggest you copy the first row of headers from the data sheet and paste them into the report sheet.

The code doesn't expect to see multiple tables on Sheet1; for .currentregion to work properly here, every table needs to be separated from its neighbour by at least one completely blank/empty row. Deleting the text Table 4 in cell A22 satisfied that in this case.


Still, headers don't match:
You have Product Number in cell A23 of Sheet1, but Product in cell A1 of the Report sheet.
You have a trailing space in cell B23 of Sheet1, but none in cell C1 of the Report sheet.


In the userform you have a textbox called txtResMinC, but you have in the same place on the userform, another textbox called ResMinC, worse, you have a variable in the code ResMinC! I deleted the textbox ResMinC from the Userform.

Attached Files

msofficeforums39312 v2.xlsm (26.9 KB, 8 views)