Prev: Auto concantenate
Next: How do I delete clip art from cell in spreadsheet
From: richard on 22 Apr 2010 08:46
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
 | 
Pages: 1
Prev: Auto concantenate
Next: How do I delete clip art from cell in spreadsheet