Combining auto suggest combo box and auto population using if statement

Murlin0920

New Member
Joined
Oct 30, 2015
Messages
2
I am trying to combine these 2 code lines to accomplish a drop down list with suggestive text. That also auto populates other cells when a menu is selected it populates menu items. The suggestive text works well but I'm a bit stumped on how to go about adding the auto population of the cells after the menu is selected from the drop down list. I have also tried doing this with select case but since I already have a worksheet selection code in use I get the ambiguous name error. I apologize in advance about the length but not sure where to cut out to give full explanation. The code I'm having trouble with is integrating the if statement at the end. I'm also missing how to input the BB code here. I'll fix it as soon as I can. Thanx in advance

Code:
==========================
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Dim Tgt As Range
Set Tgt = Target.Cells(1, 1)
Set ws = ActiveSheet
On Error GoTo errHandler

If Tgt.Validation.Type = 3 Then
Cancel = True
End If

Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
With cboTemp
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
On Error GoTo errHandler

If Tgt.Validation.Type = 3 Then
Application.EnableEvents = False
str = Tgt.Validation.Formula1
str = Right(str, Len(str) - 1)
With cboTemp
.Visible = True
.Left = Tgt.Left
.Top = Tgt.Top
.Width = Target.Width + 15
.Height = Target.Height + 5
.ListFillRange = ws.Range(str).Address
.LinkedCell = Tgt.Address
End With
cboTemp.Activate
'open the drop down list automatically
Me.TempCombo.DropDown
End If

errHandler:
Application.EnableEvents = True
Exit Sub

End Sub
'=========================================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet

Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
If cboTemp.Visible = True Then
With cboTemp
.Top = 10
.Left = 10
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With
End If

errHandler:
Application.EnableEvents = True
Exit Sub

End Sub
'=

Private Sub TempCombo_KeyDown(ByVal _
KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
Select Case KeyCode
Case 9 'Tab
ActiveCell.Offset(0, 1).Activate
Case 13 'Enter
ActiveCell.Offset(1, 0).Activate
Case Else
'do nothing
End Select
End Sub
'====================================
Private sub
Dim menu as String, result as string

menu = range ("A1").value

If menu = "menu1" then
Result = "menu item1"
If menu = "menu2" then
Result = " menu item1"
Else
Result = ""
End if

Range("A3").value = Result

End sub
Code:
 

Murlin0920

New Member
Joined
Oct 30, 2015
Messages
2
More specifically I keep getting a compile error: block if without end of
In vba
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,493
Office Version
365
Platform
Windows
More specifically I keep getting a compile error: block if without end of
Its telling you exactly what the issue is. In your last procedure, you have two IF ... THEN statements, but only one END IF statement.

Unless you are complete your entire IF ... THEN statement on one line (meaning there is something after the THEN on that same line), every IF ... THEN statement needs a corresponding END IF statement.

Also, your last procedure looks a little odd. It appears to have no name ("Private Sub").
 

Forum statistics

Threads
1,082,135
Messages
5,363,344
Members
400,729
Latest member
Lisa McConachy

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top