Results 1 to 5 of 5
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Quietly close Application.Inputbox (Excel 2000>)

    This macro calculates the absolute time of a range that you select. There is only one query I have! If I press the cancel button on the inputbox, I want the macro to quit quietly!!! But it debugs. How do I fix this?

    <pre>Sub CalcTotalTime()
    Dim CalcRange As Range

    Set CalcRange = Application.InputBox("Select the range of times to add!", "Add Range", , , , , , 8)
    If Not CalcRange Is Nothing Then
    CalcRange.Offset(CalcRange.Cells.Count, 0).Rows(1).Formula = _
    "=SUM(" & CalcRange.Columns(1).Address(False, False) & ")"
    CalcRange.Offset(CalcRange.Cells.Count, 0).Rows(1).NumberFormat = "[h]:mm"
    Else
    Exit Sub
    End If
    End Sub</pre>

    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Quietly close Application.Inputbox (Excel 2000>)

    You can use On Error Resume Next:

    Sub CalcTotalTime()
    Dim CalcRange As Range
    On Error Resume Next
    Set CalcRange = Application.InputBox("Select the range of times to add!", "Add Range", , , , , , 8)
    On Error GoTo 0
    If Not CalcRange Is Nothing Then
    CalcRange.Offset(CalcRange.Cells.Count, 0).Rows(1).Formula = _
    "=SUM(" & CalcRange.Columns(1).Address(False, False) & ")"
    CalcRange.Offset(CalcRange.Cells.Count, 0).Rows(1).NumberFormat = "<!t>[h]<!/t>:mm"
    Else
    Exit Sub
    End If
    End Sub

    Remarks:
    - Unless you have code after End If in the 'real' version of your macro, the Else / Exit Sub part isn't necessary.
    - If you have an error handler in your macro, you can replace On Error GoTo 0 with On Error GoTo <yourlabel> where <yourlabel> is the error handling label.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Quietly close Application.Inputbox (Excel 2000>)

    Thanx Hans.

    I can see this will work. One remark; I have been taught to rarely use On Error Resume Next, and if it must be used, to use it in a seperate sub with only the line of cade that could cause the problem. I am satisfied with your reply, but would like to ask if this is the only way to do this. Usually with myVar = Inputbox... you can use a conditional Like If myVar <> "" then... Can this not be done here?? It is the reason for me adding "If Not CalcRange Is Nothing Then" in the first place??
    Tx
    Regards,
    Rudi

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Quietly close Application.Inputbox (Excel 2000>)

    The standard VBA InputBox function will always return a string. The Excel Application.InputBox method returns a data type that depends on the Type argument, but this can lead to problems if the user cancels the dialog. You can work around this by using either On Error Resume Next or On Error GoTo <label>. In the code I posted, On Error Resume Next is used ONLY for the line with Application.InputBox, normal error handling is restored immediately afterwards.

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Quietly close Application.Inputbox (Excel 2000>)

    Thankyou. I can see what you mean. The type argument is a little volatile here.
    OK!
    Regards,
    Rudi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •