Message box not showing

[jaoister]

Hi Guys,

Please help. I have this code for my search button in formcontrol. The search function is working everytime I enter an exact value but when I enter a value that is not in the list, its not prompting the message box that says "No record".

Private Sub CommandButton1_Click()

For Each c In Sheet11.Range("C:C").Cells
If c.Value <> "" Then
If TextBox9.Value = c.Value Then
TextBox1date.Value = c.Offset(0, -1).Value
TextBox2spr.Value = c.Offset(0, 5).Value
TextBox3cust.Value = c.Offset(0, 1).Value
TextBox4proj.Value = c.Offset(0, 2).Value
TextBox5cont.Value = c.Offset(0, 4).Value
TextBox6datesub.Value = c.Offset(0, 8).Value
TextBox7lostval.Value = c.Offset(0, 10).Value
TextBox8remark.Value = c.Offset(0, 11).Value
TextBox10.Value = c.Offset(0, 3).Value
TextBox11.Value = c.Offset(0, 6).Value
TextBox12.Value = c.Offset(0, 7).Value
TextBox13.Value = c.Offset(0, 9).Value
TextBox14.Value = c.Offset(0, 12).Value

ElseIf TextBox9.Value <> c.Value Then
MsgBox "No record"

Else
End If
Else
Exit Sub
End If
Next
End Sub

[Richard Buttrey]

Hi and welcome to the forum. Unfortunately your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[jaoister]

oops, Im sorry. Here you go:

Please Login or Register  to view this content.

[Richard Buttrey]

Hi,

I can't see anything in the syntax that wouldn't work as you expect. In passing I don't see the need for both

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

since these are mutually exclusive but I don't think that will cause the message box not to pop up.

Have you done the obvious debugging and stepped through the code to examine the value of TextBox9 and c?

Neither is it clear what you mean by 'enter a value that is not in the list'. What Value? alpha or numeric? What List?

You'd be better uploading the workbook and indicating what you enter when you click the button so that we can see the request in context.

[jindon]

Your code will exit the procedure if any cell has no value, so I guess you have blank cell before the cells with value.

[jaoister]

Hi Thank you for the prompt reply.

Here is my excel attachment

2.SPR register2.xls

[jindon]

Change to

Please Login or Register  to view this content.

[jaoister]

Thanks so much for the help jindon and Richard! it worked