VBA IF statements

[Mohammad Said]

Dears,
I have a userform to record fixed assets in which I have 1 SAVE command button, 3 frames with multiple option buttons and two relevant text boxes named (txt_price and txt_exchangerate). The default value of both text boxes is (0) upon initialization of userform. In the first frame (named Acquisition Method), I have four option buttons named; Purchase, Lease, Trade In, Donation. In the second frame (named Currency) I have four currency options: NIS, USD, EUR, JOD. In the third frame (named Asset Status) I have three options: New, Used, Damaged. All assets have to be re-evaluated in NIS currency (the main currency).
If I select Purchase Option , I want the txt_price value to be greater than (0) regardless of the currency option and the and txt_exchangerate value to be any value other than (0). However, I have two other alternatives: (1) if NIS currency is selected I will set the value of txt_exchangerate to 1.00. If other currency is selected I will set the exchange rate to be a positive value other than zero. My sub is the the following, but when I click the SAVE command button entry is transferred to worksheet even if txt_price is 0 or txt_exchangerate value is (0). I do appreciate your help. My code is:

If Me.OPT_PURCHASE.Value = True Then
    If Me.OPT_NIS.Value = True Then
        If Me.TXT_ASSETPRICE.Value = 0 Then
            Exit Sub
            With Me.TXT_ASSETPRICE
            .SetFocus
            .SelStart = 0
            .SelLength = Len(.Text)
            End With
       
         Else
       
            If Me.TXT_ASSETPRICE <> 0 Then
                Me.TXT_EXCHANGERATE.Value = 1
                Cells(ROWNUM, 15).Value = Me.TXT_EXCHANGERATE.Text
                Cells(ROWNUM, 16).Value = Me.TXT_ASSETPRICE.Value
                Cells(ROWNUM, 19).Value = Me.TXT_ASSETPRICE.Value * Me.TXT_EXCHANGERATE.Value
             End If
         End If
        
    Else
   
        If Me.OPT_EUR.Value = True Or Me.OPT_JOD.Value = True Or Me.OPT_USD.Value = True Then
       
            If Me.TXT_ASSETPRICE.Value = 0 Then
                Exit Sub
                With Me.TXT_ASSETPRICE
                .SetFocus
                .SelStart = 0
                .SelLength = Len(.Text)
                End With
               
            Else
                If Me.TXT_ASSETPRICE <> 0 Then
                        If Me.TXT_EXCHANGERATE.Value = 0 Then
                           
                           
                            MsgBox "PLEASE KEY IN EXCHANGE RATE"
                           
                            With Me.TXT_EXCHANGERATE
                            .SetFocus
                            .SelStart = 0
                            .SelLength = Len(.Text)
                            End With
                           Exit Sub
                          

                        Else
                       
                            If Me.TXT_EXCHANGERATE.Value <> 0 Then
                                Cells(ROWNUM, 15).Value = Me.TXT_EXCHANGERATE.Text
                                Cells(ROWNUM, 16).Value = Me.TXT_ASSETPRICE.Value
                                Cells(ROWNUM, 19).Value = Me.TXT_ASSETPRICE.Value * Me.TXT_EXCHANGERATE.Value
                            End If
                        End If
            End If
        End If
    End If
End If
End If

 

[JLGWhiz]

You need to move your 'Exit Sub' statement to after the 'With...End With' statement in two places. Where they are currently located will cause the procedure to terminate be befrore the With statements execute.

 

[Mohammad Said]

Thanks JLGWhiz. I will give a try and see.

 

[Mohammad Said]

Dear JLGWhiz. I tried it. no success. It saves the entry without the exchange rate. Then it notifies me to set the exchange rate. Once I key it in, it saves the entry one more time including the exchange rate. I want to stop it from savin the first time until I key in the exchange rate. I appreciate your help, though.

 

[JLGWhiz]

Have you tried stepping through the code with the F8 key to see where it is skipping over the part you need to enter the values? I can't see any other quirks from the posted code.

 

[Mohammad Said]

I did actually. I have two cases:

1- if the price is (0) It exits the sub here.

If Me.TXT_ASSETPRICE.Value = 0 Then

With Me.TXT_ASSETPRICE
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
Exit Sub (it exits here and save the record)


2- if the exchange rate is (0) It exits the sub here.
If Me.TXT_ASSETPRICE <> 0 Then
If Me.TXT_EXCHANGERATE.Value = 0 Then

MsgBox "PLEASE KEY IN EXCHANGE RATE"

With Me.TXT_EXCHANGERATE
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
Exit Sub (it exits here and saves the record)
thanks

 

[JLGWhiz]

The sequence in which the code executes often is the cause of undesired results and can be fixed by simply relocating the statements within the code.
Regards, JLG