VBA Help

[Martin_Man]

Hi there,

I've created a search userform on Excel VBA and I'm stuck on the If Else statement.

I have coded it so that I can search for the Data, however I can't get my head around so that if there is missing information, it will display a message on the field saying something like (Information Missing). Can anyone help? I've googled but I can't seem to get it right.

Private Sub cmdSearch_Click()

row_number = 0
Do
DoEvents

row_number = row_number + 1
item_in_review = Sheets("Search").Range("A" & row_number)
    
If item_in_review = cboBranchNumber.Text Then
    
    txtStore.Text = Sheets("Search").Range("B" & row_number)
    txtFormat.Text = Sheets("Search").Range("C" & row_number)
    txtManager.Text = Sheets("Search").Range("D" & row_number)
    txtNumber.Text = Sheets("Search").Range("E" & row_number)
    txtGroup.Text = Sheets("Search").Range("F" & row_number)
    txtSd.Text = Sheets("Search").Range("G" & row_number)
    txtOd.Text = Sheets("Search").Range("H" & row_number)
    txtGroupFm.Text = Sheets("Search").Range("I" & row_number)
    
End If


Loop Until item_in_review = ""


End Sub

 

[monkehhh]

Sub cmdSearch_Click()
row_number = 1
item_in_review = Sheets("Search").Range("A" & row_number)
Do While Not item_in_review = ""
    DoEvents
    If item_in_review = cboBranchNumber.Text Then
        check_value_and_update Sheets("Search").Range("B" & row_number), txtStore
    End If
    row_number = row_number + 1
    item_in_review = Sheets("Search").Range("A" & row_number)
Loop
End Sub

Private Sub check_value_and_update(new_value As String, text_box As MSForms.TextBox)
If new_value = "" Then
    text_box.value = "Information missing"
Else
    text_box.value = new_value
End If
End Sub

.. you'd need to add 7 more calls to check_value_and_update for the other TextBoxs - assuming you're using TextBoxs - as I left them out for clarity.

 

[Martin_Man]

Thanks for the response! I'll give this a try first thing in the morning and I'll let you know how I get on.

I'm trying now to learn what you have included so I know for future reference.

I'm new to VBA so I really appreciate the time helping me out.

PS I have used textbox!

 

[monkehhh]

No problem, I can see how you've done stuff so I didn't want to completely change everything without seeing the source sheet - and it looks fairly sane! I changed where the loop checks item_in_review, just because that's how I'm used to doing it. Then I made a sub to check and set your data, so rather than repeating the same if..else statement 8 times you just have 8 calls to the sub. Hope it makes sense (and works!).

 

[Martin_Man]

Just wanted to say the code works great. Thanks for the help!!!

I'll look into this some more

 

[Martin_Man]

I have one final question...

I'm hoping to do develop a tool for work and I know that you can open the program up from the desktop as appose to needing it run from excel. Would you know how to do this?

Many thanks.