How to make msgbox() works for Excel?

[6656]

Hi,
I try to make an Excel worksheet with following function envent:
After cell A1 is entered data (nonblank), B1 should be entered something following. if skip B1 (blank), there is message box will pop-up to give warning.

Does Anyone can help me to fix my VBA code? or Another better way? Thanks in advance.

/******* VBA code for Excel **********/
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Column = 2 Then
If Target.Offset(0, -1).Value <> &quot;&quot; And IsEmpty(Target.Offset(0, 0).Value) Then
Application.EnableEvents = False
msgbox(&quot;You must enter a date in B1&quot;, vbOKOnly)
Application.EnableEvents = True
End If
End If
End Sub

 

[RobBroekhuis]

Leave the parentheses out:

msgbox &quot;You must enter a date in B1&quot;, vbOKOnly

Functions/subs with more than one parameter can only have the syntax with parentheses if they are used as a function
e.g., answer = msgbox(...)

Rob

 

[rmikesmith]

6656,

I'm not sure your code expicitly checks cells A1 and B1. Here is a modified version that does:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

  On Error Resume Next
  If Target.Address <> &quot;$A$1&quot; And Target.Address <> &quot;$B$1&quot; Then
    If Not IsEmpty(Me.Range(&quot;A1&quot;)) And IsEmpty(Me.Range(&quot;B1&quot;)) Then
      MsgBox &quot;You must enter a date in Cell B1.&quot;, vbOkOnly
      Application.EnableEvents = False
      Me.Range(&quot;B1&quot;).Select
      Application.EnableEvents = True
    End If
  End If
End Sub

This will display the msgbox if A1 is not empty and B1 is empty and the user selects a cell other than A1 or B1.


HTH
Mike

 

[Zathras]

Try this. Assumes that ONLY B1 is the cell that needs checking. However, your use of Offset leads me to suspect that you MAY want to edit for dates in every row where there is an entry in column &quot;A&quot; -- if that is the case, you can extend the logic (or leave another post and I can revise for you.)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

' Cell B1 must be a date (or blank)

On Error GoTo UserError
  If Target.Address = "$B$1" Then
    If Target.Value2 <> &quot;&quot; Then
      If Not IsDate(Range(&quot;B1&quot;).Value) Then
          MsgBox Range(&quot;B1&quot;).Value & &quot; is not a date!  You must enter a date in B1&quot;, vbOKOnly
      End If
    End If
  End If
Exit Sub
UserError:
  MsgBox &quot;Entry is not a date!  You must enter a date in B1&quot;, vbOKOnly
Resume Next
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

' If A1 is non-blank then verify that
' B1 has a date or else has the focus

If Target.Address <> &quot;$B$1&quot; Then
  If Target.Address <> &quot;$A$1&quot; Then
    If Range(&quot;A1&quot;) <> &quot;&quot; Then
      If Range(&quot;B1&quot;) = &quot;&quot; Then
        Range(&quot;B1&quot;).Select
        MsgBox &quot;You must enter a date in B1&quot;, vbOKOnly
      End If
    End If
  End If
End If
End Sub

 

[6656]

Thanks Rob & Mike,
How to modify or combine your codes of both behavious if I realy want the rest rows of Column A and B (e.g.: A2 ~ B2, A3 ~ B3.....)to have same behaviou from Mike's code?

 

[6656]

Thanks Rathras,
Yes, I want every row do the same behavious in Col &quot;A&quot; and &quot;B&quot;.

 

[Zathras]

I took out the date validation routine, but you could put it back if you want to. The following routine will check all non-blank entries in column &quot;A&quot; anytime anything changes anywhere in the worksheet. You may encounter performance problems if your worksheet has thousands of rows. But since we are talking about manual data entry, I didn't think that you would have that many rows to deal with.

There are various techniques one can use to determine the last row that needs to be processed, (Go to the bottom and do an &quot;End-Up&quot; is one. Geting the row count from .CurrentRegion is another.) but I find it just as easy to count contiguous blank cells and quit after the count exceeds a reasonable amount.

====================================================

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

' If Column A hass non-blank then verify that
' Column B is non-blank or else has the focus

Dim c As Range
Dim nEmptyCount As Integer
nEmptyCount = 0
Application.EnableEvents = False
For Each c In Range("A:A")
  If c.Text = "" Then
    nEmptyCount = nEmptyCount + 1
  Else
    nEmptyCount = 0
    If c.Text <> &quot;&quot; Then
      If c.Offset(0, 1).Text = &quot;&quot; Then
        c.Offset(0, 1).Select
        MsgBox &quot;You must enter a date in column B&quot;, vbOKOnly
        Exit For
      End If
    End If
  End If
  If nEmptyCount > 5 Then Exit For
Next c
Application.EnableEvents = True
End Sub

 

[Zathras]

Sorry, I just noticed that the above routine will &quot;ding&quot; the user even though he/she is trying to follow the rules and advance to column B to enter a date.

Here is a correction:

======================================================

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

' If Column A hass non-blank then verify that
' Column B is non-blank or else has the focus

Dim c As Range
Dim nEmptyCount As Integer
nEmptyCount = 0
Application.EnableEvents = False
For Each c In Range("A:A")
  If c.Text = "" Then
    nEmptyCount = nEmptyCount + 1
  Else
    nEmptyCount = 0
    If c.Text <> &quot;&quot; Then
      If c.Offset(0, 1).Text = &quot;&quot; Then
        If c.Offset(0, 1).Address <> Target.Address Then
          c.Offset(0, 1).Select
          MsgBox &quot;You must enter a date in column B&quot;, vbOKOnly
          Exit For
        End If
      End If
    End If
  End If
  If nEmptyCount > 5 Then Exit For
Next c
Application.EnableEvents = True
End Sub

 

[rmikesmith]

6656,

Here is a modified version of my routine that extends the behavior to cover all of columns A & B:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Static LastSelectedCell As Range
Dim R As Long

  On Error Resume Next
  If LastSelectedCell Is Nothing Then
    Set LastSelectedCell = ActiveCell
    Exit Sub
  End If

  R = LastSelectedCell.Row
  If Target.Row <> R Or (Target.Column <> 1 And Target.Column <> 2) Then
    If Not IsEmpty(Me.Cells(R, 1)) And Not IsDate(Me.Cells(R, 2)) Then
      MsgBox &quot;You must enter a date in Cell &quot; & Me.Cells(R, 2).Address
      Application.EnableEvents = False
      Me.Cells(R, 2).Select
      Application.EnableEvents = True
    End If
  End If
  Set LastSelectedCell = ActiveCell
End Sub

As before, this procedure checks as single &quot;entry&quot; as the user moves the selection. If they try to select a cell in a different row or one on the same row not in column A or B, they will get the message.

Regards,
Mike

 

[mscallisto]

Zathras

In your first post there could be a slight problem.

1. If one first enters a non date in B1 then enters a non blank in A1 the date check is not performed.

2. If one enters a non blank in A1 followed by a non date in B1 the error shows up correctly, but if one then clears the entry in A1 the error persists.

3. After the error message you could set the focus to B1, ready to enter the date.

 

[Zathras]

mscallisto:

Thanks for the clarification. It's hard to test all of the combinations that can come up.

That's primarily why I removed the validation code from my last post. If 6656 wants to validate the input for a good date, that needs to be handled separately. (He probably should do range checking, for instance.)

 

[6656]

Thanks All for your help.
I tried the Mike's code and it works for me very well. I'll try other codes to learn more of your talent and ideas. Thanks all again.

Mark