PDA

View Full Version : Solved: MsgBox Item



chem101
12-29-2010, 01:02 PM
Hello,

I need your help. I would like to display a message to the user of a spreadsheet if they enter any item (text or number into a cell). The code I have so far is:

Public Sub MsgBoxAddlMat()
Dim Ans As Integer
Ans = MsgBox("You have entered", vbOKOnly)
If wksEstimateInput.Range("e68:e70") <> "" Then
MsgBox "You have entered too many items"
End If
End Sub

I'm getting a type mis-match error. Also, I would like the caption of the msgbox to read 'Additional Materials'.

Any help you can provide will be greatly appreciated.

Thank you very much!

Bob Phillips
12-29-2010, 02:09 PM
You are testing man cells, not just one. Should it just be one, or check each cell in that range?

chem101
12-29-2010, 02:21 PM
I would like the msgbox to appear if they attempt to enter a value in any of the cells in the range.

Bob Phillips
12-29-2010, 02:33 PM
I understand that from your last thread, but it wasn't what I asked.

chem101
12-29-2010, 09:43 PM
Then what are you asking?? IF they enter anything in the range I specified I would like a msgbox to appear with the message specified. What additional info do you need?

Bob Phillips
12-30-2010, 12:00 PM
need



Public Sub MsgBoxAddlMat()
Dim Ans As Integer
Dim rng As Range

Ans = MsgBox("You have entered", vbOKOnly)
Set rng = wksEstimateInput.Range("E68:E70")
If Application.CountBlank(rng) <> rng.Cells.Count Then

MsgBox "You have entered too many items"
End If
End Sub

Bob Phillips
12-30-2010, 12:01 PM
Perhaps this is what you need



Public Sub MsgBoxAddlMat()
Dim Ans As Integer
Dim rng As Range

Ans = MsgBox("You have entered", vbOKOnly)
Set rng = wksEstimateInput.Range("E68:E70")
If Application.CountBlank(rng) <> rng.Cells.Count Then

MsgBox "You have entered too many items"
End If
End Sub

chem101
12-30-2010, 12:15 PM
Thank you for your assistance, but unfortunately the MsgBox doesn't appear when I enter a number or text in any of the cells in the range mentioned. What do you suggest?

Bob Phillips
12-30-2010, 12:43 PM
I tried it by putting a 1 in E68 and I got the second MsgBox.

chem101
12-30-2010, 12:58 PM
Thank you, but I don't want two MsgBoxes. If anything is entered in the cell range - I reduced it to cell E68 just to simplify matters - I would like the MsgBox shown in the code below to appear. I would also, if possible, like the caption for the MsgBox to read 'Additional Materials'. Currently when I run the code in the VBE the MsgBox does appear (without the caption I would like). After compiling the code I tried entering a value in cell E68 on the worksheet but the MSGBox didn't appear. Can you see the problem?


Public Sub MsgBoxAddlMat()
Dim Ans As Integer
Dim rng As Range
'Ans = MsgBox("You have entered", vbOKOnly)
Set rng = wksEstimateInput.Range("E68")
'If Application.CountBlank(rng) <> rng.Cells.Count Then

If wksEstimateInput.Range("e68") <> "" Then

Ans = MsgBox("You have entered too many items", vbOKOnly)

End If

End Sub


Thank you.

Bob Phillips
12-30-2010, 01:02 PM
Works fine for me.

Do you understand what wksEstimateInput is?

chem101
12-30-2010, 01:10 PM
I think so...isn't it the worksheet named EstimateInput?

Bob Phillips
12-30-2010, 03:20 PM
Not the worksheet itself, the worksheet codename. If the worksheet is named EstimateInput, you have to use



Set rng = Worksheets("wksEstimateInput").Range("E68")