Pop up showing the cell

[Nelson78]

Good morning.

I have to prevent manual errors in a data entry operation: more precisely, if the user types a space " " in a cell in the range F11:F13, a pop up has to specify in which cell, to make easier the consequent correction.

This is my attempt: the error is located in the highlighted row.

Sub space()

Dim cell As Range
For Each cell In Range("F11:F13")
If InStr(1, cell, " ", 1) Then [B]MsgBox ("In the cell" & Range("F11:F13").Value & " you have typed a space")[/B]
Next

End Sub

Thank's in advance.

 

[MickG]

Try this:-

Dim cell As Range
For Each cell In Range("F11:F13")
If InStr(1, cell, " ", 1) Then MsgBox ("In cell address " & cell.Address & " you have typed a space")
Next

 

[Nelson78]

Try this:-

Dim cell As Range
For Each cell In Range("F11:F13")
If InStr(1, cell, " ", 1) Then MsgBox ("In cell address " & cell.Address & " you have typed a space")
Next

Thank you. In line with your suggestion, I've tried to go forward checking a range (value 0: yes or not?).
If no, go on.
If yes, ask to user if go on or correction is needed.

Sub Test_zero()

Dim cell As Range, answer as boolean

For Each cell In Range("L11:L13")
    If cell.value = 0 Then
        answer=MsgBox (cell.address & " is Zero: Do you want to go on?", vbYesNo)
		if answer=vbNo then exit sub
        else
    End If
Next

End Sub

The problem is: answering no to the pop up, the test is not interrupted.

 

[MickG]

Perhaps something like this:-
NB:- Instead of going to "MoreCode:" you could call a sub, to run some more code.

[COLOR="Navy"]Sub[/COLOR] MG06Dec18
[COLOR="Navy"]Dim[/COLOR] cell [COLOR="Navy"]As[/COLOR] Range, fd [COLOR="Navy"]As[/COLOR] Boolean, answer [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] cell [COLOR="Navy"]In[/COLOR] Range("L11:L13")
    [COLOR="Navy"]If[/COLOR] cell.Value = "0" [COLOR="Navy"]Then[/COLOR] fd = True: [COLOR="Navy"]Exit[/COLOR] For
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]If[/COLOR] fd [COLOR="Navy"]Then[/COLOR]
        answer = MsgBox(cell.Address & " is Zero do you want to go on", vbCritical + vbYesNo + vbQuestion)
            [COLOR="Navy"]If[/COLOR] answer = vbYes [COLOR="Navy"]Then[/COLOR]
                GoTo Morecode
            [COLOR="Navy"]ElseIf[/COLOR] answer = vbNo [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]
            [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Else[/COLOR]
Morecode: MsgBox "Run more code here"
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Nelson78]

Perhaps something like this:-
NB:- Instead of going to "MoreCode:" you could call a sub, to run some more code.

[COLOR="Navy"]Sub[/COLOR] MG06Dec18
[COLOR="Navy"]Dim[/COLOR] cell [COLOR="Navy"]As[/COLOR] Range, fd [COLOR="Navy"]As[/COLOR] Boolean, answer [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] cell [COLOR="Navy"]In[/COLOR] Range("L11:L13")
    [COLOR="Navy"]If[/COLOR] cell.Value = "0" [COLOR="Navy"]Then[/COLOR] fd = True: [COLOR="Navy"]Exit[/COLOR] For
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]If[/COLOR] fd [COLOR="Navy"]Then[/COLOR]
        answer = MsgBox(cell.Address & " is Zero do you want to go on", vbCritical + vbYesNo + vbQuestion)
            [COLOR="Navy"]If[/COLOR] answer = vbYes [COLOR="Navy"]Then[/COLOR]
                GoTo Morecode
            [COLOR="Navy"]ElseIf[/COLOR] answer = vbNo [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]
            [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Else[/COLOR]
Morecode: MsgBox "Run more code here"
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

Thank's.

I'm trying to modify it, because if more then one value is zero, the pop up appears just once.

Nelson.

 

[MickG]

Try this:-
The code loops through each cell in specified range and runs to "More code" if no "0" is found, else
Msgbox appears for Instruction whether to proceed or not.

[COLOR="Navy"]Sub[/COLOR] MG07Dec40
[COLOR="Navy"]Dim[/COLOR] cell [COLOR="Navy"]As[/COLOR] Range, answer [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] cell [COLOR="Navy"]In[/COLOR] Range("L11:L13")
    [COLOR="Navy"]If[/COLOR] cell.Value = "0" [COLOR="Navy"]Then[/COLOR]
        answer = MsgBox(cell.Address & " is Zero do you want to go on", vbCritical + vbYesNo + vbQuestion)
            [COLOR="Navy"]If[/COLOR] answer = vbYes [COLOR="Navy"]Then[/COLOR]
                GoTo Morecode
            [COLOR="Navy"]ElseIf[/COLOR] answer = vbNo [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]
            [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Else[/COLOR]
Morecode: MsgBox "Run more code here"
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick