Boolean Function to constrain another function

**Blue_Wings** · 12-28-2012, 12:06 PM

Hi there,
If possible i would like some help in the following matter:

I have a function with a button that send´s values from an excel cell in one sheet to another sheet, and i would like to have another function that checks if another 2 cells are empty and then pops up a msgbox giving a warning about the empty cells.
If the 2 cells are empty the warning is given and the button will not send anything.

At the moment i have something like this: (but the boolean seems not to work, something is wrong, because even with values on the cells CD11 and CE12 on sheet3 the button dont send anything).

Private Sub Btn_Send_Arrival_Click()

    Call Check_ARRIVAL_Code

    MsgBox "before boolean is true"

    'If cell have values, button works and send values
     If ARRIVAL = True Then

        MsgBox "after boolean is true"

        'Send Comp.1 Bagagge
        Sheet1.Range("U34").Value = Sheet5.Range("T143").Value

    End If
    
End Sub

'Check when pessing button if the two ceels are empty
Public Sub Check_ARRIVAL_Code()

    Dim ARRIVAL As Boolean

    If Sheet3.Range("CD11").Value = "" And Sheet3.Range("CE12").Value = "" Then
        MsgBox "Please put values in the empty cells"
        ARRIVAL = False
    Else
        ARRIVAL = True
    End If

End Sub

Thanks.
Have a nice holidays everyone.

**sullivan372** · 12-28-2012, 12:22 PM

Might be a silly question but did you dim ARRIVAL anywhere? It looks like you are using it as a global, but you didn't include your declarations.

**Blue_Wings** · 12-28-2012, 12:52 PM

Yes i forgot to mention that, it was declared in anoter function, thanks for reminding me, now i have it on the code but it doesnt work anyway.
I Even writted a msgbox to test if it gets there but even the MsgBox "after boolean is true" dont appear, only the MsgBox "before boolean is true" pops up.

It means that the condicion "If ARRIVAL = True Then..." is not true... but it should be if the two cells CD11 and CE12 have values.

I tried to put even a code more simple like this, but it doesnt work (forcing the boolean ARRIVAL to be true, just to check if accepts the if condition from the other function):

Private Sub Btn_Send_Arrival_Click()

    Call Check_ARRIVAL_Code

    MsgBox "before boolean is true"

    'If cell have values, button works and send values
     If ARRIVAL = True Then

        MsgBox "after boolean is true"

        'Send Comp.1 Bagagge
        Sheet1.Range("U34").Value = Sheet5.Range("T143").Value

    End If
    
End Sub

'Check when pessing button if the two ceels are empty
Public Sub Check_ARRIVAL_Code()

    Dim ARRIVAL As Boolean

   ' If Sheet3.Range("CD11").Value = "" And Sheet3.Range("CE12").Value = "" Then
   '     MsgBox "Please put values in the empty cells"
   '     ARRIVAL = False
   ' Else
   '     ARRIVAL = True
   ' End If

ARRIVAL = True

End Sub

**sullivan372** · 12-28-2012, 02:30 PM

Also note that I changed your first sub to public, because I don't think you can call a private macro from a button. The second sub is a function to allow for a return value.

**sullivan372** · 12-28-2012, 02:25 PM

So from what I can see of your code, the issue is what I was hinting at before
Unless you have this line:

Public ARRIVAL As Boolean

then your boolean is only local to the specific function. So it is actually two different instances of a boolean variable called ARRIVAL, one in each of your subs.
If my understanding is correct, then there are a couple ways to fix it. You can put in the above line of code, or similarly:

Global ARRIVAL As Boolean

-Either of these two lines would work, but they need to be placed outside your sub
With either of these lines, your Check_ARRIVAL_Code function will set the ARRIVAL to true or false, and then the btn sub would check the value of that variable. If you want to implement this, make sure you get rid of the dim ARRIVAL as Boolean line because this will create a local instance and ignore the global.

The alternate fix, and maybe the better one depending on the rest of your code, is to have your function return the true or false value, rather than setting a variable. Your code would then look like this:

Public Sub Btn_Send_Arrival_Click()

    MsgBox "before boolean is true"

    'If cell have values, button works and send values
     If Check_ARRIVAL_Code = True Then

        MsgBox "after boolean is true"

        'Send Comp.1 Bagagge
        Sheet1.Range("U34").Value = Sheet5.Range("T143").Value

    End If
    
End Sub

'Check when pessing button if the two ceels are empty
Public Function Check_ARRIVAL_Code() As Boolean

    If Sheet3.Range("CD11").Value = "" And Sheet3.Range("CE12").Value = "" Then
        MsgBox "Please put values in the empty cells"
        Check_ARRIVAL_Code = False
    Else
        Check_ARRIVAL_Code = True
    End If


End Function

Let me know if this helps.

**Blue_Wings** · 12-28-2012, 05:37 PM

Thanks, it helped, but i had the vba code (Global ARRIVAL As Boolean) in the sheet, and to work needs to be in the Module, i didnt know that.

Thanks once again.