VBA Excel VB Editor Test for empty fields MsgBox

[dprayner]

Hi good people.

I have about 10 Cells with data. When the user clicks on a Send button I want to test to see if the fields are blank or contain data. This is simple enough with Select Case statements, or If Then Else stsatements. My question is if a user leaves a field (or several) blank, I want a message box to pop up and alert the user that they need to fill in the follwoing fields. I see this on online web forms all the time.

Thank you, DAVE

 

[SkipVought]

Hi,

You could use a Textbox that you place on the sheet that is ALWAYS there with a Visible property of FALSE.

I'd Select the empty cell, and assign TRUE to the Visible property of the textbox and position the textbox relative to the cell and change the message is necessary.


Skip,

_{[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More!}

 

[CautionMP]

Assuming the ten cells and labels are A1:B10 with labels in Column A and Data in Column B:

Public Function ValidateBeforeSend() As Boolean
Dim wksToCheck As Worksheet
Dim lngRow As Long
Dim strMessage As String

Set wksToCheck = ActiveSheet

'Initialize before use
strMessage = "The following fields cannot be blank:"
ValidateBeforeSend = True

'Cycle through the 10 rows
For lngRow = 1 To 10
  'Cehck column B for data
  If wksToCheck.Cells(lngRow, 2) = "" Then
    'Set the function to False and update message
    ValidateBeforeSend = False
    'The data cell is blank so add the column a (label)
    'to the validation message
    strMessage = strMessage & vbCrLf & wksToCheck.Cells(lngRow, 1)
  End If
Next lngRow

If Not ValidateBeforeSend Then
  'At least one field was blank, display message
  MsgBox strMessage, vbOKOnly, "Validation Error"
End If
End Function

You could use this in your 'Send' button routine to only send if the [tt]ValidateBeforeSend()[/tt] returns [blue]True[/blue].

Hope this helps,
CMP

Funny thing about being unemployed, weekends don't mean quite so much, just means you get to hang out with your working friends. Primus