use VBA to populate activex textbox with the value next to the cell searched for

[Mikeymike_W]

Hi there,

I have an activex combobox called ComboxC1. This is populated with a range of colours.
When a colour is chosen i want a textbox "TB1" to be populated with the result.

In sheet2 I have a series of different named ranges.
For TB1 the answer is contained in range "ColourFoundation".
The range is a table with two columns, in column one is the range of colours, column 2 has the results i want TB1 to be populated with depending on the colour. I hope this makes sense.

I know you can do this with a standard Vlookup formula but would much rather have a clean VBA code to do the job.

Thanks in advance for any help you can give,

Mike

 

[JLGWhiz]

Seee if thid will fly

Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Sheets(1) 'Edit sheet name
Set sh2 = Sheets(2) 'Edit sheet name
    For Each c In sh2.Range("ColourFoundation").Columns(1)
        If sh1.Range("C1").Interior.Color = c.Interior.Color Then
            sh1.TextBox("TB1").Value = c.Offset(, 1).Value
        End If
    Next
End Sub

 

[Mikeymike_W]

Seee if thid will fly

Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Sheets(1) 'Edit sheet name
Set sh2 = Sheets(2) 'Edit sheet name
    For Each c In sh2.Range("ColourFoundation").Columns(1)
        If sh1.Range("C1").Interior.Color = c.Interior.Color Then
            sh1.TextBox("TB1").Value = c.Offset(, 1).Value
        End If
    Next
End Sub

Hi JLGWhiz,

Thanks very much for your help.
I think i didn't explain properly though.

the range and Combox has the words written, so it is text.
I managed to make it work though with the code below.

Thanks again for your help, it is really appreciated!

Dim FindString As String
    Dim Rng As Range
    FindString = ComboxC1.Value
    If Trim(FindString) <> "" Then
        With Sheets("Sheet2").Range("ColourFoundation")
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                TBox1.Value = Rng.Offset(, 1).Value
            Else
                MsgBox "Nothing found"
            End If
        End With
    End If

 

[JLGWhiz]

Thanks for the feedback,
regards, JLG