Consulting

Results 1 to 9 of 9

Thread: Userform help

  1. #1
    VBAX Newbie
    Joined
    Aug 2018
    Posts
    2
    Location

    Userform help

    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

  2. #2
    VBAX Regular
    Joined
    Jan 2018
    Location
    The Netherlands
    Posts
    45
    Location
    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?

  3. #3
    VBAX Newbie
    Joined
    Aug 2018
    Posts
    2
    Location
    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

  4. #4

  5. #5
    VBAX Newbie
    Joined
    Oct 2019
    Location
    USA Arizona
    Posts
    1
    Location
    Hope you could understand anything!

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

  7. #7
    Quote Originally Posted by SLinas View Post
    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?

  8. #8
    Quote Originally Posted by SLinas View Post
    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.

  9. #9
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,967
    Location
    @Fouad, Well that's a positive contribution to an old thread.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •