rahulbassi
Board Regular
- Joined
- Aug 14, 2011
- Messages
- 130
Hi All,
I'm using below code for turning all the dropdowns to combo boxes. The probelm is that it is working if the database(for list) is in the same sheet. But, my database is in different worksheet "Database".
Please suggest.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet -----> This limits the scope to Sheet only
'Dim ss As Workbook -----> This is not working
'Set ss = ActiveWorkbook
Set ws = ActiveSheet
On Error GoTo errHandler
If Target.count > 1 Then GoTo exitHandler
Set cboTemp = ws.OLEObjects("TempCombo")
'Set cboTemp = ws.OLEObjects(Sheet1.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)
' MsgBox (ws.Range(str).AddressLocal)
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:
Resume exitHandler
End Sub
I'm using below code for turning all the dropdowns to combo boxes. The probelm is that it is working if the database(for list) is in the same sheet. But, my database is in different worksheet "Database".
Please suggest.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet -----> This limits the scope to Sheet only
'Dim ss As Workbook -----> This is not working
'Set ss = ActiveWorkbook
Set ws = ActiveSheet
On Error GoTo errHandler
If Target.count > 1 Then GoTo exitHandler
Set cboTemp = ws.OLEObjects("TempCombo")
'Set cboTemp = ws.OLEObjects(Sheet1.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)
' MsgBox (ws.Range(str).AddressLocal)
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:
Resume exitHandler
End Sub