InputBox question

Brent Blevins · #1 (**permalink**) 11-03-08, 14:34

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.

Code:

'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
            Range("A5:A6").Select
            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)
                        Cells(Application.InputBox).Font.Color
                End Select
            Loop
            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:

Code:

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.

MikeTheBike · #2 (**permalink**) 11-05-08, 09:03

Hi

I think this should do it for you.

Code:

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
    Else
        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 !!

MTB

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

Internet Services

Web Development

Digital Marketing

Consumer Tech