VBA Userform search box debugging

sjheest

New Member
Joined
Feb 2, 2021
Messages
2
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi everyone!

I am quite new to VBA but i am currently working on a project where we want to make a map of the warehouse with all the storage bins and their statuses. The things i have currently done are:

I,
- made a table with all the storage bin location statuses (Location, how many Handling Units are stored on each location, Putaway block, Active counting, Active Issue). (sheet name: Database)
- made a map of each floor and for each status (example: Issues 1, Issues 2, Countings 1 , Countings 2, etc.....).
- linked all the "locations" (cells) to their status on each map.
- used conditional formatting on each map to show if the status is good, false or unknown

this all went great, however it was a lot of work!

The problem i am experiencing is the following:

I made a userform in VBA where i can type in the location in a searchbox. It then searches column a in the "database".

When it's done searching, it displays all the data that is linked to that location in 5 textboxes in the form. I made a 6th textbox that displays the current date and time so when you click on ok/update it adds it behind the location you are currently editing. However when it make a typo or enter anything that isn't in the database i get the error: Run-time error '91': object variable or with block variable not set.

Is there any way to remove the error and change it a message box that displays: "Please check if you made a mistake"? Or even better.... remove the error and fix the typo instantly?

the code i used is the following:

VBA Code:
Private Sub Ok_Click()
    For I = 1 To 6
        Cells(findrow, I).Value = UserForm1.Controls.Item("TextBox" & CStr(I)).Value
        UserForm1.Controls.Item("TextBox" & CStr(I)).Value = ""
    Next I
    searchtxt.Value = ""
    

    
End Sub
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Private Sub searchtxt_Change()
    lastrow = Range("A65536").End(xlUp).Row
    
    findrow = Range("A1:A1" & lastrow).Find(searchtxt.Value, Cells(1, 1)).Row
    
    
    For I = 1 To 6
        UserForm1.Controls.Item("TextBox" & CStr(I)).Value = Cells(findrow, I).Value
    Next I
    
        Textbox6.Value = Format(Now)

    
End Sub

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Private Sub UserForm_Initialize()
  

    For I = 1 To 5
        UserForm1.Controls.Item("TextBox" & CStr(I)).Value = ""
    Next I
       searchtxt.Value = ""
       
    Textbox6.Value = Format(Now)


       
End Sub
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

fadee2

Active Member
Joined
Nov 7, 2020
Messages
335
Office Version
  1. 2019
Platform
  1. Windows
hi sjheest,

Welcome to the board.

How about using a row source enabled combox instead of a text box. That would enable the user to select the search string rather then typing it in. This would in turn rule out the possibility of the typo error by the user.
 

sjheest

New Member
Joined
Feb 2, 2021
Messages
2
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
hi sjheest,

Welcome to the board.

How about using a row source enabled combox instead of a text box. That would enable the user to select the search string rather then typing it in. This would in turn rule out the possibility of the typo error by the user.
Hi fadee2,

Thank you for replying! Does that eliminate the error message completely? It does sound very logical...

How could i input the code correctly and remove some things so i dont mess everything up? I am pretty new to VBA and i made some mistakes earlier so i am a little insecure for making changes.

And could i input data in a combobox using a barcode scanner? This is due to the fact that we use them a lot. And i don't want people to come ask me all the time why they aren't working or that they will mess something up.

I want to apologize for the bad english as it is not my native language.
 

fadee2

Active Member
Joined
Nov 7, 2020
Messages
335
Office Version
  1. 2019
Platform
  1. Windows
hi sjheest,
sorry for late reply.
As for later part of the query, I am sorry I would not be able to help you out with that, as I dont have any ecperience there in.

for the first part I would suggest uploading your sample workbook on any free service like dropbox or onedrive, that will save me the hassle of making assumptions and recreating the data and the code from ground up.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,167
Messages
5,623,123
Members
415,956
Latest member
Footballtend

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
Top