|Ive been playing around with this code from https://www.mrexcel.com/board/threads/a-vba-slap-on-the-head-please.1149690/|
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