Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2007

    Unanswered: InputBox question

    Hi all,

    I have a monthly budget report that I manage. This report shows the budget surpluses and overages for different budget categories. Sometimes certain categories have planned overages and for the purposes of this particular report, I need to have a way for users to exclude those amounts for the summary section of the report. So by using the following code I let the users select a cell or cells containing an amount to be excluded from the summary calculations.

    'Do you have any exclusions?
        resp = MsgBox("Would you like to exclude any overages from the summary report?", vbYesNo + vbQuestion, "Exception Selection")
        Select Case resp
            Case Is = vbNo
            'Color the Overages section
                With Selection.Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .ThemeColor = xlThemeColorDark2
                    .TintAndShade = -0.249977111117893
                    .PatternTintAndShade = 0
                End With
            Case Is = vbYes
                Cells(7, 1) = "Overage Exclusions"
                Cells(7, 1).HorizontalAlignment = xlLeft
                Cells(7, 1).Font.Bold = True
                curOverageExcl = Application.InputBox("Please select a cell to exclude.", Type:=8)
                Do Until resp = vbNo
                    resp = MsgBox("Do you have additional exclusions?", vbYesNo + vbQuestion, "Additional Exclusions")
                    Select Case resp
                        Case Is = vbYes
                            curOverageExcl = curOverageExcl + Application.InputBox("Please select a cell to exclude.", Type:=8)
                    End Select
                Cells(8, 1) = curOverageExcl * -1
                Cells(8, 1).NumberFormat = "#,##0.00"
                Cells(8, 1).Font.Color = -16776961
                Cells(8, 1).Font.Bold = True
                Cells(8, 1).HorizontalAlignment = xlRight
                Cells(9, 1) = "Corrected Overages"
                Cells(9, 1).HorizontalAlignment = xlLeft
                Cells(9, 1).Font.Bold = True
                Cells(10, 1) = Cells(6, 1) - Cells(8, 1)
                Cells(10, 1).NumberFormat = "#,##0.00"
                Cells(10, 1).Font.Color = -16776961
                Cells(10, 1).Font.Bold = True
                Cells(10, 1).HorizontalAlignment = xlRight
    So for the line:
    curOverageExcl = Application.InputBox("Please select a cell to exclude.", Type:=8)
    The user clicks a cell to select it and then clicks ok. and the value that is contained in that cell is excluded from the calculation of overages.

    OK, so now for the question. In addition to the current functionality, I need to change the text color of cells selected by the inputbox. This way, at a glance, anyone looking at the report can tell which items have been excluded. The problem is that the actual cell location is not captured, only the value that is in the cell.

    Any ideas would truly be appreciated.

  2. #2
    Join Date
    Apr 2004
    Derbyshire, UK
    Provided Answers: 1

    I think this should do it for you.
    Sub Test()
        Dim SelectedRange As Range
        On Error Resume Next
        Set SelectedRange = Application.InputBox("Please select a cell to exclude.", Type:=8)
        If Err.Number <> 0 Then
            On Error GoTo 0
            'do somthing
            On Error GoTo 0
            MsgBox SelectedRange.Address
            Dim cel As Range
            For Each cel In SelectedRange
                With cel
                    MsgBox .Address & " : " & .Value
                End With
            Next cel
        End If
    End Sub
    This also permits selection of a continuous range of cells or multiple individual selection of cells.

    I have never used this functianality of the InputBox before, very interesting !!


    Moded to include error hadling for the InputBox 'Cancel' case !!

Posting Permissions

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