2 simple questions...

[chouston]

Hi, hope you can help me on these...

1) Is it possible change the settings on the message box and input box so that the user can do other stuff when they're on screen? (i.e. mouse pointer is not an hourglass)

2) Do I need to do something special so that the Cancel button on the input box works?

Thanks in advance for any help.

[NateO]

1) You can manipulate the Status Bar:

Application.StatusBar = "Processing..."

to return to normal:

Application.StatusBar = False

2) The cancel button on the input box should work unless you dim the input as an object variable. In which case you need an errorhandler preceeding your inputbox statement, i.e., on error resume next.
_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-03 09:15 ]

[chouston]

1) I tried application.statusbar = false and I still can't select the spreadsheet behind the inputbox/msgbox and scroll through it.

2) I don't think I dimmed the variables as objects but here's the code:

OutDate = (DateValue(InputBox("Move out date?", "Final Billing Form")))
'If OutDate = "" Then Exit Sub
If vbCancel Then
Exit Sub
Else
End If

the 'vbcancel' part was just a guess, but it's not working.

btw thanks for your help

[jlarson]

I'm not sure about message box and input box. With userforms, use vbModeless to allow you to work "behind" the userform.

Example:

userform1.Show vbModeless

[waderw]

try this:

If OutDate = vbnullstring or vbNo Then
Exit Sub
else
'do something here
end if

[waderw]

that will not work try:

If OutDate = vbnullstring or OutDate = vbNo Then
Exit Sub
else
'do something here
end if

[NateO]

Use the statusbar at the beginning and end of your code, like:

Sub test()
Application.StatusBar = "Processing...."
OutDate = Application.InputBox(prompt:="Move out date?", _
Title:="Final Billing Form", Type:=1)
Application.StatusBar = False
End Sub

If there is any piece of your macro that states dim outdate, you'll need an errorhandler, e.g.,

Sub test()
Application.StatusBar = "Processing...."
on error resume next
OutDate = Application.InputBox(prompt:="Move out date?", _
Title:="Final Billing Form", Type:=1)
Application.StatusBar = False
End Sub

Hope this helps.

[NateO]

You want to select a date from a cell?

Try this:

Sub tst()
Dim outdate As Range
Application.StatusBar = "Processing...."
On Error GoTo errorhandler
Set outdate = Application.InputBox(prompt:="Move out date?", _
Title:="Final Billing Form (Click on a Cell)", Type:=8)
'do stuff with 'outdate.Value' e.g., msgbox outdate.value
errorhandler:
Application.StatusBar = False
End Sub

Hope this helps.

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-03 10:09 ]

[chouston]

Hi, thanks for all your help so far but I'm still having trouble debugging this. Being able to access the screen behind the msgbox/inputbox is not that big of a deal so I'm dropping that, but I'm still having trouble with error-trapping. If the user inputs everything correctly, everything works fine, including the cancel buttons. However, if someone enters an invalid unit, the correct error message gets displayed "Invalid unit...", and the sub starts over, but this time when cancel is chosen, or vbno for "Do you want to print?", then, instead of exiting properly, it continues to cycle to the beginning and ignore cancel buttons.

I know this is alot to ask but if any of you have the time/patience/saintlyness to check out my code I would very much appreciate it. btw thanks to NateO, Jlarson, and waderw for your help so far!

Sub finalbill()

Dim unit
'Dim OutDate As Date


'Application.StatusBar = "Processing..."
Application.ScreenUpdating = False


'prompt for unit number
unit = (CVar(InputBox("Enter Unit #:", "Final Billing Form")))
If unit = "" Then Exit Sub



'find row for that unit number
With Sheets("Ridge Download").Range("B43:B422")
On Error Resume Next
resrow = .Find((unit), lookat:=xlWhole).Row
If resrow = Empty Then
MsgBox ("Please enter a valid unit number.")
finalbill
Else
End If
End With



'pulls values from sheet
res = ActiveWorkbook.Sheets("Ridge Download").Cells(resrow, 6)
begdate = ActiveWorkbook.Sheets("Ridge Download").Cells(resrow, 9)
wat = ActiveWorkbook.Sheets("Ridge Download").Cells(resrow, 12)
sew = ActiveWorkbook.Sheets("Ridge Download").Cells(resrow, 13)
tras = ActiveWorkbook.Sheets("Ridge Download").Cells(resrow, 14)
fee = ActiveWorkbook.Sheets("Ridge Download").Cells(resrow, 15)
monthdays = ActiveWorkbook.Sheets("Ridge Download").Cells(12, 11)
acctnum = ActiveWorkbook.Sheets("Ridge Download").Cells(resrow, 1)
thedate = Date
feet = ActiveWorkbook.Sheets("Ridge Download").Cells(resrow, 27)
unitype = ActiveWorkbook.Sheets("Ridge Download").Cells(resrow, 25)
rooms = ActiveWorkbook.Sheets("Ridge Download").Cells(resrow, 26)
watunit = ActiveWorkbook.Sheets("Ridge Download").Cells(resrow, 45)
sewunit = ActiveWorkbook.Sheets("Ridge Download").Cells(resrow, 46)
trashunit = ActiveWorkbook.Sheets("Ridge Download").Cells(resrow, 47)
feeunit = ActiveWorkbook.Sheets("Ridge Download").Cells(resrow, 48)
watrate = ActiveWorkbook.Sheets("Ridge Download").Range("H3")
sewrate = ActiveWorkbook.Sheets("Ridge Download").Range("H4")
trashrate = ActiveWorkbook.Sheets("Ridge Download").Range("H5")
feerate = ActiveWorkbook.Sheets("Ridge Download").Range("H6")

