Solved: MsgBox Item

[chem101]

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]

You are testing man cells, not just one. Should it just be one, or check each cell in that range?

[chem101]

I would like the msgbox to appear if they attempt to enter a value in any of the cells in the range.

[Bob Phillips]

I understand that from your last thread, but it wasn't what I asked.

[chem101]

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]

need

[vba]

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
[/vba]

[Bob Phillips]

Perhaps this is what you need

[vba]

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
[/vba]

[chem101]

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]

I tried it by putting a 1 in E68 and I got the second MsgBox.

[chem101]

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]

Works fine for me.

Do you understand what wksEstimateInput is?

[chem101]

I think so...isn't it the worksheet named EstimateInput?

[Bob Phillips]

Not the worksheet itself, the worksheet codename. If the worksheet is named EstimateInput, you have to use

[vba]

Set rng = Worksheets("wksEstimateInput").Range("E68")
[/vba]