Results 1 to 6 of 6

Thread: Userform help
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2018
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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...

    Since I cannot add any photos, I will try to explain what I am seeking with my code.
    The user input weight in text box6 .. textbox35 and selects one option from 3 (Z1, Z2 or Z3) in combobox next to textbox
    In excel sheet "stock" there is 3 columns Z1 (zone 1), Z2 (zone 2) and Z3 (zone 3).
    The code should check if user input (integer) in textbox6 .. textbox35 exist in excel sheet "stock".
    If there is such number in exact zone the number should be deleted, if not user get error message.

    Here is my code so far

    Code:
    Private Sub Check_on_error()    
        Dim ws As Worksheet
        Dim strSearch As String
        Dim aCell As Range
        
        '~~> Kur ieskosim paletes svorio
        Set ws = Sheets("Stock")
    
    
    With ws
            '~~> Get the value which you want to search
            
            strSearch = TextBox6.Value
    
    
            '~~> Column A is Column 1 so Column B is 2. This is where we are searching
            '~~> 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
            
            '~~> tikrina ar rado svori
            If Not aCell Is Nothing Then
                '~~> istrina stulpeli su svoriu is tam tikros zonos
                aCell.Delete
            Else '<~~ jei neras
                MsgBox TextBox6.Value & " nera tokio svorio"
            Exit Sub
            End If
            
    End With
    
    
    
    End Sub


    The problem is when user inputs few weights correctly and later on he makes a mistake script already deleted the correct ones from "stock" worksheet.

  2. #2
    New Member
    Join Date
    Aug 2018
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Userform help

    Hey Guys,

    I am still willing someone could help me with this.
    Maybe I need to clarify my problem more detail?

  3. #3
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,487
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Userform help

    When you run a Macro and it performs several task then later you realize you made a mistake when entering values into the macro. It is nearly impossible to go back and reverse what the macro did.
    The best thing to do is close the Workbook do not save the changes and start over. Unless you can manually correct the mistake. There is no way to tell the Macro to reverse all the actions it performed.
    At least as far as I know.

    Now you can have checks in the macro to check for certain things before completely running the macro and have the macro stop if needed. Like you could tell the macro to check and see if all 9 Textboxs had data in them and if not give you a warning or even stop the script if needed.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    24,042
    Post Thanks / Like
    Mentioned
    409 Post(s)
    Tagged
    43 Thread(s)

    Default Re: Userform help

    Cross posted http://www.vbaexpress.com/forum/show...-help&p=382843

    While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
    This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    New Member
    Join Date
    Aug 2018
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Userform help

    Quote Originally Posted by My Aswer Is This View Post
    When you run a Macro and it performs several task then later you realize you made a mistake when entering values into the macro. It is nearly impossible to go back and reverse what the macro did.
    The best thing to do is close the Workbook do not save the changes and start over. Unless you can manually correct the mistake. There is no way to tell the Macro to reverse all the actions it performed.
    At least as far as I know.

    Now you can have checks in the macro to check for certain things before completely running the macro and have the macro stop if needed. Like you could tell the macro to check and see if all 9 Textboxs had data in them and if not give you a warning or even stop the script if needed.
    Thanks for the idea, will try to do this.

    Quote Originally Posted by Fluff View Post
    Cross posted http://www.vbaexpress.com/forum/show...-help&p=382843

    While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
    This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
    Sorry for that, other forum seems dead

  6. #6
    New Member
    Join Date
    Aug 2018
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Userform help

    Quote Originally Posted by My Aswer Is This View Post
    When you run a Macro and it performs several task then later you realize you made a mistake when entering values into the macro. It is nearly impossible to go back and reverse what the macro did.
    The best thing to do is close the Workbook do not save the changes and start over. Unless you can manually correct the mistake. There is no way to tell the Macro to reverse all the actions it performed.
    At least as far as I know.

    Now you can have checks in the macro to check for certain things before completely running the macro and have the macro stop if needed. Like you could tell the macro to check and see if all 9 Textboxs had data in them and if not give you a warning or even stop the script if needed.
    Your solution worked out, first i check if there is duplicates later on they are deleted.
    Now for some reason I have another problem with Object required error.
    It happens when textbox is blank, if textbox value is right it goes to next one, where could be the possible problem?

    Code:
      strSearch = TextBox6.Value
    
            '~~> Column A is Column 1 so Column B is 2. This is where we are searching
            '~~> 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
            
    
    
            If Not aCell Is Nothing Then
                aCell.Delete
            End If
            
            strSearch = TextBox7.Value
    
    
            '~~> Column A is Column 1 so Column B is 2. This is where we are searching
            '~~> xlWhole is used in the code below so that we find a complete match
            
            If ComboBox2.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 ComboBox2.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 ComboBox2.Value = "Z3" Then
            Set aCell = .Columns(3).Find(What:=strSearch, LookIn:=xlValues, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
            
            End If
            
            If Not aCell Is Nothing Then
                aCell.Delete
            End If
    .....

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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