Mixing
Control mandatory fields from proceeding
0
so i have a user form that i am working on for a while and i have made some fields mandatory using the code below
If Me.DateBox.Value = "" Then
DateBox.BackColor = vbRed
MsgBox "Date Field Can Not be Empty"
End If
now the problem is although this does mark the empty field red and display a message, it just allow the user to processed exporting data to excel after executing the above code. but what i want it to do is to not let the user transfer information without filling the empty text box.
here is the full code..
Private Sub CommandButton1_Click()
Dim addlist As Range
Dim x As Integer
Dim wf As WorksheetFunction
Dim y As Integer
Dim addlist2 As Range
Dim lNextRow As Long
Dim ans As Long
Set wf = Application.WorksheetFunction
Set addlist = Sheet1.Cells(Rows.Count, 7).End(xlUp).Offset(1, 0)
Set addlist2 = Sheet1.Cells(Rows.Count, 7).End(xlUp).Offset(1, 1)
For x = 1 To WortSelector.ListCount - 1
If Me.WortSelector.Selected(x) Then
addlist = Me.WortSelector.List(x)
Set addlist = addlist.Offset(1, 0)
addlist2 = Me.WortSelector.List(x)
Set addlist2 = addlist2.Offset(1, 0)
End If
Next x
lNextRow = Cells(Rows.Count, 2).End(xlUp).Row + 1
Cells(lNextRow, 2) = DateBox.Text
Cells(lNextRow, 3) = PrBox.Text
Cells(lNextRow, 4) = BrewBox.Text
Cells(lNextRow + 1, 9) = RmBox1.Text
Cells(lNextRow, 10) = OgBox.Text
Cells(lNextRow + 2, 9) = RmBox2.Text
Cells(lNextRow + 3, 9) = RmBox3.Text
Cells(lNextRow + 4, 9) = RmBox4.Text
Cells(lNextRow + 5, 9) = RmBox5.Text
Cells(lNextRow + 6, 9) = RmBox6.Text
Cells(lNextRow + 7, 9) = RmBox7.Text
Cells(lNextRow + 8, 9) = RmBox8.Text
Cells(lNextRow + 9, 9) = RmBox9.Text
Cells(lNextRow + 10, 9) = RmBox10.Text
Cells(lNextRow + 11, 9) = RmBox11.Text
Cells(lNextRow + 12, 9) = RmBox12.Text
Cells(lNextRow + 1, 8) = rm1
Cells(lNextRow + 2, 8) = rm2
Cells(lNextRow + 3, 8) = rm3
Cells(lNextRow + 4, 8) = rm4
Cells(lNextRow + 5, 8) = rm5
Cells(lNextRow + 6, 8) = rm6
Cells(lNextRow + 7, 8) = rm7
Cells(lNextRow + 8, 8) = rm8
Cells(lNextRow + 9, 8) = rm9
Cells(lNextRow + 10, 8) = rm10
Cells(lNextRow + 11, 8) = rm11
Cells(lNextRow + 12, 8) = rm12
Cells(lNextRow, 9) = VolumeBox.Text
Do
Set addlist = Sheet1.Cells(Rows.Count, 7).End(xlUp).Offset(1, 0)
For x = 1 To WortSelector.ListCount - 1
If Me.WortSelector.Selected(x) Then
addlist = Me.WortSelector.List(x)
End If
Next x
lNextRow = Cells(Rows.Count, 2).End(xlUp).Row + 1
Cells(lNextRow, 2) = DateBox.Text
Cells(lNextRow, 3) = PrBox.Text
Cells(lNextRow, 4) = BrewBox.Text
Loop Until Sheet1.Cells(lNextRow + 1, 8).Value = ""
If Me.DateBox.Value = "" Then
DateBox.BackColor = vbRed
MsgBox "Date Field Can Not be Empty"
End If
If Me.PrBox.Value = "" Then
PrBox.BackColor = vbRed
MsgBox "PR No. Field Can Not be Empty"
End If
If Me.BrewBox.Value = "" Then
BrewBox.BackColor = vbRed
MsgBox "Brew Number Field Can Not be Empty"
End If
End Sub
Thanks!
excel vba
asked Jan 2 at 8:26
ermias dillensawermias dillensaw
54
add a comment |
0
so i have a user form that i am working on for a while and i have made some fields mandatory using the code below
If Me.DateBox.Value = "" Then
DateBox.BackColor = vbRed
MsgBox "Date Field Can Not be Empty"
End If
now the problem is although this does mark the empty field red and display a message, it just allow the user to processed exporting data to excel after executing the above code. but what i want it to do is to not let the user transfer information without filling the empty text box.
here is the full code..
Private Sub CommandButton1_Click()
Dim addlist As Range
Dim x As Integer
Dim wf As WorksheetFunction
Dim y As Integer
Dim addlist2 As Range
Dim lNextRow As Long
Dim ans As Long
Set wf = Application.WorksheetFunction
Set addlist = Sheet1.Cells(Rows.Count, 7).End(xlUp).Offset(1, 0)
Set addlist2 = Sheet1.Cells(Rows.Count, 7).End(xlUp).Offset(1, 1)
For x = 1 To WortSelector.ListCount - 1
If Me.WortSelector.Selected(x) Then
addlist = Me.WortSelector.List(x)
Set addlist = addlist.Offset(1, 0)
addlist2 = Me.WortSelector.List(x)
Set addlist2 = addlist2.Offset(1, 0)
End If
Next x
lNextRow = Cells(Rows.Count, 2).End(xlUp).Row + 1
Cells(lNextRow, 2) = DateBox.Text
Cells(lNextRow, 3) = PrBox.Text
Cells(lNextRow, 4) = BrewBox.Text
Cells(lNextRow + 1, 9) = RmBox1.Text
Cells(lNextRow, 10) = OgBox.Text
Cells(lNextRow + 2, 9) = RmBox2.Text
Cells(lNextRow + 3, 9) = RmBox3.Text
Cells(lNextRow + 4, 9) = RmBox4.Text
Cells(lNextRow + 5, 9) = RmBox5.Text
Cells(lNextRow + 6, 9) = RmBox6.Text
Cells(lNextRow + 7, 9) = RmBox7.Text
Cells(lNextRow + 8, 9) = RmBox8.Text
Cells(lNextRow + 9, 9) = RmBox9.Text
Cells(lNextRow + 10, 9) = RmBox10.Text
Cells(lNextRow + 11, 9) = RmBox11.Text
Cells(lNextRow + 12, 9) = RmBox12.Text
Cells(lNextRow + 1, 8) = rm1
Cells(lNextRow + 2, 8) = rm2
Cells(lNextRow + 3, 8) = rm3
Cells(lNextRow + 4, 8) = rm4
Cells(lNextRow + 5, 8) = rm5
Cells(lNextRow + 6, 8) = rm6
Cells(lNextRow + 7, 8) = rm7
Cells(lNextRow + 8, 8) = rm8
Cells(lNextRow + 9, 8) = rm9
Cells(lNextRow + 10, 8) = rm10
Cells(lNextRow + 11, 8) = rm11
Cells(lNextRow + 12, 8) = rm12
Cells(lNextRow, 9) = VolumeBox.Text
Do
Set addlist = Sheet1.Cells(Rows.Count, 7).End(xlUp).Offset(1, 0)
For x = 1 To WortSelector.ListCount - 1
If Me.WortSelector.Selected(x) Then
addlist = Me.WortSelector.List(x)
End If
Next x
lNextRow = Cells(Rows.Count, 2).End(xlUp).Row + 1
Cells(lNextRow, 2) = DateBox.Text
Cells(lNextRow, 3) = PrBox.Text
Cells(lNextRow, 4) = BrewBox.Text
Loop Until Sheet1.Cells(lNextRow + 1, 8).Value = ""
If Me.DateBox.Value = "" Then
DateBox.BackColor = vbRed
MsgBox "Date Field Can Not be Empty"
End If
If Me.PrBox.Value = "" Then
PrBox.BackColor = vbRed
MsgBox "PR No. Field Can Not be Empty"
End If
If Me.BrewBox.Value = "" Then
BrewBox.BackColor = vbRed
MsgBox "Brew Number Field Can Not be Empty"
End If
End Sub
Thanks!
excel vba
asked Jan 2 at 8:26
ermias dillensawermias dillensaw
54
Apply better logic to your code: After the CommandButton is clicked first check if PrBox is not empty/correctly filled. If it isn't, set focus on the red PrBox and exit the sub. Repeat with BrewBox. Only after the form is found to be filled correctly start transferring data elsewhere.
– Variatus
Jan 2 at 8:42
add a comment |
0
0
0
0
so i have a user form that i am working on for a while and i have made some fields mandatory using the code below
If Me.DateBox.Value = "" Then
DateBox.BackColor = vbRed
MsgBox "Date Field Can Not be Empty"
End If
now the problem is although this does mark the empty field red and display a message, it just allow the user to processed exporting data to excel after executing the above code. but what i want it to do is to not let the user transfer information without filling the empty text box.
here is the full code..
Private Sub CommandButton1_Click()
Dim addlist As Range
Dim x As Integer
Dim wf As WorksheetFunction
Dim y As Integer
Dim addlist2 As Range
Dim lNextRow As Long
Dim ans As Long
Set wf = Application.WorksheetFunction
Set addlist = Sheet1.Cells(Rows.Count, 7).End(xlUp).Offset(1, 0)
Set addlist2 = Sheet1.Cells(Rows.Count, 7).End(xlUp).Offset(1, 1)
For x = 1 To WortSelector.ListCount - 1
If Me.WortSelector.Selected(x) Then
addlist = Me.WortSelector.List(x)
Set addlist = addlist.Offset(1, 0)
addlist2 = Me.WortSelector.List(x)
Set addlist2 = addlist2.Offset(1, 0)
End If
Next x
lNextRow = Cells(Rows.Count, 2).End(xlUp).Row + 1
Cells(lNextRow, 2) = DateBox.Text
Cells(lNextRow, 3) = PrBox.Text
Cells(lNextRow, 4) = BrewBox.Text
Cells(lNextRow + 1, 9) = RmBox1.Text
Cells(lNextRow, 10) = OgBox.Text
Cells(lNextRow + 2, 9) = RmBox2.Text
Cells(lNextRow + 3, 9) = RmBox3.Text
Cells(lNextRow + 4, 9) = RmBox4.Text
Cells(lNextRow + 5, 9) = RmBox5.Text
Cells(lNextRow + 6, 9) = RmBox6.Text
Cells(lNextRow + 7, 9) = RmBox7.Text
Cells(lNextRow + 8, 9) = RmBox8.Text
Cells(lNextRow + 9, 9) = RmBox9.Text
Cells(lNextRow + 10, 9) = RmBox10.Text
Cells(lNextRow + 11, 9) = RmBox11.Text
Cells(lNextRow + 12, 9) = RmBox12.Text
Cells(lNextRow + 1, 8) = rm1
Cells(lNextRow + 2, 8) = rm2
Cells(lNextRow + 3, 8) = rm3
Cells(lNextRow + 4, 8) = rm4
Cells(lNextRow + 5, 8) = rm5
Cells(lNextRow + 6, 8) = rm6
Cells(lNextRow + 7, 8) = rm7
Cells(lNextRow + 8, 8) = rm8
Cells(lNextRow + 9, 8) = rm9
Cells(lNextRow + 10, 8) = rm10
Cells(lNextRow + 11, 8) = rm11
Cells(lNextRow + 12, 8) = rm12
Cells(lNextRow, 9) = VolumeBox.Text
Do
Set addlist = Sheet1.Cells(Rows.Count, 7).End(xlUp).Offset(1, 0)
For x = 1 To WortSelector.ListCount - 1
If Me.WortSelector.Selected(x) Then
addlist = Me.WortSelector.List(x)
End If
Next x
lNextRow = Cells(Rows.Count, 2).End(xlUp).Row + 1
Cells(lNextRow, 2) = DateBox.Text
Cells(lNextRow, 3) = PrBox.Text
Cells(lNextRow, 4) = BrewBox.Text
Loop Until Sheet1.Cells(lNextRow + 1, 8).Value = ""
If Me.DateBox.Value = "" Then
DateBox.BackColor = vbRed
MsgBox "Date Field Can Not be Empty"
End If
If Me.PrBox.Value = "" Then
PrBox.BackColor = vbRed
MsgBox "PR No. Field Can Not be Empty"
End If
If Me.BrewBox.Value = "" Then
BrewBox.BackColor = vbRed
MsgBox "Brew Number Field Can Not be Empty"
End If
End Sub
Thanks!
excel vba
asked Jan 2 at 8:26
ermias dillensawermias dillensaw
54
so i have a user form that i am working on for a while and i have made some fields mandatory using the code below
If Me.DateBox.Value = "" Then
DateBox.BackColor = vbRed
MsgBox "Date Field Can Not be Empty"
End If
now the problem is although this does mark the empty field red and display a message, it just allow the user to processed exporting data to excel after executing the above code. but what i want it to do is to not let the user transfer information without filling the empty text box.
here is the full code..
Private Sub CommandButton1_Click()
Dim addlist As Range
Dim x As Integer
Dim wf As WorksheetFunction
Dim y As Integer
Dim addlist2 As Range
Dim lNextRow As Long
Dim ans As Long
Set wf = Application.WorksheetFunction
Set addlist = Sheet1.Cells(Rows.Count, 7).End(xlUp).Offset(1, 0)
Set addlist2 = Sheet1.Cells(Rows.Count, 7).End(xlUp).Offset(1, 1)
For x = 1 To WortSelector.ListCount - 1
If Me.WortSelector.Selected(x) Then
addlist = Me.WortSelector.List(x)
Set addlist = addlist.Offset(1, 0)
addlist2 = Me.WortSelector.List(x)
Set addlist2 = addlist2.Offset(1, 0)
End If
Next x
lNextRow = Cells(Rows.Count, 2).End(xlUp).Row + 1
Cells(lNextRow, 2) = DateBox.Text
Cells(lNextRow, 3) = PrBox.Text
Cells(lNextRow, 4) = BrewBox.Text
Cells(lNextRow + 1, 9) = RmBox1.Text
Cells(lNextRow, 10) = OgBox.Text
Cells(lNextRow + 2, 9) = RmBox2.Text
Cells(lNextRow + 3, 9) = RmBox3.Text
Cells(lNextRow + 4, 9) = RmBox4.Text
Cells(lNextRow + 5, 9) = RmBox5.Text
Cells(lNextRow + 6, 9) = RmBox6.Text
Cells(lNextRow + 7, 9) = RmBox7.Text
Cells(lNextRow + 8, 9) = RmBox8.Text
Cells(lNextRow + 9, 9) = RmBox9.Text
Cells(lNextRow + 10, 9) = RmBox10.Text
Cells(lNextRow + 11, 9) = RmBox11.Text
Cells(lNextRow + 12, 9) = RmBox12.Text
Cells(lNextRow + 1, 8) = rm1
Cells(lNextRow + 2, 8) = rm2
Cells(lNextRow + 3, 8) = rm3
Cells(lNextRow + 4, 8) = rm4
Cells(lNextRow + 5, 8) = rm5
Cells(lNextRow + 6, 8) = rm6
Cells(lNextRow + 7, 8) = rm7
Cells(lNextRow + 8, 8) = rm8
Cells(lNextRow + 9, 8) = rm9
Cells(lNextRow + 10, 8) = rm10
Cells(lNextRow + 11, 8) = rm11
Cells(lNextRow + 12, 8) = rm12
Cells(lNextRow, 9) = VolumeBox.Text
Do
Set addlist = Sheet1.Cells(Rows.Count, 7).End(xlUp).Offset(1, 0)
For x = 1 To WortSelector.ListCount - 1
If Me.WortSelector.Selected(x) Then
addlist = Me.WortSelector.List(x)
End If
Next x
lNextRow = Cells(Rows.Count, 2).End(xlUp).Row + 1
Cells(lNextRow, 2) = DateBox.Text
Cells(lNextRow, 3) = PrBox.Text
Cells(lNextRow, 4) = BrewBox.Text
Loop Until Sheet1.Cells(lNextRow + 1, 8).Value = ""
If Me.DateBox.Value = "" Then
DateBox.BackColor = vbRed
MsgBox "Date Field Can Not be Empty"
End If
If Me.PrBox.Value = "" Then
PrBox.BackColor = vbRed
MsgBox "PR No. Field Can Not be Empty"
End If
If Me.BrewBox.Value = "" Then
BrewBox.BackColor = vbRed
MsgBox "Brew Number Field Can Not be Empty"
End If
End Sub
Thanks!
excel vba
excel vba
asked Jan 2 at 8:26
ermias dillensawermias dillensaw
54
asked Jan 2 at 8:26
ermias dillensawermias dillensaw
54
asked Jan 2 at 8:26
ermias dillensawermias dillensaw
54
asked Jan 2 at 8:26
ermias dillensawermias dillensaw
54
asked Jan 2 at 8:26
ermias dillensawermias dillensaw
54
54
Apply better logic to your code: After the CommandButton is clicked first check if PrBox is not empty/correctly filled. If it isn't, set focus on the red PrBox and exit the sub. Repeat with BrewBox. Only after the form is found to be filled correctly start transferring data elsewhere.
– Variatus
Jan 2 at 8:42
add a comment |
Apply better logic to your code: After the CommandButton is clicked first check if PrBox is not empty/correctly filled. If it isn't, set focus on the red PrBox and exit the sub. Repeat with BrewBox. Only after the form is found to be filled correctly start transferring data elsewhere.
– Variatus
Jan 2 at 8:42
Apply better logic to your code: After the CommandButton is clicked first check if PrBox is not empty/correctly filled. If it isn't, set focus on the red PrBox and exit the sub. Repeat with BrewBox. Only after the form is found to be filled correctly start transferring data elsewhere.
– Variatus
Jan 2 at 8:42
Apply better logic to your code: After the CommandButton is clicked first check if PrBox is not empty/correctly filled. If it isn't, set focus on the red PrBox and exit the sub. Repeat with BrewBox. Only after the form is found to be filled correctly start transferring data elsewhere.
– Variatus
Jan 2 at 8:42
add a comment |
1 Answer
1
active
oldest
votes
0
Add Exit Sub as shown below. Its stops code execution further.
If Me.DateBox.Value = "" Then
DateBox.BackColor = vbRed
MsgBox "Date Field Can Not be Empty"
Exit sub
End If
Private Sub CommandButton1_Click()
Dim addlist As Range
Dim x As Integer
Dim wf As WorksheetFunction
Dim y As Integer
Dim addlist2 As Range
Dim lNextRow As Long
Dim ans As Long
'Validation start
If Me.DateBox.Value = "" Then
DateBox.BackColor = vbRed
MsgBox "Date Field Can Not be Empty"
Exit Sub
End If
If Me.PrBox.Value = "" Then
PrBox.BackColor = vbRed
MsgBox "PR No. Field Can Not be Empty"
Exit Sub
End If
If Me.BrewBox.Value = "" Then
BrewBox.BackColor = vbRed
MsgBox "Brew Number Field Can Not be Empty"
Exit Sub
End If
'Validation end
Set wf = Application.WorksheetFunction
Set addlist = Sheet1.Cells(Rows.Count, 7).End(xlUp).Offset(1, 0)
Set addlist2 = Sheet1.Cells(Rows.Count, 7).End(xlUp).Offset(1, 1)
For x = 1 To WortSelector.ListCount - 1
If Me.WortSelector.Selected(x) Then
addlist = Me.WortSelector.List(x)
Set addlist = addlist.Offset(1, 0)
addlist2 = Me.WortSelector.List(x)
Set addlist2 = addlist2.Offset(1, 0)
End If
Next x
lNextRow = Cells(Rows.Count, 2).End(xlUp).Row + 1
Cells(lNextRow, 2) = DateBox.Text
Cells(lNextRow, 3) = PrBox.Text
Cells(lNextRow, 4) = BrewBox.Text
Cells(lNextRow + 1, 9) = RmBox1.Text
Cells(lNextRow, 10) = OgBox.Text
Cells(lNextRow + 2, 9) = RmBox2.Text
Cells(lNextRow + 3, 9) = RmBox3.Text
Cells(lNextRow + 4, 9) = RmBox4.Text
Cells(lNextRow + 5, 9) = RmBox5.Text
Cells(lNextRow + 6, 9) = RmBox6.Text
Cells(lNextRow + 7, 9) = RmBox7.Text
Cells(lNextRow + 8, 9) = RmBox8.Text
Cells(lNextRow + 9, 9) = RmBox9.Text
Cells(lNextRow + 10, 9) = RmBox10.Text
Cells(lNextRow + 11, 9) = RmBox11.Text
Cells(lNextRow + 12, 9) = RmBox12.Text
Cells(lNextRow + 1, 8) = rm1
Cells(lNextRow + 2, 8) = rm2
Cells(lNextRow + 3, 8) = rm3
Cells(lNextRow + 4, 8) = rm4
Cells(lNextRow + 5, 8) = rm5
Cells(lNextRow + 6, 8) = rm6
Cells(lNextRow + 7, 8) = rm7
Cells(lNextRow + 8, 8) = rm8
Cells(lNextRow + 9, 8) = rm9
Cells(lNextRow + 10, 8) = rm10
Cells(lNextRow + 11, 8) = rm11
Cells(lNextRow + 12, 8) = rm12
Cells(lNextRow, 9) = VolumeBox.Text
Do
Set addlist = Sheet1.Cells(Rows.Count, 7).End(xlUp).Offset(1, 0)
For x = 1 To WortSelector.ListCount - 1
If Me.WortSelector.Selected(x) Then
addlist = Me.WortSelector.List(x)
End If
Next x
lNextRow = Cells(Rows.Count, 2).End(xlUp).Row + 1
Cells(lNextRow, 2) = DateBox.Text
Cells(lNextRow, 3) = PrBox.Text
Cells(lNextRow, 4) = BrewBox.Text
Loop Until Sheet1.Cells(lNextRow + 1, 8).Value = ""
End Sub
answered Jan 2 at 8:34
SantoshSantosh
10.7k23160
Hi Santosh, i did but still nothing.. it does the same thing
– ermias dillensaw
Jan 2 at 8:38
Move your validation statements after the code starts executing and not at the end.
– Santosh
Jan 2 at 8:42
@ermiasdillensaw Checkout the updated answer
– Santosh
Jan 2 at 8:45
Thanks a lot Santosh, works now..
– ermias dillensaw
Jan 2 at 9:35
i have another question if i may.. how do i make sure that the user never enters the same value twice in BrewBox.text? please let me know if i can do that
– ermias dillensaw
Jan 2 at 9:36
|
show 12 more comments
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54003252%2fcontrol-mandatory-fields-from-proceeding%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
Add Exit Sub as shown below. Its stops code execution further.
If Me.DateBox.Value = "" Then
DateBox.BackColor = vbRed
MsgBox "Date Field Can Not be Empty"
Exit sub
End If
Private Sub CommandButton1_Click()
Dim addlist As Range
Dim x As Integer
Dim wf As WorksheetFunction
Dim y As Integer
Dim addlist2 As Range
Dim lNextRow As Long
Dim ans As Long
'Validation start
If Me.DateBox.Value = "" Then
DateBox.BackColor = vbRed
MsgBox "Date Field Can Not be Empty"
Exit Sub
End If
If Me.PrBox.Value = "" Then
PrBox.BackColor = vbRed
MsgBox "PR No. Field Can Not be Empty"
Exit Sub
End If
If Me.BrewBox.Value = "" Then
BrewBox.BackColor = vbRed
MsgBox "Brew Number Field Can Not be Empty"
Exit Sub
End If
'Validation end
Set wf = Application.WorksheetFunction
Set addlist = Sheet1.Cells(Rows.Count, 7).End(xlUp).Offset(1, 0)
Set addlist2 = Sheet1.Cells(Rows.Count, 7).End(xlUp).Offset(1, 1)
For x = 1 To WortSelector.ListCount - 1
If Me.WortSelector.Selected(x) Then
addlist = Me.WortSelector.List(x)
Set addlist = addlist.Offset(1, 0)
addlist2 = Me.WortSelector.List(x)
Set addlist2 = addlist2.Offset(1, 0)
End If
Next x
lNextRow = Cells(Rows.Count, 2).End(xlUp).Row + 1
Cells(lNextRow, 2) = DateBox.Text
Cells(lNextRow, 3) = PrBox.Text
Cells(lNextRow, 4) = BrewBox.Text
Cells(lNextRow + 1, 9) = RmBox1.Text
Cells(lNextRow, 10) = OgBox.Text
Cells(lNextRow + 2, 9) = RmBox2.Text
Cells(lNextRow + 3, 9) = RmBox3.Text
Cells(lNextRow + 4, 9) = RmBox4.Text
Cells(lNextRow + 5, 9) = RmBox5.Text
Cells(lNextRow + 6, 9) = RmBox6.Text
Cells(lNextRow + 7, 9) = RmBox7.Text
Cells(lNextRow + 8, 9) = RmBox8.Text
Cells(lNextRow + 9, 9) = RmBox9.Text
Cells(lNextRow + 10, 9) = RmBox10.Text
Cells(lNextRow + 11, 9) = RmBox11.Text
Cells(lNextRow + 12, 9) = RmBox12.Text
Cells(lNextRow + 1, 8) = rm1
Cells(lNextRow + 2, 8) = rm2
Cells(lNextRow + 3, 8) = rm3
Cells(lNextRow + 4, 8) = rm4
Cells(lNextRow + 5, 8) = rm5
Cells(lNextRow + 6, 8) = rm6
Cells(lNextRow + 7, 8) = rm7
Cells(lNextRow + 8, 8) = rm8
Cells(lNextRow + 9, 8) = rm9
Cells(lNextRow + 10, 8) = rm10
Cells(lNextRow + 11, 8) = rm11
Cells(lNextRow + 12, 8) = rm12
Cells(lNextRow, 9) = VolumeBox.Text
Do
Set addlist = Sheet1.Cells(Rows.Count, 7).End(xlUp).Offset(1, 0)
For x = 1 To WortSelector.ListCount - 1
If Me.WortSelector.Selected(x) Then
addlist = Me.WortSelector.List(x)
End If
Next x
lNextRow = Cells(Rows.Count, 2).End(xlUp).Row + 1
Cells(lNextRow, 2) = DateBox.Text
Cells(lNextRow, 3) = PrBox.Text
Cells(lNextRow, 4) = BrewBox.Text
Loop Until Sheet1.Cells(lNextRow + 1, 8).Value = ""
End Sub
answered Jan 2 at 8:34
SantoshSantosh
10.7k23160
Hi Santosh, i did but still nothing.. it does the same thing
– ermias dillensaw
Jan 2 at 8:38
Move your validation statements after the code starts executing and not at the end.
– Santosh
Jan 2 at 8:42
@ermiasdillensaw Checkout the updated answer
– Santosh
Jan 2 at 8:45
Thanks a lot Santosh, works now..
– ermias dillensaw
Jan 2 at 9:35
i have another question if i may.. how do i make sure that the user never enters the same value twice in BrewBox.text? please let me know if i can do that
– ermias dillensaw
Jan 2 at 9:36
|
show 12 more comments
0
Add Exit Sub as shown below. Its stops code execution further.
If Me.DateBox.Value = "" Then
DateBox.BackColor = vbRed
MsgBox "Date Field Can Not be Empty"
Exit sub
End If
Private Sub CommandButton1_Click()
Dim addlist As Range
Dim x As Integer
Dim wf As WorksheetFunction
Dim y As Integer
Dim addlist2 As Range
Dim lNextRow As Long
Dim ans As Long
'Validation start
If Me.DateBox.Value = "" Then
DateBox.BackColor = vbRed
MsgBox "Date Field Can Not be Empty"
Exit Sub
End If
If Me.PrBox.Value = "" Then
PrBox.BackColor = vbRed
MsgBox "PR No. Field Can Not be Empty"
Exit Sub
End If
If Me.BrewBox.Value = "" Then
BrewBox.BackColor = vbRed
MsgBox "Brew Number Field Can Not be Empty"
Exit Sub
End If
'Validation end
Set wf = Application.WorksheetFunction
Set addlist = Sheet1.Cells(Rows.Count, 7).End(xlUp).Offset(1, 0)
Set addlist2 = Sheet1.Cells(Rows.Count, 7).End(xlUp).Offset(1, 1)
For x = 1 To WortSelector.ListCount - 1
If Me.WortSelector.Selected(x) Then
addlist = Me.WortSelector.List(x)
Set addlist = addlist.Offset(1, 0)
addlist2 = Me.WortSelector.List(x)
Set addlist2 = addlist2.Offset(1, 0)
End If
Next x
lNextRow = Cells(Rows.Count, 2).End(xlUp).Row + 1
Cells(lNextRow, 2) = DateBox.Text
Cells(lNextRow, 3) = PrBox.Text
Cells(lNextRow, 4) = BrewBox.Text
Cells(lNextRow + 1, 9) = RmBox1.Text
Cells(lNextRow, 10) = OgBox.Text
Cells(lNextRow + 2, 9) = RmBox2.Text
Cells(lNextRow + 3, 9) = RmBox3.Text
Cells(lNextRow + 4, 9) = RmBox4.Text
Cells(lNextRow + 5, 9) = RmBox5.Text
Cells(lNextRow + 6, 9) = RmBox6.Text
Cells(lNextRow + 7, 9) = RmBox7.Text
Cells(lNextRow + 8, 9) = RmBox8.Text
Cells(lNextRow + 9, 9) = RmBox9.Text
Cells(lNextRow + 10, 9) = RmBox10.Text
Cells(lNextRow + 11, 9) = RmBox11.Text
Cells(lNextRow + 12, 9) = RmBox12.Text
Cells(lNextRow + 1, 8) = rm1
Cells(lNextRow + 2, 8) = rm2
Cells(lNextRow + 3, 8) = rm3
Cells(lNextRow + 4, 8) = rm4
Cells(lNextRow + 5, 8) = rm5
Cells(lNextRow + 6, 8) = rm6
Cells(lNextRow + 7, 8) = rm7
Cells(lNextRow + 8, 8) = rm8
Cells(lNextRow + 9, 8) = rm9
Cells(lNextRow + 10, 8) = rm10
Cells(lNextRow + 11, 8) = rm11
Cells(lNextRow + 12, 8) = rm12
Cells(lNextRow, 9) = VolumeBox.Text
Do
Set addlist = Sheet1.Cells(Rows.Count, 7).End(xlUp).Offset(1, 0)
For x = 1 To WortSelector.ListCount - 1
If Me.WortSelector.Selected(x) Then
addlist = Me.WortSelector.List(x)
End If
Next x
lNextRow = Cells(Rows.Count, 2).End(xlUp).Row + 1
Cells(lNextRow, 2) = DateBox.Text
Cells(lNextRow, 3) = PrBox.Text
Cells(lNextRow, 4) = BrewBox.Text
Loop Until Sheet1.Cells(lNextRow + 1, 8).Value = ""
End Sub
answered Jan 2 at 8:34
SantoshSantosh
10.7k23160
Hi Santosh, i did but still nothing.. it does the same thing
– ermias dillensaw
Jan 2 at 8:38
Move your validation statements after the code starts executing and not at the end.
– Santosh
Jan 2 at 8:42
@ermiasdillensaw Checkout the updated answer
– Santosh
Jan 2 at 8:45
Thanks a lot Santosh, works now..
– ermias dillensaw
Jan 2 at 9:35
i have another question if i may.. how do i make sure that the user never enters the same value twice in BrewBox.text? please let me know if i can do that
– ermias dillensaw
Jan 2 at 9:36
|
show 12 more comments
0
0
0
Add Exit Sub as shown below. Its stops code execution further.
If Me.DateBox.Value = "" Then
DateBox.BackColor = vbRed
MsgBox "Date Field Can Not be Empty"
Exit sub
End If
Private Sub CommandButton1_Click()
Dim addlist As Range
Dim x As Integer
Dim wf As WorksheetFunction
Dim y As Integer
Dim addlist2 As Range
Dim lNextRow As Long
Dim ans As Long
'Validation start
If Me.DateBox.Value = "" Then
DateBox.BackColor = vbRed
MsgBox "Date Field Can Not be Empty"
Exit Sub
End If
If Me.PrBox.Value = "" Then
PrBox.BackColor = vbRed
MsgBox "PR No. Field Can Not be Empty"
Exit Sub
End If
If Me.BrewBox.Value = "" Then
BrewBox.BackColor = vbRed
MsgBox "Brew Number Field Can Not be Empty"
Exit Sub
End If
'Validation end
Set wf = Application.WorksheetFunction
Set addlist = Sheet1.Cells(Rows.Count, 7).End(xlUp).Offset(1, 0)
Set addlist2 = Sheet1.Cells(Rows.Count, 7).End(xlUp).Offset(1, 1)
For x = 1 To WortSelector.ListCount - 1
If Me.WortSelector.Selected(x) Then
addlist = Me.WortSelector.List(x)
Set addlist = addlist.Offset(1, 0)
addlist2 = Me.WortSelector.List(x)
Set addlist2 = addlist2.Offset(1, 0)
End If
Next x
lNextRow = Cells(Rows.Count, 2).End(xlUp).Row + 1
Cells(lNextRow, 2) = DateBox.Text
Cells(lNextRow, 3) = PrBox.Text
Cells(lNextRow, 4) = BrewBox.Text
Cells(lNextRow + 1, 9) = RmBox1.Text
Cells(lNextRow, 10) = OgBox.Text
Cells(lNextRow + 2, 9) = RmBox2.Text
Cells(lNextRow + 3, 9) = RmBox3.Text
Cells(lNextRow + 4, 9) = RmBox4.Text
Cells(lNextRow + 5, 9) = RmBox5.Text
Cells(lNextRow + 6, 9) = RmBox6.Text
Cells(lNextRow + 7, 9) = RmBox7.Text
Cells(lNextRow + 8, 9) = RmBox8.Text
Cells(lNextRow + 9, 9) = RmBox9.Text
Cells(lNextRow + 10, 9) = RmBox10.Text
Cells(lNextRow + 11, 9) = RmBox11.Text
Cells(lNextRow + 12, 9) = RmBox12.Text
Cells(lNextRow + 1, 8) = rm1
Cells(lNextRow + 2, 8) = rm2
Cells(lNextRow + 3, 8) = rm3
Cells(lNextRow + 4, 8) = rm4
Cells(lNextRow + 5, 8) = rm5
Cells(lNextRow + 6, 8) = rm6
Cells(lNextRow + 7, 8) = rm7
Cells(lNextRow + 8, 8) = rm8
Cells(lNextRow + 9, 8) = rm9
Cells(lNextRow + 10, 8) = rm10
Cells(lNextRow + 11, 8) = rm11
Cells(lNextRow + 12, 8) = rm12
Cells(lNextRow, 9) = VolumeBox.Text
Do
Set addlist = Sheet1.Cells(Rows.Count, 7).End(xlUp).Offset(1, 0)
For x = 1 To WortSelector.ListCount - 1
If Me.WortSelector.Selected(x) Then
addlist = Me.WortSelector.List(x)
End If
Next x
lNextRow = Cells(Rows.Count, 2).End(xlUp).Row + 1
Cells(lNextRow, 2) = DateBox.Text
Cells(lNextRow, 3) = PrBox.Text
Cells(lNextRow, 4) = BrewBox.Text
Loop Until Sheet1.Cells(lNextRow + 1, 8).Value = ""
End Sub
answered Jan 2 at 8:34
SantoshSantosh
10.7k23160
Add Exit Sub as shown below. Its stops code execution further.
If Me.DateBox.Value = "" Then
DateBox.BackColor = vbRed
MsgBox "Date Field Can Not be Empty"
Exit sub
End If
Private Sub CommandButton1_Click()
Dim addlist As Range
Dim x As Integer
Dim wf As WorksheetFunction
Dim y As Integer
Dim addlist2 As Range
Dim lNextRow As Long
Dim ans As Long
'Validation start
If Me.DateBox.Value = "" Then
DateBox.BackColor = vbRed
MsgBox "Date Field Can Not be Empty"
Exit Sub
End If
If Me.PrBox.Value = "" Then
PrBox.BackColor = vbRed
MsgBox "PR No. Field Can Not be Empty"
Exit Sub
End If
If Me.BrewBox.Value = "" Then
BrewBox.BackColor = vbRed
MsgBox "Brew Number Field Can Not be Empty"
Exit Sub
End If
'Validation end
Set wf = Application.WorksheetFunction
Set addlist = Sheet1.Cells(Rows.Count, 7).End(xlUp).Offset(1, 0)
Set addlist2 = Sheet1.Cells(Rows.Count, 7).End(xlUp).Offset(1, 1)
For x = 1 To WortSelector.ListCount - 1
If Me.WortSelector.Selected(x) Then
addlist = Me.WortSelector.List(x)
Set addlist = addlist.Offset(1, 0)
addlist2 = Me.WortSelector.List(x)
Set addlist2 = addlist2.Offset(1, 0)
End If
Next x
lNextRow = Cells(Rows.Count, 2).End(xlUp).Row + 1
Cells(lNextRow, 2) = DateBox.Text
Cells(lNextRow, 3) = PrBox.Text
Cells(lNextRow, 4) = BrewBox.Text
Cells(lNextRow + 1, 9) = RmBox1.Text
Cells(lNextRow, 10) = OgBox.Text
Cells(lNextRow + 2, 9) = RmBox2.Text
Cells(lNextRow + 3, 9) = RmBox3.Text
Cells(lNextRow + 4, 9) = RmBox4.Text
Cells(lNextRow + 5, 9) = RmBox5.Text
Cells(lNextRow + 6, 9) = RmBox6.Text
Cells(lNextRow + 7, 9) = RmBox7.Text
Cells(lNextRow + 8, 9) = RmBox8.Text
Cells(lNextRow + 9, 9) = RmBox9.Text
Cells(lNextRow + 10, 9) = RmBox10.Text
Cells(lNextRow + 11, 9) = RmBox11.Text
Cells(lNextRow + 12, 9) = RmBox12.Text
Cells(lNextRow + 1, 8) = rm1
Cells(lNextRow + 2, 8) = rm2
Cells(lNextRow + 3, 8) = rm3
Cells(lNextRow + 4, 8) = rm4
Cells(lNextRow + 5, 8) = rm5
Cells(lNextRow + 6, 8) = rm6
Cells(lNextRow + 7, 8) = rm7
Cells(lNextRow + 8, 8) = rm8
Cells(lNextRow + 9, 8) = rm9
Cells(lNextRow + 10, 8) = rm10
Cells(lNextRow + 11, 8) = rm11
Cells(lNextRow + 12, 8) = rm12
Cells(lNextRow, 9) = VolumeBox.Text
Do
Set addlist = Sheet1.Cells(Rows.Count, 7).End(xlUp).Offset(1, 0)
For x = 1 To WortSelector.ListCount - 1
If Me.WortSelector.Selected(x) Then
addlist = Me.WortSelector.List(x)
End If
Next x
lNextRow = Cells(Rows.Count, 2).End(xlUp).Row + 1
Cells(lNextRow, 2) = DateBox.Text
Cells(lNextRow, 3) = PrBox.Text
Cells(lNextRow, 4) = BrewBox.Text
Loop Until Sheet1.Cells(lNextRow + 1, 8).Value = ""
End Sub
answered Jan 2 at 8:34
SantoshSantosh
10.7k23160
edited Jan 2 at 8:45
answered Jan 2 at 8:34
SantoshSantosh
10.7k23160
answered Jan 2 at 8:34
SantoshSantosh
10.7k23160
answered Jan 2 at 8:34
SantoshSantosh
10.7k23160
10.7k23160
Hi Santosh, i did but still nothing.. it does the same thing
– ermias dillensaw
Jan 2 at 8:38
Move your validation statements after the code starts executing and not at the end.
– Santosh
Jan 2 at 8:42
@ermiasdillensaw Checkout the updated answer
– Santosh
Jan 2 at 8:45
Thanks a lot Santosh, works now..
– ermias dillensaw
Jan 2 at 9:35
i have another question if i may.. how do i make sure that the user never enters the same value twice in BrewBox.text? please let me know if i can do that
– ermias dillensaw
Jan 2 at 9:36
|
show 12 more comments
Hi Santosh, i did but still nothing.. it does the same thing
– ermias dillensaw
Jan 2 at 8:38
Move your validation statements after the code starts executing and not at the end.
– Santosh
Jan 2 at 8:42
@ermiasdillensaw Checkout the updated answer
– Santosh
Jan 2 at 8:45
Thanks a lot Santosh, works now..
– ermias dillensaw
Jan 2 at 9:35
i have another question if i may.. how do i make sure that the user never enters the same value twice in BrewBox.text? please let me know if i can do that
– ermias dillensaw
Jan 2 at 9:36
Hi Santosh, i did but still nothing.. it does the same thing
– ermias dillensaw
Jan 2 at 8:38
Hi Santosh, i did but still nothing.. it does the same thing
– ermias dillensaw
Jan 2 at 8:38
Move your validation statements after the code starts executing and not at the end.
– Santosh
Jan 2 at 8:42
Move your validation statements after the code starts executing and not at the end.
– Santosh
Jan 2 at 8:42
@ermiasdillensaw Checkout the updated answer
– Santosh
Jan 2 at 8:45
@ermiasdillensaw Checkout the updated answer
– Santosh
Jan 2 at 8:45
Thanks a lot Santosh, works now..
– ermias dillensaw
Jan 2 at 9:35
Thanks a lot Santosh, works now..
– ermias dillensaw
Jan 2 at 9:35
i have another question if i may.. how do i make sure that the user never enters the same value twice in BrewBox.text? please let me know if i can do that
– ermias dillensaw
Jan 2 at 9:36
i have another question if i may.. how do i make sure that the user never enters the same value twice in BrewBox.text? please let me know if i can do that
– ermias dillensaw
Jan 2 at 9:36
|
show 12 more comments
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54003252%2fcontrol-mandatory-fields-from-proceeding%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Apply better logic to your code: After the CommandButton is clicked first check if PrBox is not empty/correctly filled. If it isn't, set focus on the red PrBox and exit the sub. Repeat with BrewBox. Only after the form is found to be filled correctly start transferring data elsewhere.
– Variatus
Jan 2 at 8:42