PDA

View Full Version : Userform help



SLinas
08-08-2018, 07:16 AM
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.

22684

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
08-09-2018, 02:34 AM
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
08-10-2018, 12:44 AM
Hello,

There is my worksheet "batteries_processing" where all the data goes from user form
22693
There is "printing_sheet", which saves as .pdf after guys finishing their shift and press process button
22694
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...
22695

Fluff
08-13-2018, 04:59 AM
Cross posted https://www.mrexcel.com/forum/excel-questions/1066561-userform-help.html

Lianne
10-23-2019, 02:38 AM
Hope you could understand anything!

William22
11-29-2021, 12:43 AM
Maybe loop can solve this for you. Also visit google and search your query there. I think you will get answer quickly.

fouad
04-01-2022, 11:28 AM
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.

22684

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
04-02-2022, 01:25 AM
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. (https://www.avoid.uk.net/youtube-vanced-apk-download/)
22684

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. (https://www.avoid.uk.net/fmwhatsapp/)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
04-02-2022, 02:24 AM
@Fouad, Well that's a positive contribution to an old thread.