searching for a combobox.value and filling in textboxes from results
 

Hi all!

what I am trying to do is after the combobox is updated, do a searc
for the combobox.value and fill in the remaining textboxes from th
search. what i have is this.

Private Sub combobox1_AfterUpdate()

Dim rng As Range

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set wb = Workbooks.Open("Path")
Set rng = wb.Worksheets(1).Columns(1).find(combobox1.Value)

If rng = True Then
UserForm1.textbox1.value = ActiveCell.Offset(0, 1)
UserForm1.textbox2.value = ActiveCell.Offset(0, 2)
UserForm1.textbox3.value = ActiveCell.Offset(0, 3)
UserForm1.textbox3.value = ActiveCell.Offset(0, 4)
End If

wb.Close True
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub


I'm thinking the problem has to do with the activecell.offset
I am not getting any error message, but the boxes are not bein
filled.

can anyone help

--
Message posted from http://www.ExcelForum.com

searching for a combobox.value and filling in textboxes from results
 

Hi GregJG
Try
If Not rng Is Nothing Then
UserForm1.textbox1.value = rng.Offset(0, 1)
UserForm1.textbox2.value = rng.Offset(0, 2)
UserForm1.textbox3.value = rng.Offset(0, 3)
UserForm1.textbox3.value = rng.Offset(0, 4)
....

HTH
Cordially
Pascal


"GregJG " a écrit dans le message de
...
Hi all!

what I am trying to do is after the combobox is updated, do a search
for the combobox.value and fill in the remaining textboxes from the
search. what i have is this.

Private Sub combobox1_AfterUpdate()

Dim rng As Range

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set wb = Workbooks.Open("Path")
Set rng = wb.Worksheets(1).Columns(1).find(combobox1.Value)

If rng = True Then
UserForm1.textbox1.value = ActiveCell.Offset(0, 1)
UserForm1.textbox2.value = ActiveCell.Offset(0, 2)
UserForm1.textbox3.value = ActiveCell.Offset(0, 3)
UserForm1.textbox3.value = ActiveCell.Offset(0, 4)
End If

wb.Close True
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub


I'm thinking the problem has to do with the activecell.offset
I am not getting any error message, but the boxes are not being
filled.

can anyone help?


---
Message posted from http://www.ExcelForum.com/

searching for a combobox.value and filling in textboxes from results
 

Thanks Papou !! It worked. Not exactly sure though.

I understand the change to rng.offset. But the

If Not rng Is Nothing Then

has me confused. not exactly sure what that is saying. Guess it will b
one to keep on my notes :)

Thanks again

--
Message posted from http://www.ExcelForum.com

searching for a combobox.value and filling in textboxes from results
 

GregJG
has me confused. not exactly sure what that is saying. Guess it will be
one to keep on my notes :)
The find method returns Nothing when no match value is found.

HTH
Cordially
Pascal


"GregJG " a écrit dans le message de
...
Thanks Papou !! It worked. Not exactly sure though.

I understand the change to rng.offset. But the

If Not rng Is Nothing Then

has me confused. not exactly sure what that is saying. Guess it will be
one to keep on my notes :)

Thanks again!


---
Message posted from http://www.ExcelForum.com/