Solved: Names I define in a macro ignored by workbook?

[MrSteve]

Hello

I really hope someone can help me with this.

I recorded adding a number of name definitions on a test workbook. The VBA code is as follows -

Windows("Book2").Activate
ActiveWorkbook.Names.Add Name:="ActionColumn", RefersToR1C1:= _
"=[Book1.xls]Sheet1!C2"
ActiveWorkbook.Names.Add Name:="KeywordColumn", RefersToR1C1:= _
"=[Book1.xls]Sheet1!C1"
ActiveWorkbook.Names.Add Name:="KeywordList", RefersToR1C1:= _
"=[Book1.xls]Sheet1!R2C4:R20C4"
ActiveWorkbook.Names.Add Name:="KeywordStart", RefersToR1C1:= _
"=[Book1.xls]Sheet1!R1C1"
ActiveWorkbook.Names.Add Name:="KeywordStart", RefersToR1C1:= _
"=[Book1.xls]Sheet1!R1C1"

I have added this VBA code to Module1 of a new workbook called Book2.

When I open Book2 and view the name definitions (Insert -> Name -> Define) there is nothing there.

Does anyone know why this is?

Thank you.

[MrSteve]

Note I can get this working if I add it to the Workbook_Open() function, but then it gives an error immediately after the final name definition -

Run-time error '1004'.

Application-defined or object-defined error.

So it's the last line below -

ActiveWorkbook.Names.Add Name:="ActionColumn", RefersToR1C1:= _
"=[Book1.xls]Sheet1!C2"
ActiveWorkbook.Names.Add Name:="KeywordColumn", RefersToR1C1:= _
"=[Book1.xls]Sheet1!C1"
ActiveWorkbook.Names.Add Name:="KeywordList", RefersToR1C1:= _
"=[Book1.xls]Sheet1!R2C4:R20C4"
ActiveWorkbook.Names.Add Name:="KeywordStart", RefersToR1C1:= _
"=[Book1.xls]Sheet1!R1C1"
ActiveWorkbook.Names.Add Name:="KeywordStart", RefersToR1C1:= _
"=[Book1.xls]Sheet1!R1C1"
Columns("A2:A30").Select

Any ideas?

Thanks

[tstav]

Columns("A2:A30").Select
Any ideas?

Did you mean [vba]Range("A2:A30").Select[/vba]

Besides that, I don't think it works.
The names are getting assigned to the Book1.xls, since this is where they are pointing. Aren't names Workbook-dependent?

Edit: No, you're right. It worked when I put it in the Workbook_Open event.

[MrSteve]

Thanks for your reply. When I change it to Range it actually solves the problem

However, then the next set of VBA code has a problem. This is really weird, as I created all of this using the Macro recorder.

So continuing on from above...

ActiveWorkbook.Names.Add Name:="KeywordStart", RefersToR1C1:= _
"=[Book1.xls]Sheet1!R1C1"
Range("A2:A30").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:= _
"=IF(COUNTA($A2:$A$30)=0,$S$1,"")"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

It doesn't like -

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:= _
"=IF(COUNTA($A2:$A$30)=0,$S$1,"")"

Any ideas?

[tstav]

It doesn't like -
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:= _
"=IF(COUNTA($A2:$A$30)=0,$S$1,"")"
Any ideas?

This is what Microsoft says concerning Validation.Type:=xlValidateList
"Formula1 must contain either a comma-delimited list of values or a worksheet reference to this list."
Look it up in VBA help on "Validation object", Add method.

[MrSteve]

That's weird.

So does anyone know how you can get a non-comma delimited list into a data validation field via VBA?

Obviously it's easy to do it manually (Data -> Validation -> Type whatever formula you want), so why can you not do it via VBA? Seems strange.

[tstav]

