Error while reading value of combobox - Run time error 424 : Object Required

[acmehra]

Hi Friends ,

I am trying to put the value selected in combobox in a variable but its showing Run Time error 424 - Object Required .

Here is my code :

Dim dd_val As String
Dim Data_sht As Worksheet
  dd_val = DD_Sell.Value
  Set Data_sht = ThisWorkbook.Worksheets(dd_val)

I tried using

dd_val = .List(.value)

But it also doesn't work .

NOTE : DD_Sell is the name I have for combobox in Formula bar of excel. In my excel file , this combobox is having list of the names of different sheets in my excel file.

Please help.

 

[cerfani]

Check if DD_Sell is Nothing, also...

I think you have to reference the sheet to get to the combobox... look here... https://stackoverflow.com/questions/44181019/how-to-reference-activex-combobox-in-excel-2016-vba

 

[Norie]

Where do you get the error and where is the combobox located?

 

[acmehra]

Where do you get the error and where is the combobox located?

Combobox is on the same sheet where pivot is being built. Sheet name is "Reports".
I get error while selecting any value from the combobox list. I tried using

dd_val = reports.DD_Sell.value

but its also not working

 

[acmehra]

Hi All,

I found the issue. Below was the reason. I had added below code to correct the error :

Sub ChangeSellDataSource()


With ActiveSheet.Shapes("DD_Sell").ControlFormat




   dd_val = .List(.ListIndex)
   
  Set Data_sht = ThisWorkbook.Worksheets(dd_val)
  Set Pivot_sht = ThisWorkbook.Worksheets("reports")
End With
End SUB