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

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

[/COLOR]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[COLOR=#333333]

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.

 

[SLinas]

Hey Guys,

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

 

[My Aswer Is This]

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.

 

[Fluff]

Cross posted http://www.vbaexpress.com/forum/showthread.php?63369-Userform-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.

 

[SLinas]

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.

Cross posted http://www.vbaexpress.com/forum/showthread.php?63369-Userform-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

 

[SLinas]

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?

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