MrSteve, if I've understood well from your code,
* you want the range A2:A30 to acquire a validation list that will show only the value of $S$1, if all range cells are blank
* you want the range A2:A30 to acquire a validation list that will show only one blank, if at least one range cell <> ""
Even though the above is not really clear to me as to why such a thing should happen, you can take the formula out of the "Validation.Formula1:= ..." and use the following:
[vba]Sub testValidation()
Dim a As String
'Take note that the " " in the next line is a space, not a blank
a = IIf(WorksheetFunction.CountA(Range("A2:A30")) = 0, Range("S1").Value, " ")
Range("A2:A30").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=a
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub[/vba]

Forgive me if I haven't understood well...

[rory]

Are you saying that you can manually set Data Validation with the Allow box set to List and then enter =IF(COUNTA($A2:$A$30)=0,$S$1,"") in the Source box without an error???

[MrSteve]

Rory: Yes, it works fine if I do it manually.

Tstav: Thanks, I will try that!

[tstav]

Rory: Yes, it works fine if I do it manually.

IT DOES??????
It never worked for me either.

[tstav]

I think I've got it, rory and MrSteve.

The reason the formula never worked for me either was, as I just found out, because I always used a string value and not a cell reference in either the second or the third argument of the IF function (just like you do MrSteve...)

Explain:
change the "" in your formula =IF(COUNTA($A2:$A$30)=0,$S$1,"")
and make it a reference to a cell that is blank e.g. $G$65535 and it will work.

So, I guess you might not need the code I sent.

Edit1: But as I see now, it seems to work only for the first cell of your selection. I haven't tested it yet, but maybe you'll have to loop through the selection's cells and apply the validation to each one of them. I may get back to you on this, later.
Edit2: It works fine for the whole selection.

[rory]

Should have thought about it more - the workbook I was testing on had a value in the A2:A30 range, so it wouldn't allow the validation (because of the ""). If the range is blank, it will allow you to add the validation.
The reason it won't work in code is that you need to double the quotes:
[VBA] .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:= _
"=IF(COUNTA($A2:$A$30)=0,$S$1,"""")"[/VBA]

[MrSteve]

Thanks everyone for the replies. I've made a lot of progress. The problem was a combination of needing to double my quotes and seperate the formula into a named range.

There is still one section of code which isn't working. The error message is:

Run-time error '1004'.

Application-defined or object-defined error.

The code is the following, with the error in bold:

    Columns("D:D").Select
    ActiveWorkbook.Names.Add Name:="myNamedRange4", RefersTo:="=OFFSET(KeywordStart,MATCH(C1,KeywordColumn,0)-1,1,COUNTIF(KeywordColumn,C1),1)"
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
     xlBetween, Formula1:="=myNamedRange4"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

Do any of you have any ideas what might be causing this?

Thanks again!

EDIT: I have attached all my code if that will make debugging easier

[rory]

Is the worksheet protected at all?

[rory]

Oh, and have you double-checked that you can do the same thing manually without error with the worksheet as it is when the code runs?

[MrSteve]

Hi Rory,

Thanks for your reply.

No the worksheet isn't protected. It's just a normal file I have created here.

Manually, it works fine. The reason I am trying to convert it to VBA is because I need to apply all the settings to a few thousand spreadsheets. I'm guessing there is a way to automate this if I'm using VBA.

Thanks

[rory]

That code works fine for me pasted in as it is posted. Which version of Excel are you using? Also, can I ask why this is in a Workbook Open event?

[MrSteve]

Weird!

I am using Excel 2003 SP3

The reason I am using the workbook open event is because I want all this stuff set up when the workbook opens. I'm guessing there is a better way to do it?

[rory]

The reason I am using the workbook open event is because I want all this stuff set up when the workbook opens.

I guess I walked into that one!
I was asking because:
1. you said you wanted this done for thousands of workbooks. Adding this code to each of those workbooks will be a nightmare, IMO.
2. This will run every time you open the workbook. Seems a bit pointless to keep recreating the names and validation every time you open the workbook, unless you have a reason for it?

[MrSteve]

Hi Rory

1. I think there is a way to automate it? At the moment I am just trying to get the code to work. I will worry about this part of the project next
2. I'm a total excel beginner, so a lot of my decisions are based on being a newbie Where do you recommend I put the code?

Thank you for your time.