Event VBA if then Statement 1: Statement 2: Statement 3: endsub

Hi

I have several keypress events that I wish to process the same way.  The following code works fine.  I have quite a few similiar statements for the keypress event.
For this reason,  I would like the following code to be on one line. When I combine the the two lines, I get a compile error at runtime.

Private Sub fltrColour_KeyPress(KeyAscii As Integer): If KeyAscii = 32 Then fltrColour = "":   SetFilters:  fltrColour.SetFocus
End Sub

Open in new window

What am I missing here?
peispudAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
mbizupConnect With a Mentor Commented:
You closed this one too soon.

When I tested this myself, I was getting no error because I had mistakenly left the Key Preview property set to no, so the code simply wasn't running.

Anyhow, if you are looking to get a block conditional statement into one line, use Select Case instead of If-Then-Else

Private Sub fltrColour_KeyPress(KeyAscii As Integer): Select Case KeyAscii: Case 32: fltrColour = "":   SetFilters:   fltrColour.SetFocus:  End Select: End Sub

Open in new window


We had a question posted some time ago by one of our top Experts as a challenge in unusual programming, which wound up focusing on one-line programming, and some of the code is really amazing:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_23579815.html

Use the Request Attention button if you want to reopen this - you might get some input from additional Experts if you give it a little more time.
0
 
Rey Obrero (Capricorn1)Commented:
it is better for readability to create separate lines for your conditions


Private Sub fltrColour_KeyPress(KeyAscii As Integer)
If KeyAscii = 32 Then fltrColour = "":   SetFilters:  fltrColour.SetFocus
If KeyAscii = 33 Then fltrColour = "":   SetFilters:  fltrColour.SetFocus

End Sub
0
 
mbizupCommented:
Did you remember to put a :  before the End Sub?  I can't test fully without your SetFilters procedure, but this general syntax works without error for me:

Private Sub fltrColour_KeyPress(KeyAscii As Integer): If KeyAscii = 32 Then fltrColour = "":   SetFilters:  fltrColour.SetFocus: End Sub

Open in new window


However I generally prefer multiple lines, for the same reasons Cap1 posted above.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
peispudAuthor Commented:
This is true, but I have quite a few keypress events that behave exactly the same.   It's simply a matter of preference.  Can this be put on one line?
0
 
Rey Obrero (Capricorn1)Commented:
try terminating each IF conditions with ":"  without the quotes

If KeyAscii = 32 Then fltrColour = "":   SetFilters:  fltrColour.SetFocus: If KeyAscii = 32 Then fltrColour = "":   SetFilters:  fltrColour.SetFocus:
0
 
mbizupCommented:
I think we cross-posted... try my comment at http:#a39639680.  Cap seems to be suggesting the same thing.  If that doesn't work, post the code you tried.
0
 
peispudAuthor Commented:
I tried these 3 tests.  In the first one,  I substituted the call to "Setfilter".
Case 1 & 2 fail.
Case 3 works



    Private Sub fltrColour_KeyPress(KeyAscii As Integer): If KeyAscii = 32 Then fltrColour = "":    fltrColour = "":    fltrColour.SetFocus: End Sub
    Private Sub fltrColour_KeyPress(KeyAscii As Integer): If KeyAscii = 32 Then fltrColour = "":  If KeyAscii = 32 Then SetFilters:     fltrColour.SetFocus: End Sub
    Private Sub fltrColour_KeyPress(KeyAscii As Integer): If KeyAscii = 32 Then fltrColour = "":   SetFilters:   fltrColour.SetFocus
    End Sub

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
so now you need to use the third format

 Private Sub fltrColour_KeyPress(KeyAscii As Integer): If KeyAscii = 32 Then fltrColour = "":   SetFilters:   fltrColour.SetFocus
    End Sub
0
 
peispudAuthor Commented:
If I have to use the 3rd format,  then so be it.  But that is two lines.

I would prefer one line.   But I can live with two lines of code.

I'll leave this question open just a bit longer..... just in case.
0
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
i don't think you can have it in one line...
0
 
peispudAuthor Commented:
Thanks everyone for your input.
0
 
peispudAuthor Commented:
So,  it can't be done in one line using the if / then statement.
But it can be don in one line using a select statement.

Both answers are correct.  I appreciate the insight.

Thanks all.
0
 
mbizupCommented:
Yeah - sorry you didn't get more of a response.

Definitely agree that this won't work with an If-Then-Else block, but there are a variety of other conditional statements that WILL work and get you exactly the same results.

<<
 I have quite a few keypress events that behave exactly the same.   It's simply a matter of preference.
>>
I'm personally not a fan of one-line programming like this,  but I can easily see your point of view in wanting such similar procedures to be stacked up neatly in your code modules.

Another suggestion - kind of a hybrid approach (untested).  The idea here is to put your commands for Case 32 (or any other keys) into their own functions, which you can also write on single lines -- and call those functions from your one-line subs.  The benefit of that approach might be that however many similar keypress events you may have, you will only have to change the actual commands for specific keys in one place (ie: fnSpaceBar in this code).

So you get a similar overall appearance, but less maintenance if the code for handling the keys needs to be updated.

Private Sub Textbox1_KeyPress(KeyAscii As Integer): Select Case KeyAscii: Case 32: fnSpaceBar("Textbox1"):  End Select: End Sub
Private Sub Textbox2_KeyPress(KeyAscii As Integer): Select Case KeyAscii: Case 32: fnSpaceBar("Textbox2"):  End Select: End Sub
Private Sub fltrColour_KeyPress(KeyAscii As Integer): Select Case KeyAscii: Case 32: fnSpaceBar("fltrColour"):  End Select: End Sub

Function fnSpaceBar(sCtlName as string): Me.controls(sCtlName)  = "":   SetFilters:   Me.controls(sCtlName).SetFocus: End Function 

Open in new window

0
 
peispudAuthor Commented:
I have been using the hybrid approach in my coding in the situation that you describe.  

I am well satisfied with the replies including yours.  Thanks.
0
All Courses

From novice to tech pro — start learning today.