How to use "or" "and" "case" function

[davidboutche]

I have a series of tick boxes in a user form. Only if all of these tick boxes are all ticked then does the user not have to complete a statement which will be on my active document.

I had planned to write the statement out on the the active document and enclose it within a bookmark.

I would then in the userform code, a command that would only replace the bookmark if every tick box is true.

I've used the case command to delete text when a combo box is selected but now what I want to do is run the case only when the series of ticks are true.

Can I have some advice please.

This is what I'm trying to far and seems to be working:
Private Sub TheftButton_Click()
If Theftform.PresentBox = True _
And Theftform.cautionsuspectbox = True _
Then Call FillBM("statementbook", "rubbsish")

Theftform.Hide

End Sub

[lucas]

I don't understand the question. You ask about or but your requirement seems to be that all have to be checked.....I am assuming you mean checkboxes, right?

Try to clarify your question and post a dressed down version of your document so we can see what you are trying to do without having to duplicate your work...

[davidboutche]

You're quite right.. I am using 'and' not 'or'.

Yes I'm using check boxes.

I am writing this for a criminal justice system. It's basically a checking system so each officer can check their work against a list of criteria. If anyone of these is not met they will have to adjust a statement which will be kept within a book mark.

Only if every check box is true then it will fill the bookmark with other information.

What I'm doing seems to be work as below:
Private Sub TheftButton_Click()
If Theftform.PresentBox = True _
And Theftform.cautionsuspectbox = True _
And Theftform.FactBox = True _
And Theftform.Agreebox = True _
And Theftform.responsebox = True _
And Theftform.infooffenderbox = True _
Then Call FillBM("statementbook", "No need for statement")

Unload Me

End Sub

I tried to upload a copy of the whole document but I don't think it will allow it as it is a template

I think what I'm doing is right.

[lucas]

zip it up. You can upload zip files.

[davidboutche]

no can do. The work pc i'm on has no zip facillities on it. If you need it, email me with your email address and then I'll email the document back to you. Unless you can think of another way?

Edit Lucas: I removed your email addy from the public post as spambots will pick it up and start sending you spam......

check your email.

[davidboutche]

Thanks for that, you'll see why i didn't put my work email on the forum for exactly that reason.

[lucas]

David's example file attached to this post

Can you now walk us through exactly how to experience the problem you are having and explain a little better please.

[davidboutche]

Almost everything seems to be going well so far..

userform1 is opened intially
offence code DA12 is selected and the button pressed

theftform is opened.

If all the check boxes are not checked a msgbox displayed.

If all the check boxes are checked then the FillBM function replaces some text in a book mark.

What I can't seem to do, is execute more than one command for each of the scenarios... ie, if checkbox=true then do this this this and this
else do that that and that.

[lucas]

Well, you still aren't very specific so here is a generic example using if and elseif

see attachment.

Private Sub CommandButton1_Click()
If CheckBox1.Value = False And CheckBox2.Value = False Then
MsgBox "You must check one of the checkboxes"
ElseIf CheckBox1.Value = True And CheckBox2.Value = False Then
MsgBox "Checkbox 1 has been selected"
ElseIf CheckBox1.Value = False And CheckBox2.Value = True Then
MsgBox "Checkbox 2 has been selected"
ElseIf CheckBox1.Value = True And CheckBox2.Value = True Then
MsgBox "Checkbox 1 & Checkbox 2 has been selected"
End If
End Sub

[davidboutche]

Thanks for that.. it does help generally.

but, here is my code:
Private Sub TheftButton_Click()
If Theftform.PresentBox = True _
And Theftform.cautionsuspectbox = True _
And Theftform.FactBox = True _
And Theftform.Agreebox = True _
And Theftform.responsebox = True _
And Theftform.infooffenderbox = True _
Then Call FillBM("statementbook", "No need for statement") _
Else: MsgBox ("Assuming these facts took place, but you have not recorded them in your pocket note book, then you will have to complete a statement for the detection and fine to stand.")

Unload Me

End Sub

what I want it to do is more than one action when the 'then' and 'else' happens.

