ElseIf Issues

I'm trying to setup a macro that will check three designated cells for zero values. There are two subs; one to designate cells to be used and another to give give message boxes depending on cell values. If any zero values are found, a message box will come up stating that one of the cells has not been given a value other than zero ("bill has not been entered"). If all three cells have a value other than zero, a different message box will come up stating that all cells are filled in (all bills have been entered). The problem I'm having is that the message box for "no gas bill has been entered" comes up regardless of whether the cell has a real value or zero.
Here's what I've got so far:

Sub SetBills()

Dim ElectricBill As Integer
Dim GasBill As Integer
Dim WaterBill As Integer

Set ElectricBill = Sheets("LGE Info").Cell(E27).Value
Set GasBill = Sheets("LGE Info").Cell(E32).Value
Set WaterBill = Sheets("Water Bill").Cell(D16).Value


End Sub

Sub Ready()
'
' Ready Macro
' Tells admin if bills are ready to be printed.
'

'

If GasBill = 0 Then
MsgBox "No, the gas bill has not been entered yet."
ElseIf ElectricBill = 0 Then
MsgBox "No, the electric bill has not been entered yet."
ElseIf WaterBill = 0 Then
MsgBox "No, the water bill has not been entered yet."
Else
MsgBox "Yes, bills are ready to be printed."
End If

End Sub


I'm at a loss as to why I'm not getting the right message box. Help!

Hi,

I have identified the problem (variables being out of scope), but to give you what I feel would be the optimal solution, I have a few questions, which hopefully will allow me to help further:

1. Is there a reason for there being two subroutines rather than one? From what I tell of the workflow, it would make more sense to amalgamate them into one, but if there is a functional reason we can work around that.
2. Are these values for the gas, electricity and water to be used anywhere else in the VBA for this project?
3. Would it be beneficial for the user to have one message box that displays all of the pertinent information about what is and is not entered correctly, rather than up to three separate message boxes? I know I would quickly get annoyed by that as a user. This is a slight aside from the issue, but may improve the code.

Thanks,

---
Bernieburnham

1. The only reason there are two subroutines is that I wrote the second sub first, then went back and wrote the other.
2. Gas, electricity, and water will not be used anywhere else in this macro. They may be used in another macro, though.
3. The purpose of the message box is to get the user to stop and verify that the worksheet has been completely updated for the month. If you have a better idea on what the message box should display, I'd be happy to hear it.

Hopefully the following should work (I haven't been able to test it unfortunately):

Please Login or Register  to view this content.

The problem was that because the variables were declared inside the first subroutine, they were specific to that subroutine and thus cannot be referenced anywhere else. They are also destroyed as soon as the subroutine gets to End Sub. Variables can be declared at Module, Project or even Global level, but having everything in one subroutine negates that problem. For more, please read the fine work of Chip Pearson on the subject: http://www.cpearson.com/excel/scope.aspx.

Hope that helps,

---
Bernieburnham

I like what you did with the message box. Unfortunately, I'm getting a Compile Error: Object Required error when I test it.

Hi again capurs01,

Arkadi got you half way there in solving the compile error. Those three lines should read:

Please Login or Register  to view this content.

That should now compile and work as designed (I've now done a few simple tests and I think it does what it should).

Thanks to Arkadi too,

---
Bernieburnham

Bernie & Arkadi,

I've updated the macro, but now I'm getting caught up on a run-time error 1004: application-defined or object-defined error.

I appreciate your help so far.

It was occurring on this line

ElectricBill = ThisWorkbook.Worksheets("LGE Info").Range(E27).Value

But then I realized I had left out the quotation marks around E27 in my range. However, correcting this issue give me a runtime error 6: overflow.

Double was the key. I've attached it to a command button and it works just as I had hoped it would. Thank you bernieburnham, Arkadi, and xlnitwit.