If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Combo Box
Hi
I am trying combo boxes for the first time in excel. I have created cells with validation lists with named ranges I have pinched the code below from Debra Dalgleish/Contextures and the problem is I have no understanding of what is going on and how to fix When I click into the cell with the validation list in I get the following error message The named ranges are in 'sheet2' and the working sheet is 'sheet1' Method 'Range' of object '_Worksheet' failed Any help please Private 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 End If exitHandler: Application.ScreenUpdating = True Application.EnableEvents = True Exit Sub errHandler: MsgBox Err.Description Resume exitHandler End Sub Thanks Richard |
Thread Tools | |
Display Modes | |
|
|