InputBox pops up, but won't display text in worksheet

Posted by Linda on March 20, 2001 4:11 PM

I have a outstanding claims database in Excel that I am trying to update. Once I find the client's name, I want to be able to insert "paid" in the claims status column and the date the claim was paid in a date column.

I finally figured out how to get an InputBox to pop up and prompt me to input the date. I type in a date and click OK. However, nothing displays on my worksheet. I have the date column format set to Date format xx/xx/xx. What am I doing wrong??

Here is my macro:

ActiveCell.Offset(0, 8).Range("A1").Select
ActiveCell.FormulaR1C1 = "paid"
ActiveCell.Offset(0, 1).Select
Dim Message, Title, MyValue
Message = "Enter Date" ' Set prompt.
Title = "InputBox" ' Set title.
' Display message, and title.
MyValue = InputBox(Message, Title)

End Sub

Posted by Ivan Moala on March 20, 2001 5:08 PM

Activecell = MyValue OR
Activecell = InputBox(message,Title)

However you may need some sought of error check
or masking to get a Valid date in.

Ivan

Posted by Dave Hawley on March 21, 2001 3:44 AM

Hi Linda

I couldn't agree with Ivan more! Dates are in a league of their own within Excel. Try this modified code, should help.

Sub Modified()
Dim Message, Title, MyValue

On Error Resume Next
ActiveCell.Offset(0, 8) = "paid"

Message = "Enter Date" ' Set prompt.
Title = "InputBox" ' Set title.
' Display message, and title.
MyValue = InputBox(Message, Title)

If MyValue = "" Then Exit Sub

If Not IsDate(MyValue) Then
MsgBox "Invalid Date"
Run "Modified"
End If

ActiveCell.Offset(0, 9) = MyValue

End Sub

OzGrid Business Applications

Posted by Linda on March 21, 2001 10:10 AM

Re: InputBox help - THANKS!!

Thanks Ivan and Dave! I've been trying to figure out how to do this since switching from Lotus 2 years ago!!!!