Active X Control Combo Box to give result in cell. Then VBA VLookup to place notes image in cell but does not work how I need it to

[CrustyR1]

Hi All, hopeflully someone can help with this one.

I have a sheet (Entry Form) that has a Active X combo Box C35 ("1 Door Base" - which doesn't show ) which works well and gives result in linked cell E35. I have a VBA VLookup looking at sheet 2 (Images) to find the match in E35 and show a notes image in F35. This all seems to work however I have to select E35, click on formula bar which is text only and then hit enter. After this the notes image and text "image" shows. I would like to be able to have the notes image show without the click & enter in formula bar as column E in grey gets hidden.

For some reason there is a need to select E35 call / formula bar for it to work. Auto calc is on and double clicking in E35 does nothing.

Cabinet Order Form_V6_WIP_3.xlsm
	B	C	D	E	F
33	Line No	Cabinet Type		Cabinet Code
34
35	1			1 Door Base	Image
36	2			1 Door Base	Image
37	3			2 Door Base	Image
38	4			1 Door Base	Image
39	5
40	6
41	7
42	8
43	9
44	10
Entry Form

 

[CrustyR1]

Lookup code

Private Sub Worksheet_Change(ByVal target As Range)
Dim cell As Range, vFND As Range

On Error GoTo ErrorExit
For Each cell In target
If cell.Column = 5 Then 'only cells in column E
Application.EnableEvents = False
With Sheets("Images")
On Error Resume Next
Set vFND = .Range("A:A").Find(cell, LookIn:=xlValues, LookAt:=xlWhole)
On Error GoTo ErrorExit
If Not vFND Is Nothing Then
vFND.Offset(, 1).Copy cell.Offset(, 1)
Else
cell.Offset(, 1) = ""
End If
End With

End If
Next cell

ErrorExit:
Application.EnableEvents = True
End Sub

 

[steve the fish]

The linked cell wont cause the worksheet_change macro to trigger. You will have to use a combobox_change event in the sheet module.

 

[CrustyR1]

The linked cell wont cause the worksheet_change macro to trigger. You will have to use a combobox_change event in the sheet module.

Thanks Steve the fish. I’ve placed a combobox change event for combobox that’s in row 35 but does nothing..... I may be doing it wrong though. Are you able to offer further advice?
Cheers

 

[steve the fish]

I cant see what you have done.

 

[CrustyR1]

I cant see what you have done.

I’ll rephrase...... are you able to show me what needs to be done to make it operate so lookup works please?

 

[steve the fish]

Can you rephrase again. What is the lookup value? Is it the combobox value? If so how are you changing the combobox? Where is it to look for the combobox value? When it finds it what do you want to produce?

 

[steve the fish]

Ill take a stab at it? This maybe:

Private Sub ComboBox1_Change()

Dim cell As Range, vFND As Range

Set cell = Me.Range(ComboBox1.LinkedCell)

With Sheets("Images")
    Set vFND = .Range("A:A").Find(ComboBox1.Value, LookIn:=xlValues, LookAt:=xlWhole)
    If Not vFND Is Nothing Then
        vFND.Offset(, 1).Copy cell.Offset(, 1)
    Else
        cell.Offset(, 1) = ""
    End If
End With

End Sub

 

[CrustyR1]

Ill take a stab at it? This maybe:

Private Sub ComboBox1_Change()

Dim cell As Range, vFND As Range

Set cell = Me.Range(ComboBox1.LinkedCell)

With Sheets("Images")
    Set vFND = .Range("A:A").Find(ComboBox1.Value, LookIn:=xlValues, LookAt:=xlWhole)
    If Not vFND Is Nothing Then
        vFND.Offset(, 1).Copy cell.Offset(, 1)
    Else
        cell.Offset(, 1) = ""
    End If
End With

End Sub

Thanks I’ll try that. I’m not a code writer...... I googled to find how to insert an image in notes of what had been selected in the combobox. Basically if the user selected 1 door cupboard in B35 combobox then this image would show in F35 as a note. If a 2 door cupboard is selected in B35 combobox then the image changes to a 2 door cupboard & so on. Tried to show this in attached pic. It all worked OK except the note/image doesn’t populate unless I put mouse curser in formula bar & hit enter..... then it appeared.

 

[steve the fish]

Thats just because you have no trigger to fire the code into action. The combobox change will act as a trigger. Or your original code would trigger if you changed the cells yourself rather than asking the combobox to change them which is what you are doing by reinputting them.