Dropdowns to Combo boxes

[rahulbassi]

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

 

[Norie]

If you mean the list of values for the comboboxes is on Database try this.

Change this:

Set ws = ActiveSheet

To this:

Set ws = Worksheets("Database")

 

[rahulbassi]

Thanks Norie...But the dropdown is not getting converted to combobox at all with this change...

 

[Norie]

My fault, I thought ws was only being used for the list.

Change the code back and try this.

Add this at the top of the code.

Dim wsData As Worksheets

Add this below the line I suggested you should change.

Set wsData = Worksheets("Database")

Change this:

.ListFillRange = ws.Range(str).Address

To this:

.ListFillRange = wsData.Range(str).Address

 

[rahulbassi]

Hi Norie
The combo box is not having any value in it now after i had made the changes you recommended.
Should I send you the code and the complete situation?

 

[Norie]

Best stick to the forum for now.

Can you post the exact code you have now?

Also, post details of where the list is on worksheet 'Database'.