Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

VBA msgBox question

  Asked By: Fahimah    Date: Aug 31    Category: MS Office    Views: 1244
  

I am using an Excel VBA form to display spreadsheet information.
User enters search criteria in text box. A loop is executed to
search for the input in the text box. If found, the record is
displayed in the appropriate text boxes on the form.

Question: If the user enters criteria not contained in the
spreadsheet, I would like to display a message box
indicating "Record not found". Where do I put the message box,
inside the loop or outside the loop? I have tried various
combinations with no success.

The user input is activated by a command button. Here is a sample of
the code.

----------
Private Sub cmdReview_Click()
Range(Selection, Selection.End(xlToRight)).Select
Do Until ActiveCell.Value = ""
Review = txtName.Text

If Review = ActiveCell.Value Then
txtName.Text = Review
txtDate.Text = ActiveCell.Offset(0, 1)
txtID.Text = ActiveCell.Offset(0, 2)
End If

ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlToRight)).Select
Loop

Range("A1").Select

End Sub
-------------

Share: 

 

2 Answers Found

 
Answer #1    Answered By: Alessio Smith     Answered On: Aug 31

I don't fully understand your code, but it seems that while you are
in your loop, the line "If Review = ActiveCell.Value Then" seems to
mean that the test was "passes" (ie, the record  was found  -- btw, you
could add an "Exit Do" statement here to speed things up).

If I'm right so far, then perhaps you could add a variable prior to
the loop, such as didFIND (dim as boolean). It will be
assigned "False" by default. In your test branch, if passes, add a
line like "didFIND = True".

Then, outside of your loop, you will add a new test... if didFIND is
still False, then activate your message  box.

 
Answer #2    Answered By: Sammy Anderson     Answered On: Aug 31

This is a common problem every programmer faced
Use a FLAG (boolean type user  variable) inside  the loop  if any values match in
specified search  criteria change the value with TRUE or else False
Again, check outside the loop with a IF condition like:

Private Sub cmdReview_Click()
Range(Selection, Selection.End(xlToRight)).Select
Do Until ActiveCell.Value = ""
Review = txtName.Text

If Review = ActiveCell.Value Then
txtName.Text = Review
txtDate.Text = ActiveCell.Offset(0, 1)
txtID.Text = ActiveCell.Offset(0, 2)
bSearchFlag = TRUE
End If

ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlToRight)).Select
Loop
Range("A1").Select

'---- My Code
IF bSearchFlag = TRUE then
MsgBox "Value Found"
Else
MsgBox "Value Not Found"
End If
'--- End My Code
End Sub

 
Didn't find what you were looking for? Find more on VBA msgBox question Or get search suggestion and latest updates.




Tagged: