Using VBA code when searching in a spreadsheet

A

anb001

I have a form in an excel spreadsheet, with two textboxes and one comb
box. I have below code, which will search for values
given in the combobox.

Private Sub CommandButton1_Click()

Dim foundcell As Object
myvalue = ComboBox1.Value
Set foundcell = ActiveSheet.Columns(1).Find(myvalue)
If foundcell Is Nothing Then
MsgBox ("Not found.")
Else
foundcell.Select
End If


End Sub

If e.g. the comobox1 is empty, it should look for a value in textbox1
and search for that in column(2) in the spreadsheet, and if that als
is empty, it should look for the value in textbox2, and search for tha
in column(3) in the spreadsheet.

Help is badly needed. Thnks
 
B

Bob Flanagan

if me.combobox1.text ="" then
myvalue = me.textbox1.text
else
myvalue = me.combobox1.text
end if

The above would have to be used in the userform's module. If you use the
userform name instead of "me",then you can use the above code in a regular
module.

Bob Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel
 
B

BrianB

Try this :-



Code
-------------------
Private Sub CommandButton1_Click()
Dim foundcell As Object
Dim Col As Integer
'-----------------------------
If ComboBox1.Value <> "" Then
myvalue = ComboBox1.Value
Col = 1
ElseIf ComboBox2.Value <> "" Then
myvalue = ComboBox2.Value
Col = 2
ElseIf ComboBox3.Value <> "" Then
myvalue = ComboBox3.Value
Col = 3
Else
MsgBox ("No entry made")
Exit Sub
End If
'--------------------------
Set foundcell = ActiveSheet.Columns(Col).Find(myvalue)
If foundcell Is Nothing Then
MsgBox ("Not found.")
Else
foundcell.Select
End If
End Sub
 
Top