Pop up showing the cell

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Pop up showing the cell

  1. #1
    Board Regular
    Join Date
    Sep 2017
    Posts
    72
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Pop up showing the cell

     
    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.

    Code:
    Sub space()
    
    Dim cell As Range
    For Each cell In Range("F11:F13")
    If InStr(1, cell, " ", 1) Then MsgBox ("In the cell" & Range("F11:F13").Value & " you have typed a space")
    Next
    
    End Sub
    Thank's in advance.
    Last edited by Nelson78; Dec 6th, 2017 at 11:47 AM.

  2. #2
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    12,879
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Pop up showing the cell

    Try this:-
    Code:
    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

  3. #3
    Board Regular
    Join Date
    Sep 2017
    Posts
    72
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pop up showing the cell

    Quote Originally Posted by MickG View Post
    Try this:-
    Code:
    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.

    Code:
    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.
    Last edited by Nelson78; Dec 6th, 2017 at 01:16 PM.

  4. #4
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    12,879
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Pop up showing the cell

    Perhaps something like this:-
    NB:- Instead of going to "MoreCode:" you could call a sub, to run some more code.
    Code:
    Sub MG06Dec18
    Dim cell As Range, fd As Boolean, answer As String
    For Each cell In Range("L11:L13")
        If cell.Value = "0" Then fd = True: Exit For
    Next
    If fd Then
            answer = MsgBox(cell.Address & " is Zero do you want to go on", vbCritical + vbYesNo + vbQuestion)
                If answer = vbYes Then
                    GoTo Morecode
                ElseIf answer = vbNo Then
                    Exit Sub
                End If
    Else
    Morecode: MsgBox "Run more code here"
    End If
    End Sub
    Regards Mick
    Last edited by MickG; Dec 6th, 2017 at 03:06 PM.

  5. #5
    Board Regular
    Join Date
    Sep 2017
    Posts
    72
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pop up showing the cell

    Quote Originally Posted by MickG View Post
    Perhaps something like this:-
    NB:- Instead of going to "MoreCode:" you could call a sub, to run some more code.
    Code:
    Sub MG06Dec18
    Dim cell As Range, fd As Boolean, answer As String
    For Each cell In Range("L11:L13")
        If cell.Value = "0" Then fd = True: Exit For
    Next
    If fd Then
            answer = MsgBox(cell.Address & " is Zero do you want to go on", vbCritical + vbYesNo + vbQuestion)
                If answer = vbYes Then
                    GoTo Morecode
                ElseIf answer = vbNo Then
                    Exit Sub
                End If
    Else
    Morecode: MsgBox "Run more code here"
    End If
    End Sub
    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.

  6. #6
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    12,879
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Pop up showing the cell

      
    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.
    Code:
    Sub MG07Dec40
    Dim cell As Range, answer As String
    For Each cell In Range("L11:L13")
        If cell.Value = "0" Then
            answer = MsgBox(cell.Address & " is Zero do you want to go on", vbCritical + vbYesNo + vbQuestion)
                If answer = vbYes Then
                    GoTo Morecode
                ElseIf answer = vbNo Then
                    Exit Sub
                End If
        Else
    Morecode: MsgBox "Run more code here"
        End If
    Next
    End Sub
    Regards Mick

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com