Activate 2nd Combobox after selection in 1st Combobox

[julhs]

Ive been playing around with this code from https://www.mrexcel.com/board/threads/a-vba-slap-on-the-head-please.1149690/

….. and attempting to reduce the number of mouse clicks required

I’m try to achieve this:-
So starting out with B7:C12 all Blank; make selection in “tempcombo2” dropdown; then for “tempcombo” to become active/displayed with the relevant list; make selection in “tempcombo”; then for d8 (for example) to become the active cell.
But also want to work it in that it defaults to d8 if there is already a selection in either “tempcombo2” or “tempcombo” and either is clicked BUT NO selection change is made (so only defaults to “tempcombo” if there has been a change)

All I have managed succesfully so for is using the code below; if there IS a change in either “tempcombo2” or “tempcombo” then d8 becomes the active cell

Private Sub tempcombo2_Click()
tempcombo2.Visible = False
ActiveSheet.[d8].Select
End Sub

Private Sub tempcombo_Click()
tempcombo2.Visible = False
ActiveSheet.[d8].Select
End Sub

This is the main code from:- https://www.mrexcel.com/board/members/bc4240.122797/

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet

'This sets the size and shape of the "Animal Type" Combo Box (#2 Combo Box) when it is visible, but makes it invisible until the cell where it will be attached is clicked.

On Error Resume Next
If tempCombo.Visible = True Then
With tempCombo
'.Top = 10 N/A
'.Left = 10 N/A
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With
End If

'This sets the size and shape of the "Animal" Combo Box (#1 Combo Box) when it is visible, but makes it invisible until the cell where it will be attached is clicked.

If tempcombo2.Visible = True Then
With tempcombo2
'.Top = 10 N/A
'.Left = 10 N/A
.Visible = False
.LinkedCell = ""
.Value = ""
End With
End If

'If there is an error nothing will happen (nothing works).

On Error GoTo errHandler
If Target.Count > 1 Then GoTo exitHandler

'The following segment of code insures that the correct combo box (tempCombo vs. tempCombo2) is used in the correct column (left column vs. right column). It also demands that the Combo Boxes only appear in cells with a designated color (in this case vb ColorIndex 19 (yellow gold). If the color is changed the code must be changed to match the color.

On Error GoTo errHandler
If (Target.Column = 2 Or Target.Column = 3) And (Target.Interior.ColorIndex = 19) Then
If Target.Column = 2 Then
Set cboTemp = ws.OLEObjects("TempCombo2")
ElseIf Target.Column = 3 Then
Set cboTemp = ws.OLEObjects("TempCombo")
Else
Exit Sub
End If

'This part of the code insures that the tempCombo Box (#2 Combo Box) has it's ListFill Range populated with the lists of words (names) associated with the tempCombo2 value, e.g. dog = lab, retriever, etc..

With cboTemp
.LinkedCell = Target.Address
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 18
.Height = Target.Height + 5

If Target.Column = 3 Then
.ListFillRange = Cells(Target.Row, Target.Column - 1)
End If
End With
cboTemp.Activate
End If

'This part of the code allows the actions above to continue if correct, or stop the code from working if incorrect.

exitHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
errHandler:
Resume exitHandler
End Sub

 

[julhs]

Bumped