Activate 2nd Combobox after selection in 1st Combobox

julhs

Board Regular
Joined
Dec 3, 2018
Messages
80
Office Version
  1. 2010
Platform
  1. Windows
….. 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

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

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

Capture.JPG

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

VBA Code:
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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Watch MrExcel Video

Forum statistics

Threads
1,130,119
Messages
5,640,218
Members
417,131
Latest member
Seanr19871

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top