VBA Userform search box debugging

[sjheest]

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:

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

 

[fadee2]

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]

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]

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.