'if user enters a vacant unit
'If ActiveWorkbook.Sheets("Ridge Download").Cells(resrow, 3) = "" Then
' MsgBox ("Unit not occupied!")
' finalbill
'Else
'End If
If wat = "" Then
MsgBox ("Unit was not occupied")
finalbill
Else
End If




'prompt for move-out date
OutDate = (DateValue(InputBox("Move out date?", "Final Billing Form")))
If OutDate = vbNullString Or OutDate = vbNo Then
Exit Sub
Else
End If


'perform calcs
Days = OutDate - begdate
prorate = Days / monthdays

If wat <> "" Then water = wat * prorate
If sew <> "" Then sewer = sew * prorate
If tras <> "" Then trash = tras * prorate
If fee <> "" Then adminfee = fee

FinalAmt = water + sewer + trash + adminfee



'build message
Message = res
Message = Message & vbCrLf & "Unit #:" & vbTab & vbTab & unit
Message = Message & vbCrLf & "Move-Out Date" & vbTab & OutDate
Message = Message & vbCrLf & "Days Occupied" & vbTab & Days
Message = Message & vbCrLf & "Water" & vbTab & vbTab & Format(water, "$##.#0")
Message = Message & vbCrLf & "Sewer" & vbTab & vbTab & Format(sewer, "$##.#0")
Message = Message & vbCrLf & "Trash" & vbTab & vbTab & Format(trash, "$##.#0")
Message = Message & vbCrLf & "Admin Fee" & vbTab & Format(adminfee, "$##.#0")
Message = Message & vbCrLf & "Amt. Due" & vbTab & vbTab & Format(FinalAmt, "$##.#0")
Message = Message & vbCrLf & vbCrLf & "Print Final Bill?"



'displays and prompts for printing
Application.ScreenUpdating = True
ActiveWorkbook.Sheets("Ridge Download").Cells(resrow, 6).Select
ans = MsgBox(Message, vbYesNo, "Final Billing Form")
If ans = vbYes Then

Application.ScreenUpdating = False
ActiveWorkbook.Sheets("final bill").Select

'formats bill template
ActiveWorkbook.Sheets("final bill").Range("C13") = res
ActiveWorkbook.Sheets("final bill").Range("I14") = unit
ActiveWorkbook.Sheets("final bill").Range("Q28") = OutDate
ActiveWorkbook.Sheets("final bill").Range("M28") = acctnum
ActiveWorkbook.Sheets("final bill").Range("N28") = begdate
ActiveWorkbook.Sheets("final bill").Range("O28") = OutDate
ActiveWorkbook.Sheets("final bill").Range("b28") = unitype
ActiveWorkbook.Sheets("final bill").Range("g28").Value = rooms
ActiveWorkbook.Sheets("final bill").Range("k28").Value = feet
ActiveWorkbook.Sheets("final bill").Range("b37").Value = watunit * prorate
ActiveWorkbook.Sheets("final bill").Range("b38").Value = sewunit * prorate
ActiveWorkbook.Sheets("final bill").Range("b39").Value = trashunit * prorate
ActiveWorkbook.Sheets("final bill").Range("b40").Value = feeunit
ActiveWorkbook.Sheets("final bill").Range("i37").Value = watrate
ActiveWorkbook.Sheets("final bill").Range("i38").Value = sewrate
ActiveWorkbook.Sheets("final bill").Range("i39").Value = trashrate
ActiveWorkbook.Sheets("final bill").Range("i40").Value = feerate
ActiveWorkbook.Sheets("final bill").Range("k32").Value = Days

'print and return home
ActiveWindow.SelectedSheets.PrintOut Copies:=1
ActiveWorkbook.Sheets("Ridge Download").Select
Application.ScreenUpdating = True
ActiveWorkbook.Sheets("Ridge Download").Range("A42").Select


Else
End If

'Application.StatusBar = False

End Sub

p.s. how do you paste your code with the smaller font?

[NateO]

Golly gee whiz this is finicky, I broke down the error trapping into two procedures, you may need to play with this. The way you've defined your input box creates a situaiton where cancel or a bad input creates a null value, you can't distinguish between the two . So here's the code:

Sub finalbill()
On Error GoTo errorhandler1
outdate = (DateValue(InputBox("Move out date?", "Final Billing Form")))
MsgBox outdate
Exit Sub
'rest of your code
errorhandler1:
Call hndle
End Sub

Sub hndle()
Select Case MsgBox("Task aborted." & Chr(13) & Chr(13) & _
    "   If you meant to ""Cancel"" click ""Yes.""" & Chr(13) & Chr(13) & _
    "   If you inadvertantly botched the format click ""No.""" & Chr(13), vbYesNo)
Case vbYes
Case vbNo
Call finalbill
End Select
End Sub

Click on edit and notice the code and /code in brackets at the beg and end of my codes. 'Tis BB code. Hope this helps.

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-03 11:41 ]