Input values directly in message box?

[Guest]

Is there a way to have a message box come up that allows the user to input a
value directly into the message box, which stores that value in a seperate
cell. I have a drop down box, and when the user picks "Other", I want them to
be able to input their own value, except, I don't want it to remain in the
drop down box.

 

[Chip]

You have two options...one you could create a user form which would
have a pop up window where they could put in a number..or you could
what I did once, which was when they choose other a cell next to the
drop down window, changes color, gets the focus, and the number can be
put in there, here is vba code to do that.

Sub Freeform()


If Range("E53").Value = 0 Then

Range("I24").Select
Selection.Font.ColorIndex = 0

Range("C21").Select
Selection.Font.ColorIndex = 0
Selection.Interior.ColorIndex = 34
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
..LineStyle = xlContinuous
..Weight = xlMedium
..ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
..LineStyle = xlContinuous
..Weight = xlMedium
..ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
..LineStyle = xlContinuous
..Weight = xlMedium
..ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
..LineStyle = xlContinuous
..Weight = xlMedium
..ColorIndex = xlAutomatic
End With

Range("C21").Select

''''''''''''''''''

Else

Range("C21").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
With Selection.Font
..ColorIndex = 33
End With
With Selection.Interior
..ColorIndex = 33
End With

Range("I24").Select
With Selection.Font
..ColorIndex = 33
End With

Range("A21").Select

End If
End Sub

 

[Bob Phillips]

JJ,

Take a look at Inputbox in the VBA help.

--

HTH

RP
(remove nothere from the email address if mailing direct)