Userform help

[SLinas]

Hello Guys,

Searched net way too much and could not find solution to my problem.
Will try to explain as clear as possible what I am trying to do.
I tried to do simple weighbridge userform with simple stock control but I am stuck...

Here is photo how looks my userform, it might look too complicated but its what I need.

userform.jpg

The code which does the final step should check user's input in the table and if there is no such value it should give error, if there is such value it should delete it.
I have the code which works pretty much as I want but it works only for one textbox, the problem is if user makes mistake for example at 10 or 11 input of weight all before weights are already deleted.
Hope you could understand anything, my code is below:

Private Sub Check_on_error()    
    Dim ws As Worksheet
    Dim strSearch As String
    Dim aCell As Range
    
    '~~> where we look
    Set ws = Sheets("Stock")


With ws
        '~~> Get the value which you want to search
        
        strSearch = TextBox6.Value


        '~~> xlWhole is used in the code below so that we find a complete match
        If ComboBox1.Value = "Z1" Then
        Set aCell = .Columns(1).Find(What:=strSearch, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
        
        End If
        
        If ComboBox1.Value = "Z2" Then
        Set aCell = .Columns(2).Find(What:=strSearch, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
        
        End If
        
        If ComboBox1.Value = "Z3" Then
        Set aCell = .Columns(3).Find(What:=strSearch, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
        
        End If
        
        '~~> check if the weight is in the list
        If Not aCell Is Nothing Then
            '~~> deletes weight from exact zone
            aCell.Delete
        Else '<~~ if there is no such weight
            MsgBox TextBox6.Value & " nera tokio svorio"
        Exit Sub
        End If
        
End With


End Sub

[Hightree]

I think you can schorten this code with a loop or Select case, because the richt sign from the text into the combobox is always the same as the column you can schorten this code by calling the code: Set aCell etc. Into a sub routine.
Caan you show us the worksheet?

[SLinas]

Hello,

There is my worksheet "batteries_processing" where all the data goes from user form
Processed.jpg
There is "printing_sheet", which saves as .pdf after guys finishing their shift and press process button
DBP.jpg
And finally before guys press process button it should check if there is such weight in the exact zone, if there is such it deletes the weight if not it gives error and stops the script.
This sheet I called "stock", data to this sheet comes from another userform "deliveries"
There is where I am struggling at the moment, the final step to finish my project...
stock.jpg

[Fluff]

Cross posted https://www.mrexcel.com/forum/excel-...form-help.html

[Lianne]

Hope you could understand anything!

[William22]

Maybe loop can solve this for you. Also visit google and search your query there. I think you will get answer quickly.

[fouad]

Hello Guys,

Searched net way too much and could not find solution to my problem.
Will try to explain as clear as possible what I am trying to do.
I tried to do simple weighbridge userform with simple stock control but I am stuck...

Here is photo how looks my userform, it might look too complicated but its what I need.

userform.jpg

The code which does the final step should check user's input in the table and if there is no such value it should give error, if there is such value it should delete it.
I have the code which works pretty much as I want but it works only for one textbox, the problem is if user makes mistake for example at 10 or 11 input of weight all before weights are already deleted.
Hope you could understand anything, my code is below:

Private Sub Check_on_error()    
    Dim ws As Worksheet
    Dim strSearch As String
    Dim aCell As Range
    
    '~~> where we look
    Set ws = Sheets("Stock")


With ws
        '~~> Get the value which you want to search
        
        strSearch = TextBox6.Value


        '~~> xlWhole is used in the code below so that we find a complete match
        If ComboBox1.Value = "Z1" Then
        Set aCell = .Columns(1).Find(What:=strSearch, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
        
        End If
        
        If ComboBox1.Value = "Z2" Then
        Set aCell = .Columns(2).Find(What:=strSearch, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
        
        End If
        
        If ComboBox1.Value = "Z3" Then
        Set aCell = .Columns(3).Find(What:=strSearch, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
        
        End If
        
        '~~> check if the weight is in the list
        If Not aCell Is Nothing Then
            '~~> deletes weight from exact zone
            aCell.Delete
        Else '<~~ if there is no such weight
            MsgBox TextBox6.Value & " nera tokio svorio"
        Exit Sub
        End If
        
End With


End Sub

Hello, did you find any solution for this?

[fouad]

Hello Guys,

Searched net way too much and could not find solution to my problem.
Will try to explain as clear as possible what I am trying to do.
I tried to do simple weighbridge userform with simple stock control but I am stuck...

Here is photo how looks my userform, it might look too complicated but its what I need.
userform.jpg

The code which does the final step should check user's input in the table and if there is no such value it should give error, if there is such value it should delete it.
I have the code which works pretty much as I want but it works only for one textbox, the problem is if user makes mistake for example at 10 or 11 input of weight all before weights are already deleted.
Hope you could understand anything, my code is below:

Private Sub Check_on_error()    
    Dim ws As Worksheet
    Dim strSearch As String
    Dim aCell As Range
    
    '~~> where we look
    Set ws = Sheets("Stock")


With ws
        '~~> Get the value which you want to search
        
        strSearch = TextBox6.Value


        '~~> xlWhole is used in the code below so that we find a complete match
        If ComboBox1.Value = "Z1" Then
        Set aCell = .Columns(1).Find(What:=strSearch, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
        
        End If
        
        If ComboBox1.Value = "Z2" Then
        Set aCell = .Columns(2).Find(What:=strSearch, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
        
        End If
        
        If ComboBox1.Value = "Z3" Then
        Set aCell = .Columns(3).Find(What:=strSearch, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
        
        End If
        
        '~~> check if the weight is in the list
        If Not aCell Is Nothing Then
            '~~> deletes weight from exact zone
            aCell.Delete
        Else '<~~ if there is no such weight
            MsgBox TextBox6.Value & " nera tokio svorio"
        Exit Sub
        End If
        
End With


End Sub

Nevermind, I found the solution.

[Aussiebear]

@Fouad, Well that's a positive contribution to an old thread.