Macro gives error for no reason while creating a drop down data validation?

Question

Range("C4").Select
    Application.CutCopyMode = False
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Materials"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

I am trying to contruct a data validation to a one cell with macro. So that when you choose the material the graph will automatically change. Materials in the code is the data validation range, I named the range hoping that resolves the error.

So the main problem is that while the macro is running it gives "Run time error 1004". If I debug and put my yellow cursor to a few step back and go with f8, it works perfectly. I take this code from record macro. Code seems right but I don't know why macro gives error about it.

Delete the line `Range("C4").Select` and replace `With Selection.Validation` with `With ThisWorkbook.Sheets("Sheet1").Range("C4").Validation` Also change "Sheet1" to the relevant sheet name and try again — Siddharth Rout, Aug 25 '16 at 14:47
And finally see [How to avoid using Select in Excel VBA macros](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) :) — Siddharth Rout, Aug 25 '16 at 14:50
Unfortunately not worked :( . I know there is a lot going on avoiding select but avoiding it does not solve problems all the time. If you know how to use it properly, it will work with no problem. I have 1 month work of macro using a lot of select and activate, no problem at all. In your solution, it is not recognizing the thisworkbook since it works on module. Even though i deleted it the program gives error always at the .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Materials" code. Thanks for help anyway! — hakandeep, Aug 25 '16 at 15:01
Ok. Couple of questions... `1` Is the code in the same workbook which has `Materials` named range? `2` Is the code trying to add the DV List in the same workbook from where it is run? `3` If the answer to the above questions is "Yes" then do you have multiple workbooks opened? If yes, close them and try again. — Siddharth Rout, Aug 25 '16 at 15:06
there is no code in workbooks. It is on module of a workbook. Which uses other workbooks to construct another result workbook. The data validation part is on the result workbook. The materials table is at one sheet and I am trying to construct the data validation to another sheet. — hakandeep, Aug 25 '16 at 15:17
`The data validation part is on the result workbook. The materials table is at one sheet and I am trying to construct the data validation to another sheet.` And hence you need to fully qualify the `Range("C4")` object and `"=Materials"` — Siddharth Rout, Aug 25 '16 at 15:20
`1` What is the name of the workbook and worksheet where `Range("C4")` resides `2` What is the name of the workbook and worksheet where `Materials` resides ? `3` What is the name of the workbook where the code resides — Siddharth Rout, Aug 25 '16 at 15:23
1) ROL workbook, sheet1 2)ROL workbook, 6monthsConsumption 3)MainCode workbook — hakandeep, Aug 25 '16 at 15:25
Just to confirm your worbooks are "ROL.xls*" and "MainCode.xls*" \? — Siddharth Rout, Aug 25 '16 at 15:26
One moment. testing the code before posting an answer... I am assuming that the "materials" is a named range and not a table — Siddharth Rout, Aug 25 '16 at 15:31
btw man the thing is code is right i think. Like i say it gives error (application.events=false by the way and all the excels are minimized), when I click the ROL excel, the code continues to work — hakandeep, Aug 25 '16 at 15:36

hakandeep · Answer 1 · 2016-08-25T15:16:15.917

0

ok I found it.

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=Materials

since Materials is a table now on, I should have deleted the extra = and "

note: with this it gives no error but couldn't read the data from materials table also.

edited Aug 25 '16 at 15:16

answered Aug 25 '16 at 15:12

hakandeep

51
1
10

You never mentioned anywhere in your question that `Materials` is a table? You said it is a `Named Range` – Siddharth Rout Aug 25 '16 at 15:16
it was a named range, I am just trying different things. It doesn't work anyway – hakandeep Aug 25 '16 at 15:18
the thing interesting about that is when I debug and just re-do the things after I maximize the workbook window (even though it is the only workbook that is active) it works. Other than that it gives error. Just makes no sense – hakandeep Aug 25 '16 at 15:20

Siddharth Rout · Answer 2 · 2016-08-25T15:38:19.210

0

This works just fine for me.

My Assumptions:

Materials is a named range.
Workbooks("ROL.xlsx") is open in the same Excel instance.

Code

Sub Sample()
    Dim wbDV As Workbook
    Dim wsDV As Worksheet

    Set wbDV = Workbooks("ROL.xlsx")
    Set wsDV = wbDV.Sheets("Sheet1")

    With wsDV.Range("C4").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Materials"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

edited Aug 25 '16 at 15:38

answered Aug 25 '16 at 15:34

Siddharth Rout

147,039
17
206
250

I will try, but like I say the excel couldn't see the code I think. Sheets and workbooks are already arranged. But wait like 2 minutes I will try. Thanks for all the effort btw, it means a lot. – hakandeep Aug 25 '16 at 15:38
it gives the same error. Like I say I need to make excel understand that the workbook is opened. There is no problem with the definitions of ranges or workbooks. – hakandeep Aug 25 '16 at 15:44
Like I said it works for me. Is Workbooks("ROL.xlsx") open? – Siddharth Rout Aug 25 '16 at 15:45
If it is not open then change the line `Set wbDV = Workbooks("ROL.xlsx")` to `Set wbDV = Workbooks.Open("C:\ROL.xlsx")` I have used `C:\`. You have to replace it with the full path.. – Siddharth Rout Aug 25 '16 at 15:46
ı changed it already, but like i say problem is same and i solved it really unlogically. you can check the answer – hakandeep Aug 25 '16 at 16:11

score 0 · Answer 3 · answered Aug 25 '16 at 16:12

 Workbooks(strii).Activate
    Application.WindowState = xlMaximized
Sheets("Sheet1").Activate
    With Sheets("Sheet1").Range("C4").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlEqual, Formula1:="=Materials"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

maximizing the excel works just fine. Don't know why but it works

I just checked. I had missed the part where you said that the workbook is minimised. My code works just fine when the workbook is maximised. So all you had to do was add the maximize code to my code ;) — Siddharth Rout, Aug 25 '16 at 16:59

Macro gives error for no reason while creating a drop down data validation?

3 Answers3