For example:
Then Call FillBM("statementbook", "No need for statement") _
msgbox ("There is not need for you to do a statement)

I want it to do both those things after the 'then'. Does that make sense? Thanks for being patient.

[lucas]

Let's try again. What you have now checks to see if all the checkboxes are true and if they are all checked you call fillBM and it adds text to a bookmark, right so far?

If they are all not checked it issues a messagbox......

Now what do you want it to do?

In other words are you looking for a way to trigger an event if only one checkbox is checked? If so you just need to look at the logic above and it's not simple. You will have to write code for each possible scenario.

[davidboutche]

that's all i want it to do..

but the quote mentioned above doesn't work for me.

I want it to check that they are all checked and then do two things, the fillbm and the msg box, but it will only do one before returning an error.

Have I written the syntax wrong? :banghead:

It returns "expected: end of statement"

[lucas]

Try this if I understand you correctly:
Private Sub TheftButton_Click()
If Theftform.PresentBox = True _
And Theftform.cautionsuspectbox = True _
And Theftform.FactBox = True _
And Theftform.Agreebox = True _
And Theftform.responsebox = True _
And Theftform.infooffenderbox = True _
Then Call FillBM("statementbook", "No need for statement")
Unload Me
MsgBox ("Assuming these facts took place, but you have not recorded them in your pocket note book, then you will have to complete a statement for the detection and fine to stand.")
End Sub

[davidboutche]

I'm afraid i'm still not quite there

Maybe I'm not explaining what I cant clearly.

What I want is

IF something = true
Then 'do several things like FillBM(), msgbox ("qwewqe")
else 'do this, and this and this

..in English, is something is true I want it to call a function, pump out a message box. IF the above wasn't true, I want it to call a different function, do a different message box etc etc etc

then carry on with the rest of the code.

I don't know how to make it do more than one thing in the IF procedure.

[Paul_Hossler]

Maybe you just need to bracket the procedures within If/Then/Else/Endif


If something = true Then
Call FillBM()
Call MsgBox ("qwewqe")
Else
Call This
Call And_This
Call And_This_Also
EndIf



Paul

[davidboutche]

:thumb Thanks for that. Got there in the end. It's doing just what I need now. Code as below:
Private Sub TheftButton_Click()
If Theftform.PresentBox = True _
And Theftform.cautionsuspectbox = True _
And Theftform.FactBox = True _
And Theftform.Agreebox = True _
And Theftform.responsebox = True _
And Theftform.infooffenderbox = True Then
Call FillBM("statementbook", "Attach a copy of your PNB")
MsgBox ("Make sure you attach a copy of your PNB to the PND")
Else: MsgBox ("Assuming these facts took place, but you have not recorded them in your pocket note book, then you will have to complete a statement for the detection and fine to stand.")
End If


Unload Me
genericcheckform.Show

End Sub


I appreciate that it was the bracketting with the if/then/else/endif that sorted the problem. Does the layout of the code make any difference, ie indenting lines or does that just make it easy to read?

David

[fumei]

I would avoid - even if it is legitimate - using the semi-colon in code.
Else: MsgBox ("Assuming these facts took place, but you have " & _
"not recorded them in your pocket note book, " & _
"then you will have to complete a statement " & _
"for the detection and fine to stand.")
First of all, your issue of wanting to do multiple separate instrutions can not be done if you use a semi-colon.

Second if makes reading code clearer if you consistently use:

If condition Then
' do THIS
Else
' do THAT
' and do another thing
' and maybe something else as well
End If


BTW: please note the use of the underscore character used to break up the very long messagebox string.

[fumei]

Oh, and I would make your checking of all the checkboxes a separate procedure.
Function AllClicked() As Boolean
If Theftform.PresentBox = True _
And Theftform.cautionsuspectbox = True _
And Theftform.FactBox = True _
And Theftform.Agreebox = True _
And Theftform.responsebox = True _
And Theftform.infooffenderbox = True Then
AllChecked = True
Else
AllChecked = False
End If
End Function
Now you can do your button click code like:
Private Sub TheftButton_Click()
If AllChecked Then
Call FillBM("statementbook", "Attach a copy of your PNB")
MsgBox ("Make sure you attach a copy of your PNB to the PND")
Else
MsgBox ("Assuming these facts took place, but " & _
"you have not recorded them in your pocket note book, " & _
"then you will have to complete a statement for the " & _
"detection and fine to stand.")
End If

Depending on what else you may be doing, I suspect an Exit Sub may be a good idea in there.

[davidboutche]

Thanks, that makes sense, except, I'm not sure what semi colon you are talking about? Are you refering to the full colon : after the else that I'd included? For some reason word keeps putting that in itself.

[fumei]

Sorry, me being stupid...yes, I meant colon, not semi-colon.

"For some reason word keeps putting that in itself."

No, that is not possible. First of all, Word does not write VBA code. VBA code is written in the VBE (Visual Basic Editor).

Second, unless you have some else very strange going on, NO code content is written by itself.

I - good thing! - actually checked after I wrote the above. I am tempted to remove it so I can maintain some mystique of actually knowing what I am doing, but what the heck...

Yes, you are quite correct. I did not know this, as I never use the colon. I always write out logic statements in full structure. However, yes, if you write:
Else Msgbox "Blah blah"
No colon. VBA does indeed put in the colon. In retrospect, this is a good thing. Otherwise the above would cause a syntax error. So VBA nicely fixes it for you.

In my mind, all the MORE reason to never use it.

[davidboutche]

haha, you almost had me believe I was writing the semi colon myself ;)

I forget VBA is very separate from word.

Thanks for the help, got there in the end.