Data Validation Excel Issues – Expert Urgently Needed

Hello,

 

I am a complete novice newbie to VBA and coding and I would like some help with some coding (hence the length)… If someone could provide me with the correct coding for my question below this would be most helpful and appreciated.

 

I am trying to create a drop-down combo box list on my Sheet1 (which was renamed in the sheet tab bar ‘501’), using a ‘One click’ Combo Box coding over merged cells that have already been data validated in the same Sheet 1. 

The ‘data validated list’ (not the combo list) in Sheet1 / 501  has retrieved my list data information from another sheet in the same workbook. The data list table was retrieved from Sheet21 (named ‘LESSON PLAN). And this has worked okay for my data validation. No problems here.

 

However, the text is too small in the data validation drop down list in sheet1 and therefore now I want to use a combo box over the data validation box so that I can change the font of the text etc. in Sheet1 (sheet tab bar named 501).

To start to do this, I have used the code below code which has worked fine so far. [CODE ‘A’ INFORMATION] And this has allowed me to click on each of the data validation cells in sheet1 one time only for the Combo box list to automatically  show in each cell. This is fine.

 

However, the problem is… There is no data / information showing in the Combo Boxes when I click on them. My QUESTION is… How can I retrieve the data information from Sheet21 (LESSON PLAN) which is also in the data validation drop down list, and put it in the Combo Boxes I have created?

 

Now… about Sheet21 (LESSON PLAN).

This is where my data list (table) has been kept. I have a Lesson Plan in this sheet and I have had to create a separate table in this work sheet to link information from the lesson plan for the purpose of this coding. I.e., in Sheet21, the table in column/row   X30 to X52 uses the formula =$C$6 to =$C$52 . Therefore, when I type information in the lesson plan, the information automatically populates in the data table. (All staff will have different information, hence the formula used for bespoke purposes.)

I have then highlighted the completed table and named it TempCombo

The TempCombo data has then been data validated back to sheet1 and this has worked fine. I.e. the information in the sheet21, shows fine in sheet 1 data validation drop down list.

 

The problem is here…. The combo Box in Sheet1 over the data validation list is showing up blank. It is not picking up the data from the list?

 

I have tried the below code but it doesn’t work..  [CODE ‘B’ INFORMATION BELOW DOESN’T WORK – NEEDED TO ADD DATA TO THE COMBO BOX FOR THE DATA LIST INFORMATION TO BE RETRIEVED] . My basic understanding is that this code is needed to add the Data list from data validation to the combo box, however I get compile error messages.. i.e. “Ambiguous name detected: TempCombo_KeyDown” when I use this code and no information / list data shows in the combo box?

I am a novice and will not know technical terms for the errors, thus please can someone provide me with the correct code that I can copy and paste and try and get this working?

 

Thanks in advance! 

 

 [CODE ‘A’ INFORMATION BELOW WORKS FINE SO FAR – FOR ONE CLICK ACCESS TO COMBO BOX IN MERGE CELLS:-]

Private Sub TempCombo_Change()

Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim str As String

Dim cboTemp As OLEObject

Dim ws As Worksheet

Set ws = ActiveSheet

On Error GoTo errHandler

 

‘If Target.Count > 1 Then GoTo exitHandler

 

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

 

On Error GoTo errHandler

If Target.Validation.Type = 3 Then

‘if the cell contains a data validation list

Application.EnableEvents = False

‘get the data validation formula

str = Target.Validation.Formula1

str = Right(str, Len(str) – 1)

With cboTemp

‘show the combobox with the list

.Visible = True

.Left = Target.Left

.Top = Target.Top

.Width = Target.Width + 15

.Height = Target.Height + 5

.ListFillRange = ws.Range(str).Address

.LinkedCell = Target.Address

End With

cboTemp.Activate

‘open the drop down list automatically

Me.TempCombo.DropDown

End If

 

exitHandler:

Application.ScreenUpdating = True

Application.EnableEvents = True

Exit Sub

errHandler:

Resume exitHandler

 

End Sub

‘====================================

‘Optional code to move to next cell if Tab or Enter are pressed

‘from code by Ted Lanham

‘***NOTE: if KeyDown causes problems, change to KeyUp

‘Table with numbers for other keys such as Right Arrow (39)

‘https://msdn.microsoft.com/en-us/library/aa243025%28v=vs.60%29.aspx

 

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

 

End Sub

 

 

 

 

 

 

 

 

[CODE ‘B’ INFORMATION BELOW DOESN’T WORK – NEEDED TO ADD DATA TO THE COMBO BOX FOR THE DATA LIST INFORMATION TO BE RETRIEVED]

 

Option Explicit

‘ Developed by Contextures Inc.

‘ www.contextures.com

Private Sub TempCombo_KeyDown(ByVal _

        KeyCode As MSForms.ReturnInteger, _

        ByVal Shift As Integer)

    ‘Hide combo box and move to next cell on Enter and Tab

    Select Case KeyCode

        Case 9

            ActiveCell.Offset(0, 1).Activate

        Case 13

            ActiveCell.Offset(1, 0).Activate

        Case Else

            ‘do nothing

    End Select

 

End Sub

 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim str As String

Dim cboTemp As OLEObject

Dim ws As Worksheet

Dim Tgt As Range

Dim TgtMrg As Range

Dim c As Range

Dim TgtW As Double

Dim AddW As Long

Dim AddH As Long

 

Set ws = ActiveSheet

On Error Resume Next

‘extra width to cover drop down arrow

AddW = 15

‘extra height to cover cell

AddH = 5

 

If Target.Rows.Count > 1 Then GoTo exitHandler

 

Set Tgt = Target.Cells(1, 1)

Set TgtMrg = Tgt.MergeArea

On Error GoTo errHandler

 

  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

 

  On Error GoTo errHandler

  If Tgt.Validation.Type = 3 Then

    Application.EnableEvents = False

    If Not TgtMrg Is Nothing Then

      ‘get total width of merged cells

      TgtW = 0

      For Each c In TgtMrg.Cells

        TgtW = TgtW + c.Width

      Next c

    End If

 

    str = Tgt.Validation.Formula1

    str = Right(str, Len(str) – 1)

    With cboTemp

      .Visible = True

      .Left = Tgt.Left

      .Top = Tgt.Top

      If TgtW <> 0 Then

        ‘use total width for merged cells

        .Width = TgtW + AddW

      Else

        .Width = Tgt.Width + AddW

      End If

      .Height = Tgt.Height + AddH

      .ListFillRange = str

      .LinkedCell = Tgt.Address

    End With

    cboTemp.Activate

    Me.TempCombo.DropDown

  End If

 

exitHandler:

  Application.EnableEvents = True

  Application.ScreenUpdating = True

  Exit Sub

errHandler:

  Resume exitHandler

 

End Sub

By: Excel NoviceUser

One thought on “Data Validation Excel Issues – Expert Urgently Needed

  1. Excel Novice User

    This issue has now been resolved. The coding needs to be formatted properly in the VBA module page, by highlighting the whole code and then pressing the tab key once. The data then comes up in the combo box okay.

Leave a Reply

Your email address will not be published. Required fields are marked *