If and ElseIf confusion !

[Pacman52]

Hi all, I'm in no way an expert on VBA but I do have a good understanding of the basics (mainly thanks to all the support and help on here!)

I am really getting confused and just going round in a loop trying to workout the flow of some textboxes on a Userform. The main purpose of my aim is to have a coded solution to whatever a user inputs into a textbox be it a text value, a numeric value or simply trying to exit a textbox with no value entered and something is required before the user can move on to the next textbox. I know how to format each textbox (currency or dates) and the actual userform is designed how I need it to be with the tab order also correctly numbered.

To me, logically the code seems to follow the flow and covers all possible inputs into txbTotalCost but I keep coming up against errors like 'Block if without if' so I put another 'end if' in and then it errors again but now saying 'end if without block if ' and if I sort these errors out it then seems to set the focus to textboxes that are disabled when the userform is activated??

I really sorry if this thread isn't explaining things very well it may show how confused my brain is right now trying to sort out something I assumed was going to be simply to code lol. I think its because I'm not really understanding what Elseif does and when to use it and if its used do I need an End if ect

Just for a quick summary txbTotalCost can not be left empty it must have a numeric value entered before the user can move on to the next relevant textbox. The value can be either a '0' or a value of '1' or more.

So basically in txbTotalCost (the first active textbox) the user has to enter a numeric value if the enter a text value then I have the following code in the change event

This checks to make sure a numeric value has been entered and if it hasn't prompts the user with a Msgbox and sets the focus back to the textbox.

Private Sub txbTotalCost_Change()

    If Not IsNumeric (txbTotalCost)  Then

        MsgBox "It looks like you've entered a text value. Please only enter a numeric value or a 0 (zero). ", vbExclamation, "Invalid Entry"
        Cancel = True
        txbTotalCost.Value = ""
        txbTotalCost.SetFocus
        txbTotalCost.BackColor = RGB(204, 255, 255)

    End If

End Sub

And in the Exit event I have

Private Sub txbTotalCost_Exit(ByVal Cancel As MSForms.ReturnBoolean)

 If txbTotalCost.Value = "" Then

        MsgBox "It looks like you've left this box empty. Please only enter a numeric value or a 0 (zero). ", vbExclamation, "Invalid Entry"
        Cancel = True
        txbTotalCost.Value = ""
        txbTotalCost.SetFocus
        txbTotalCost.BackColor = RGB(204, 255, 255)

  ElseIf txbTotalCost.Value = "0" Then

         txbTotalCost.Value = Format(Textbox1.Value, "£0.00")
         txbTotalCost.BackColor = RGB(255, 255, 255)
         txbTotalCostDeposit.Value = "0"
         txbTotalCostDeposit.Value = Format(txbTotalCostDeposit.Value, "£0.00")
         txbTotalCostDepositDate.Enabled = False ' This is actually disabled when the userform is activated so do I need this line?
         txbTotalCostBalance.Value = txbTotalCost.Value
         txbTotalCostBalance.Value = Format(txbTotalCostBalance.Value, "£0.00")
         txbTotalCostBalanceDatePaid.Enabled = False ' Also disabled when the userform is activated so do I need this line?
         txbFestivalNotes.SetFocus

  Else

  If txbTotalCost.Value > "1" Then
         
        txbTotalCost.Value = Format(Textbox1.Value, "£0.00")
        txbPitchDeposit.Enabled = True ' Disabled when the userform is activated
        txbPitchDeposit.SetFocus

End If
End If

End Sub

 

[Micron]

First, do yourself a favour and properly indent your code because that is the main reason for errors you describe (as well as "Object variable or With block variable not set"). Blocks must be terminated so that everything they contain has a start and end. Nested blocks must do the same but within the outer blocks. Those errors are usually due to nested blocks being out of sync.

I have not examined all that code because of the indentation but will probably do so. In the meantime I'll suggest another method for validation. If you only have 1 or 2 controls that require it, then that approach is simple and usually good enough. Otherwise being prompted for every validation that does not pass, one by one, is annoying. Beyond that I suggest looping over all form controls and look for those whose tag property you have set (e.g. put req'd in the property sheet for those controls). If a control with .Tag = "req'd" And (validation here). In Access, the caption of a label that is attached to a control is easily read, thus making it intuitive to the user, whereas txbSomething maybe not so much. In Excel you'd need a workaround for this problem (why Excel developers? Could you not have made some things the same??). The time to do this looping is when the user attempts to save the form values. I'll end this part there since you may not go down that road.

Does this help?

Using If...Then...Else statements (VBA)

Office VBA reference topic

learn.microsoft.com

 

[Pacman52]

Thanks so much for the reply I'll read through the link in a minute but just reference on the indenting of the code I actually typed the post in manually rather than pasting the code from the workbook as to be honest I'd made so many changes and commented out so much, it was just easier but it was indented correctly initially before and I tried entering the various values I was testing.

I'll post back once I've read through the post in the link.

Thanks again Paul

 

[Micron]

Actually, after a better look it wasn't all that bad. The last 2 End If's threw me off. After playing with your code I see no reason for those errors and can't tell what the current issue is with that code.

FWIW, Select Case is a way to avoid nested If's and Else If's. One has to remember that Select Case terminates as soon as any test returns True so it may not be suitable in some cases. Also, one reference can be dealt with (e.g. txbTotalCost) and do validation tests on the value but another way is to look for True or False evaluations. That can make the Select Case very versatile. Here's a way using the latter type of validation:

Select Case True
    Case Not IsNumeric(txbTotalCost) 'should be true for any non-numeric value
        MsgBox "Please enter only a numeric value for Total Cost"
        Cancel = True
        txbTotalCost.Value = ""
        txbTotalCost.SetFocus
        txbTotalCost.BackColor = RGB(204, 255, 255)
    Case txbTotalCost = "0"
        txbTotalCost.Value = Format(Textbox1.Value, "£0.00")
        txbTotalCost.BackColor = RGB(255, 255, 255)
        txbTotalCostDeposit.Value = "0"
        txbTotalCostDeposit.Value = Format(txbTotalCostDeposit.Value, "£0.00")
        txbTotalCostDepositDate.Enabled = False ' This is actually disabled when the userform is activated so do I need this line?
        txbTotalCostBalance.Value = txbTotalCost.Value
        txbTotalCostBalance.Value = Format(txbTotalCostBalance.Value, "£0.00")
        txbTotalCostBalanceDatePaid.Enabled = False ' Also disabled when the userform is activated so do I need this line?
        txbFestivalNotes.SetFocus
    Case txbTotalCost.Value > "1" 'what if value is 1??
        txbTotalCost.Value = Format(Textbox1.Value, "£0.00")
        txbPitchDeposit.Enabled = True ' Disabled when the userform is activated
        txbPitchDeposit.SetFocus
End Select

FWIW, you seldom need to write .Value as it is the default in many cases; certainly for textbox/combobox.

EDIT - it's a mystery as to why much of that code went orange
Also, note my comment re: value being "1" (and why use text data for numbers??)
EDIT 2- It was a missing double quote.

 

[Pacman52]

Thanks again for the update - I'm just working through the link you originally posted on a new 'test' userform and so far so good.

I never thought about using the Select Case method but will give it a go if the If / Elseif causes problems as I go through it.

The .Value bit to be honest its just out of habit really and as far as I know doesn't cause any problems using it or does it?

 

[Micron]

No problems that I know of. I just consider it redundant.