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
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.
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:
[/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.